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:
Topic Author
