Back to limesurvey.org

Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Ajax autocomplete data from a database for multiple short text questions filters

More
4 years 6 months ago #226297 by davebostockgmail
Ajax autocomplete data from a database for multiple short text questions filters was created by davebostockgmail
Please help us help you and fill where relevant:
Your LimeSurvey version: Version 5.3.4+220309
Own server or LimeSurvey hosting: Own
Survey theme/template:
==================

I am not sure if this is even possible ... if it isn't then I will resort to using multiple pages to get the required effect.

We have a list of 3500+ car dealerships that have been uploaded to a database table, it could be in CSV for all I care, which we want to perform filtered lookups via a series of autocomplete questions to allow the respondent to get to the dealership they use.

The 3 would be 
Region - English County
Town - Towns within that county
Dealer - Dealers within that town

Using auto complete would make this task a lot easier and having it shown in one question would make the survey more user friendly.

I can do the Autocomplete from a database using Ajax and PHP with the following in the source code of the question to return the county to a short text question  ....

<script src=" server.url/lookup/jquery.easy-autocomplete.min.js ">
<link href=" server.url/lookup/easy-autocomplete.min.css " rel="stylesheet" />
<script type="text/javascript" charset="utf-8">
    $(document).ready(function(){
    var optionsName = {
        url: function (data) {
            return " server.url/lookup/countyLookup.php?phrase= " + data;
        },
          list: {
        maxNumberOfElements: 100,
        match: {
            enabled: true
        }
    },
        getValue: "makemodel"
    };
        var qID = '{QID}';
        $('#question'+qID+' input[type="text"]').easyAutocomplete(optionsName);
    });
 
</script>

And the PHP file that pulls in the answers for that question, but this is triggered on page load so the filtering of the database is not possible there,

What I would like to do is after each short code question is answered, that answer is used to filter the next questions autocomplete list. I have tried with the Focus event but had no luck.

Is this even possible to do in the way described?

Any and all help appreciated.

Thanks
Dave
 

Please Log in to join the conversation.

Partner Banner
More
4 years 6 months ago #226345 by tpartner
Replied by tpartner on topic Ajax autocomplete data from a database for multiple short text questions filters
I would use a multiple-short-text question so you can easily manipulate all inputs with code in a single question.

I am not familiar with easy-autocomplete but it seems to me that if you initially import the complete data set into local memory, you can initiate autocomplete on the region input, then use the onChooseEvent event to filter the data and initiate autocomplete on the city. Same for city --> dealer inputs.

You should be able to do the same thing with jQueryUI Autocomplete, using the select event - api.jqueryui.com/autocomplete/#event-select .
Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

Please Log in to join the conversation.

More
4 years 6 months ago #226347 by davebostockgmail
Replied by davebostockgmail on topic Ajax autocomplete data from a database for multiple short text questions filters
Thanks Tony

I will give this a try with your suggestions and update you on the result

Cheers
Dave

Please Log in to join the conversation.

More
4 years 6 months ago #226348 by tammo
Replied by tammo on topic Ajax autocomplete data from a database for multiple short text questions filters
It would be great if you could share your solution here.
Tammo ter Hark at Respondage
For Limesurvey reporting, education and customized themes
respondage.nl

Please Log in to join the conversation.

More
4 years 6 months ago #226349 by davebostockgmail
Replied by davebostockgmail on topic Ajax autocomplete data from a database for multiple short text questions filters
If I get it working I definitely will ... as I always do when helped by others :)

Thanks
Dave
The following user(s) said Thank You: tammo

Please Log in to join the conversation.

More
4 years 6 months ago #226357 by tpartner
Replied by tpartner on topic Ajax autocomplete data from a database for multiple short text questions filters
Dave, can you share an example of what the data from the PHP file looks like?
Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

Please Log in to join the conversation.

More
4 years 6 months ago - 4 years 6 months ago #226382 by davebostockgmail
Replied by davebostockgmail on topic Ajax autocomplete data from a database for multiple short text questions filters
OK So I think I have got this done ... probably not the most elegant way I am sure but it is working. 

Tony, if you can think of a more elegant way to do this then I would appreciate it but for now this meets the needs we have

Step 1 : Download the easy-autocomplete code and place it on your server -  easyautocomplete.com/ or your autocomplete plugin of your choice.
Step 2 : Create your database with the required fields to use for the look ups - Mine is County , Town and Dealership name
Step 3 : Create a Multiple Short text question with 3 Inputs
Step 4 : Add the following code to the question source ... changing the red parts to meet your needs

<script src="https://yourserver/lookup/jquery.easy-autocomplete.min.js"></script>

<link href="https://yourserver/lookup/easy-autocomplete.min.css" rel="stylesheet" /> <script>

$(document).ready(function() {


$( "#answer568141X835X379011" ).keyup(function() {


var optionsName1 = { url: function (data) {

return "https://yourserver/lookup/dealerregionlookup.php?phrase=" + data; },
list: { maxNumberOfElements: 100,
match: { enabled: true },
},

getValue: "County" };

$('#javatbd568141X835X379011 input[type="text"]').easyAutocomplete(optionsName1);
$(this).focus();
return false;
});

$( "#answer568141X835X379012" ).keyup(function() {

var str = $("#answer568141X835X379011").val();


var optionsName2 = { url: function (data1) {

return "https://yourserver/lookup/dealercitylookup.php?countyValue=" + str +"&phrase1=" + data1; },
list: { maxNumberOfElements: 100,
match: { enabled: true },
},

getValue: "Town" };

$('#javatbd568141X835X379012 input[type="text"]').easyAutocomplete(optionsName2);
$(this).focus();
return false;
});

$( "#answer568141X835X379013" ).keyup(function() {

var str1 = $("#answer568141X835X379012").val();


var optionsName3 = { url: function (data2) {

return "https://yourserver/lookup/dealernamelookup.php?townValue=" + str1 +"&phrase2=" + data2; },
list: { maxNumberOfElements: 100,
match: { enabled: true },
},

getValue: "Dealership" };

$('#javatbd568141X835X379013 input[type="text"]').easyAutocomplete(optionsName3);
$(this).focus();
return false;
});

});

</script>

Step 4 : Create your lookup files in php to query the database and return the matches you want.

Example first lookup file.

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

header('Content-Type: application/json');

$servername =  
$dbname = 
$username = 
$password=

$countyRequest = $_GET;
//$iSurveyID = $_GET["sid"];

 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT Distinct County FROM yourdatabase.DealerLookup where County like '%".$countyRequest."%';";


$result = $conn->query($sql);
    
//echo json_encode($result);
$rows = array();
while($r = mysqli_fetch_assoc($result)) {
    $rows[] = $r;
}

echo json_encode($rows);


$conn->close();


?>

Example subsequent look up files .... In this case the final one after I have used the county to look up the towns this uses the towns to filter the dealerships.


<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

header('Content-Type: application/json');

$servername = 
$dbname = 
$username = 
$password=

$town = $_GET;
$DealerRequest = $_GET;


 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT Distinct Dealership FROM yourdatabase.DealerLookup where Town like '%".$town."%' and Dealership like '%".$DealerRequest."%';";


$result = $conn->query($sql);
    
//echo json_encode($result);
$rows = array();
while($r = mysqli_fetch_assoc($result)) {
    $rows[] = $r;
}

echo json_encode($rows);


$conn->close();
?>


Like I said not elegant but functional.

Thanks
Dave
Last edit: 4 years 6 months ago by davebostockgmail.

Please Log in to join the conversation.

More
4 years 6 months ago #226402 by tpartner
Replied by tpartner on topic Ajax autocomplete data from a database for multiple short text questions filters
I think I would prefer to load all of the data initially and then filter it locally as inputs are changed instead of initiating several AJAX calls. This script, containing some sample data, will handle re-initiating the autocompletes as answers are changed and will handle returning to the page.

In the attached sample survey, start by typing "o" in the first input, select a region and then type "a" or "b" in the second input. Repeat for the third input.
 
 
Code:
<script src="pathTo/jquery.easy-autocomplete.min.js"></script> <link href="pathTo/easy-autocomplete.min.css" rel="stylesheet" /> <script type="text/javascript" data-author="Tony Partner"> $(document).on('ready pjax:scriptcomplete',function(){ // Identify this question var thisQuestion = $('#question{QID}'); // Define the data var fullData = [ { 'region': 'Ontario', 'city': 'ONA1', 'dealer': 'ON11A1' }, { 'region': 'Ontario', 'city': 'ONA1', 'dealer': 'ON11A2' }, { 'region': 'Ontario', 'city': 'ONA1', 'dealer': 'ON11B1' }, { 'region': 'Ontario', 'city': 'ONA1', 'dealer': 'ON11B2' }, { 'region': 'Ontario', 'city': 'ONA2', 'dealer': 'ON12A1' }, { 'region': 'Ontario', 'city': 'ONA2', 'dealer': 'ON12A2' }, { 'region': 'Ontario', 'city': 'ONA2', 'dealer': 'ON12B1' }, { 'region': 'Ontario', 'city': 'ONA2', 'dealer': 'ON12B2' }, { 'region': 'Ontario', 'city': 'ONB1', 'dealer': 'ON21A1' }, { 'region': 'Ontario', 'city': 'ONB1', 'dealer': 'ON21A2' }, { 'region': 'Ontario', 'city': 'ONB1', 'dealer': 'ON21B1' }, { 'region': 'Ontario', 'city': 'ONB1', 'dealer': 'ON21B2' }, { 'region': 'Ontario', 'city': 'ONB2', 'dealer': 'ON22A1' }, { 'region': 'Ontario', 'city': 'ONB2', 'dealer': 'ON22A2' }, { 'region': 'Ontario', 'city': 'ONB2', 'dealer': 'ON22B1' }, { 'region': 'Ontario', 'city': 'ONB2', 'dealer': 'ON22B2' }, { 'region': 'Quebec', 'city': 'QCA1', 'dealer': 'QC11A1' }, { 'region': 'Quebec', 'city': 'QCA1', 'dealer': 'QC11A2' }, { 'region': 'Quebec', 'city': 'QCA1', 'dealer': 'QC11B1' }, { 'region': 'Quebec', 'city': 'QCA1', 'dealer': 'QC11B2' }, { 'region': 'Quebec', 'city': 'QCA2', 'dealer': 'QC12A1' }, { 'region': 'Quebec', 'city': 'QCA2', 'dealer': 'QC12A2' }, { 'region': 'Quebec', 'city': 'QCA2', 'dealer': 'QC12B1' }, { 'region': 'Quebec', 'city': 'QCA2', 'dealer': 'QC12B2' }, { 'region': 'Quebec', 'city': 'QCB1', 'dealer': 'QC21A1' }, { 'region': 'Quebec', 'city': 'QCB1', 'dealer': 'QC21A2' }, { 'region': 'Quebec', 'city': 'QCB1', 'dealer': 'QC21B1' }, { 'region': 'Quebec', 'city': 'QCB1', 'dealer': 'QC21B2' }, { 'region': 'Quebec', 'city': 'QCB2', 'dealer': 'QC22A1' }, { 'region': 'Quebec', 'city': 'QCB2', 'dealer': 'QC22A2' }, { 'region': 'Quebec', 'city': 'QCB2', 'dealer': 'QC22B1' }, { 'region': 'Quebec', 'city': 'QCB2', 'dealer': 'QC22B2' }, { 'region': 'Nova Scotia', 'city': 'NSA1', 'dealer': 'NS11A1' }, { 'region': 'Nova Scotia', 'city': 'NSA1', 'dealer': 'NS11A2' }, { 'region': 'Nova Scotia', 'city': 'NSA1', 'dealer': 'NS11B1' }, { 'region': 'Nova Scotia', 'city': 'NSA1', 'dealer': 'NS11B2' }, { 'region': 'Nova Scotia', 'city': 'NSA2', 'dealer': 'NS12A1' }, { 'region': 'Nova Scotia', 'city': 'NSA2', 'dealer': 'NS12A2' }, { 'region': 'Nova Scotia', 'city': 'NSA2', 'dealer': 'NS12B1' }, { 'region': 'Nova Scotia', 'city': 'NSA2', 'dealer': 'NS12B2' }, { 'region': 'Nova Scotia', 'city': 'NSB1', 'dealer': 'NS21A1' }, { 'region': 'Nova Scotia', 'city': 'NSB1', 'dealer': 'NS21A2' }, { 'region': 'Nova Scotia', 'city': 'NSB1', 'dealer': 'NS21B1' }, { 'region': 'Nova Scotia', 'city': 'NSB1', 'dealer': 'NS21B2' }, { 'region': 'Nova Scotia', 'city': 'NSB2', 'dealer': 'NS22A1' }, { 'region': 'Nova Scotia', 'city': 'NSB2', 'dealer': 'NS22A2' }, { 'region': 'Nova Scotia', 'city': 'NSB2', 'dealer': 'NS22B1' }, { 'region': 'Nova Scotia', 'city': 'NSB2', 'dealer': 'NS22B2' }, { 'region': 'Saskatchewan', 'city': 'SKA1', 'dealer': 'SK11A1' }, { 'region': 'Saskatchewan', 'city': 'SKA1', 'dealer': 'SK11A2' }, { 'region': 'Saskatchewan', 'city': 'SKA1', 'dealer': 'SK11B1' }, { 'region': 'Saskatchewan', 'city': 'SKA1', 'dealer': 'SK11B2' }, { 'region': 'Saskatchewan', 'city': 'SKA2', 'dealer': 'SK12A1' }, { 'region': 'Saskatchewan', 'city': 'SKA2', 'dealer': 'SK12A2' }, { 'region': 'Saskatchewan', 'city': 'SKA2', 'dealer': 'SK12B1' }, { 'region': 'Saskatchewan', 'city': 'SKA2', 'dealer': 'SK12B2' }, { 'region': 'Saskatchewan', 'city': 'SKB1', 'dealer': 'SK21A1' }, { 'region': 'Saskatchewan', 'city': 'SKB1', 'dealer': 'SK21A2' }, { 'region': 'Saskatchewan', 'city': 'SKB1', 'dealer': 'SK21B1' }, { 'region': 'Saskatchewan', 'city': 'SKB1', 'dealer': 'SK21B2' }, { 'region': 'Saskatchewan', 'city': 'SKB2', 'dealer': 'SK22A1' }, { 'region': 'Saskatchewan', 'city': 'SKB2', 'dealer': 'SK22A2' }, { 'region': 'Saskatchewan', 'city': 'SKB2', 'dealer': 'SK22B1' }, { 'region': 'Saskatchewan', 'city': 'SKB2', 'dealer': 'SK22B2' } ]; // Define some elements and vars var keys = ['region', 'city', 'dealer']; var input1 = $('.answer-item:eq(0) input:text.form-control', thisQuestion); var inputs = { 0: $('.answer-item:eq(0) input:text.form-control', thisQuestion), 1: $('.answer-item:eq(1) input:text.form-control', thisQuestion), 2: $('.answer-item:eq(2) input:text.form-control', thisQuestion) } var answers = { 0: $.trim($(inputs[0]).val()), 1: $.trim($(inputs[1]).val()), 2: $.trim($(inputs[2]).val()) } // Create an array of "region" values var regions = []; $.each(fullData, function(i, val) { if(!regions.includes(val.region)) { regions.push(val.region); } }); // Initiate autocomplete on the first input var aOptions1 = { data: regions, list: { maxNumberOfElements: 100, match: { enabled: true }, onChooseEvent: function() { //If new selection... if($.trim(inputs[0].val()) != answers[0]) { answers[0] = $.trim(inputs[0].val()); // Reset following items $('li.answer-item:gt(0) input:text', thisQuestion).val('').trigger('keyup').easyAutocomplete({ 'data': '' }); //Initiate autocomplete on next input childAutocomplete(0); } } } }; inputs[0].easyAutocomplete(aOptions1); function childAutocomplete(index) { if((index+1) < $('li.answer-item', thisQuestion).length) { var parentInput = inputs[index]; var nextInput = inputs[(index+1)]; var key1 = keys[index]; var key2 = keys[(index+1)]; // Define the new data var thisFullData = fullData.filter(function(obj) { return (obj[key1] == $.trim(parentInput.val())); }); var thisData = []; $.each(thisFullData, function(i, val) { if(!thisData.includes(val[key2])) { thisData.push(val[key2]); } }); //Initiate autocomplete on next input var aOptions = { data: thisData, list: { maxNumberOfElements: 100, match: { enabled: true }, onChooseEvent: function() { //If new selection... if($.trim($(nextInput).val()) != answers[(index+1)]) { answers[(index+1)] = $.trim($(nextInput).val()); // Reset following items $('li.answer-item:gt('+index+1+') input:text', thisQuestion).val('').trigger('keyup').easyAutocomplete({ 'data': '' }); //Initiate autocomplete on next input childAutocomplete(index+1); } } } }; nextInput.easyAutocomplete(aOptions); } } // Returning to page if($.trim(inputs[0].val()) != '') { childAutocomplete(0); } if($.trim(inputs[1].val()) != '') { childAutocomplete(1); } }); </script>

Sample survey attached: 

File Attachment:

File Name: limesurvey...9(1).lss
File Size:34.11 KB
Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The following user(s) said Thank You: davebostockgmail

Please Log in to join the conversation.

More
4 years 6 months ago - 4 years 6 months ago #226426 by davebostockgmail
Replied by davebostockgmail on topic Ajax autocomplete data from a database for multiple short text questions filters
Tony

As always that is amazing and works way better than my solution. 

All I have had to do is pull in the data from the PHP MySQL query which selects every record so I replaced your array with 

var fullData = ;

$.ajax({
  method: 'GET',
    url: 'https://yourserver/lookup/DealerDataFull.php',
  success: function(data) {
    fullData = data;
    console.log(fullData);

and added 

    }
});

At the end of the file before the closing brackets and this now works amazingly well.

Thanks again for your help on this.

Dave
Last edit: 4 years 6 months ago by davebostockgmail.

Please Log in to join the conversation.

More
4 years 6 months ago #226427 by Joffm
Replied by Joffm on topic Ajax autocomplete data from a database for multiple short text questions filters
This is really an awesome solution and will satisfy many of the users.
So many times there was the requirement to have something like a tree question "region -> province -> city" or whatever.

But now:
I imported the sample survey into my Community version 5.2.9. and in the Cloud version 5.3.4.
And nothing happened.
 

The js and css files are loaded.

Is there something I missed?

Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless

Please Log in to join the conversation.

More
4 years 6 months ago - 4 years 6 months ago #226428 by davebostockgmail
Replied by davebostockgmail on topic Ajax autocomplete data from a database for multiple short text questions filters
Are you getting any errors in the Developer Console?
Are you using Tony's data array or a call to a database?
Did you update the path to the JS and CSS files from Tony's example?
Last edit: 4 years 6 months ago by davebostockgmail.

Please Log in to join the conversation.

More
4 years 6 months ago #226429 by tpartner
Replied by tpartner on topic Ajax autocomplete data from a database for multiple short text questions filters
The sample survey has absolute paths to the JS and CSS files available online.
Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

Please Log in to join the conversation.

More
4 years 6 months ago #226430 by tpartner
Replied by tpartner on topic Ajax autocomplete data from a database for multiple short text questions filters
...when time allows, I will develop a version using jQuery UI autocomplete which I find to be a little more powerful. (more methods, events and options)
Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The following user(s) said Thank You: Joffm, davebostockgmail

Please Log in to join the conversation.

More
4 years 6 months ago #226433 by tammo
Replied by tammo on topic Ajax autocomplete data from a database for multiple short text questions filters
If the server is on https, the scripts should also be on https.
Error message: Mixed Content: The page at ' myserver.com/index.php/699959 ' was loaded over HTTPS, but requested an insecure script ' easyautocomplete.com/dist/jquery.easy-autocomplete.min.js '. This request has been blocked; the content must be served over HTTPS.


But when I change the call to the scripts to https, the following error message appears in the console:

Failed to load resource: net::ERR_CERT_COMMON_NAME_INVALID
Tammo ter Hark at Respondage
For Limesurvey reporting, education and customized themes
respondage.nl

Please Log in to join the conversation.

More
4 years 6 months ago #226434 by davebostockgmail
Replied by davebostockgmail on topic Ajax autocomplete data from a database for multiple short text questions filters
I cant help with that .. I always copy the scripts to a local directory

Please Log in to join the conversation.

Moderators: tpartner, holch

Lime-years ahead

Online-surveys for every purse and purpose

Pricing & Plans
Get started

Legal

About Us

Open Source