aphextwig aphextwig - 1 month ago 6
Javascript Question

Retrieve multiple values from SQL db to use in JavaScript Autocomplete

I'm using the following script for an input autocomplete feature. I retrieve 3 columns of data from a MYSQL database. The script is great at displaying 1 column, 'title', but I want to use the others, 'slug' and 'id', to build a clickable URL for each result.

What I've tried so far hasn't worked. How do I make slug and id available to use?

MIN_LENGTH = 2;
$( document ).ready(function() {
$("#keyword").keyup(function() {
var keyword = $("#keyword").val();
if (keyword.length >= MIN_LENGTH) {

$.get( "auto-complete.php", { keyword: keyword } )
.done(function( data ) {
$('#results').html('');
var results = jQuery.parseJSON(data);
$(results).each(function(key, value) {
$('#results').append('<a href="/' + value.slug + '/' + value.id + '/"><div class="item">' + value + '</div></a>');
})

$('#keywordsearch').click(function() {
var text = $(this).html();
$('#keyword').val(text);
})

});
} else {
$('#results').html('');
}
});

$("#keyword").blur(function(){
$("#results").hide();
})
.focus(function() {
$("#results").show();
});

});


This is the PHP/SQL

function serachForKeyword($keyword) {

$db = getDbConnection();
$stmt = $db->prepare("SELECT title,id,slug FROM `articles` WHERE title LIKE ? ORDER BY title ASC");

$keyword = $keyword . '%';
$stmt->bindParam(1, $keyword, PDO::PARAM_STR, 100);

$isQueryOk = $stmt->execute();

$results = array();

if ($isQueryOk) {
$results = $stmt->fetchAll(PDO::FETCH_COLUMN);
} else {

trigger_error('Error executing statement.', E_USER_ERROR);
}

$db = null;

return $results;

Answer

PDO::FETCH_COLUMN tells the driver to just get one column, in your case the title. Try a different fetch mode.