Using jQuery autocomplete with PHP/MySQL in LimeSurvey

More
2 years 9 months ago #118135 by eirikat
eirikat created the topic: Using jQuery autocomplete with PHP/MySQL in LimeSurvey
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:
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:
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:

Please Log in to join the conversation.

More
2 years 9 months ago #118146 by eirikat
eirikat replied the topic: Using jQuery autocomplete with PHP/MySQL in LimeSurvey
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:

Please Log in to join the conversation.

More
2 years 9 months ago #118149 by holch
holch replied the topic: Using jQuery autocomplete with PHP/MySQL in LimeSurvey
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'm not a LimeSurvey GmbH member. I answer at the LimeSurvey forum in my spare time. No support via private message.
Some helpful links: Manual (EN) | Question Types | Workarounds

Please Log in to join the conversation.

More
2 years 9 months ago #118150 by eirikat
eirikat replied the topic: Using jQuery autocomplete with PHP/MySQL in LimeSurvey
Sure, holch, I'll get onto it :)
The following user(s) said Thank You: holch

Please Log in to join the conversation.

More
2 years 9 months ago #118151 by holch
holch replied the topic: Using jQuery autocomplete with PHP/MySQL in LimeSurvey
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'm not a LimeSurvey GmbH member. I answer at the LimeSurvey forum in my spare time. No support via private message.
Some helpful links: Manual (EN) | Question Types | Workarounds

Please Log in to join the conversation.

More
2 years 9 months ago #118179 by eirikat
eirikat replied the topic: Using jQuery autocomplete with PHP/MySQL in LimeSurvey
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

Please Log in to join the conversation.

More
2 years 9 months ago #118180 by holch
holch replied the topic: Using jQuery autocomplete with PHP/MySQL in LimeSurvey
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'm not a LimeSurvey GmbH member. I answer at the LimeSurvey forum in my spare time. No support via private message.
Some helpful links: Manual (EN) | Question Types | Workarounds

Please Log in to join the conversation.

More
2 years 9 months ago #118194 by eirikat
eirikat replied the topic: Using jQuery autocomplete with PHP/MySQL in LimeSurvey
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)

Please Log in to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now
Join our Newsletter!