Welcome to the LimeSurvey Community Forum

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

Using jQuery autocomplete with PHP/MySQL in LimeSurvey

  • eirikat
  • eirikat's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
9 years 1 week ago #118135 by eirikat
Hi,

I've enjoyed using LimeSurvey for some time, but this is my first post to the forum.

In a short text question, I want my users to be able to preview results based on their first couple of keystrokes, using jQuery autocomplete. I've already achieved this using a string or a .csv file as source for the autocomplete, but what I really want is to get the data from a MySQL table.

I actually have a working example of autocomplete using MySQL in a subfolder of my survey template, but for some reason I can't make this work in the survey itself. Since I know the autocomplete script is working (I can check this by replacing source), and the database connection should be fine (it's the same as in my test) I wonder if there may be some specific limitation to MySQL lookups within LimeSurvey? Either that, or I'm simply overlooking something.

This is my question source, including the autocomplete script:
Code:
Start typing: <script type="text/javascript" charset="utf-8">
 
    $(document).ready(function() {
 
    var qID = 1;
 
    $('#question'+qID+' input[type="text"]').autocomplete({
      minLength: 2,
// For testing:   source: ["Test1","Test2","Test3"]
      source: 'autocomplete.php'
    });
 
  });
 
</script>

And here is autocomplete.php:
Code:
set_include_path('/var/www/surveys/test-survey');
 
// Database credentials
define('DB_SERVER', "localhost");
define('DB_USER', "username");
define('DB_PASSWORD', "password");
define('DB_DATABASE', "database_name");
define('DB_DRIVER', "mysql");
 
// Connect and query MySQL database, using PDO
try {
  $db = new PDO(DB_DRIVER . ":dbname=" . DB_DATABASE . ";host=" . DB_SERVER . ";charset=utf8", DB_USER, DB_PASSWORD);
}
 
catch(PDOException $e) {
    echo $e->getMessage();
}
 
$return_arr = array();
 
if ($db)
{
  $ac_term = "%".$_GET['term']."%";
  $query = "SELECT * FROM table_name WHERE column_name LIKE :term";
  $result = $db->prepare($query);
  $result->bindValue(":term",$ac_term); 
  $result->execute(); 
 
// Fetch data and store in array
  while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $row_array['label'] = $row['column_name'];
    $row_array['value'] = $row['column_name'];
 
        array_push($return_arr,$row_array);
    }
}
 
// Clear query
$db = null;  
 
// Encode results in JSON, for jquery autocomplete
echo json_encode($return_arr);


I should add that I'm not an expert in any of this, so please do not hesitate to give me a lesson in the obvious :laugh:
The topic has been locked.
  • eirikat
  • eirikat's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
9 years 1 week ago #118146 by eirikat
Allright, I found the obvious... Turns out the autocomplete.php file was referenced incorrectly once the autocomplete script was moved into the survey question. Using the full url resolved this.

Even with the instructions presented in the manual's workaround section , it has taken me some effort to tweak the scripts to my use, and incorporate autocomplete into my survey. Hopefully, the scripts I have presented here may be of use to someone else :silly:
The topic has been locked.
  • holch
  • holch's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
9 years 1 week ago #118149 by holch
It would be great if you could add this to the workaround section as well. The Limesurvey manual is a wiki, anyone can contribute. Unfortunately the forums get "cleaned up" from time to time, which means solutions posted only here can get lost over time.

I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.

The topic has been locked.
  • eirikat
  • eirikat's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
9 years 1 week ago #118150 by eirikat
Sure, holch, I'll get onto it :)
The following user(s) said Thank You: holch
The topic has been locked.
  • holch
  • holch's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
9 years 1 week ago #118151 by holch
This would be a great help for the community, because it can save time for others with the same problem in the future.

Would be also great if you could post a link to the solution in the workaround section here in this thread, so when someone comes via the forum, will still find it easily!

I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.

The topic has been locked.
  • eirikat
  • eirikat's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
9 years 1 week ago #118179 by eirikat
I have now added an update to the manual's workaround page , where all of my working scripts have been included.
The following user(s) said Thank You: holch, Ben_V
The topic has been locked.
  • holch
  • holch's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
9 years 1 week ago #118180 by holch
I didn't try it, but thank you very much for including this into the manual.

Looks like a very detailed description, which will help users with little programming knowledge.

I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.

The topic has been locked.
  • eirikat
  • eirikat's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
9 years 1 week ago #118194 by eirikat
Well, I'm not an expert, either, so I wrote it the way I would have liked to read it. Very often you find yourself trying to combine suggestions from a variety of different sources, which makes debugging very difficult (especially when you're combining several coding languages and don't really feel confident with any of them...). Having scripts at hand that have already been tested, in a similar system as the one you're working in, definitely helps B)
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose