CODEWITHSUNDEEP
javascriptmysql
aphextwig
aphextwig

Reputation: 553

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;

Upvotes: 0

Views: 65

Answers (1)

Paul
Paul

Reputation: 36339

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

Upvotes: 2

Related Questions