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

davebostockgmail
Topic Author
Offline
Elite Member

More

2 years 9 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.

tpartner
Offline
LimeSurvey Community Team

More

2 years 9 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.

davebostockgmail
Topic Author
Offline
Elite Member

More

2 years 9 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.

tammo
Offline
Official LimeSurvey Partner

More

2 years 9 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.

davebostockgmail
Topic Author
Offline
Elite Member

More

2 years 9 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.

tpartner
Offline
LimeSurvey Community Team

More

2 years 9 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.

davebostockgmail
Topic Author
Offline
Elite Member

More

2 years 9 months ago - 2 years 9 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: 2 years 9 months ago by davebostockgmail.

Please Log in to join the conversation.

tpartner
Offline
LimeSurvey Community Team

More

2 years 9 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 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.

davebostockgmail
Topic Author
Offline
Elite Member

More

2 years 9 months ago - 2 years 9 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: 2 years 9 months ago by davebostockgmail.

Please Log in to join the conversation.

Joffm
Offline
LimeSurvey Community Team

More

2 years 9 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.

davebostockgmail
Topic Author
Offline
Elite Member

More

2 years 9 months ago - 2 years 9 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: 2 years 9 months ago by davebostockgmail.

Please Log in to join the conversation.

tpartner
Offline
LimeSurvey Community Team

More

2 years 9 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.

Moderators: tpartner, holch

Lime-years ahead

Online-surveys for every purse and purpose

Pricing & Plans

Get started

Welcome to the LimeSurvey Community Forum

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

File Attachment:

Lime-years ahead

Legal

About Us

Open Source