How to get jQuery autocomplete with PHP + MySQL Ajax and pass to JavaScript

Question

I have a javascript code that I am using for my autocomplete search feature and it is working fine. But if you look at the code below, the data that the search feature is returning is hard coded and I want to get the data from MySQL using PHP.

Anyone can help me how to convert the code below to use PHP query to gather data MySQL then use the results and pass it to javascript? Thank you.

//<![CDATA[
var a1;
var a2;

function InitMonths() {
    a2.setOptions({ lookup: 'January,February,March,April,May,June,July,August,September,October,November,December'.split(',') });
}

function InitWeekdays() {
    a2.setOptions({ lookup: 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday'.split(',') });
}

jQuery(function () {

    a1 = $('#query').autocomplete({
        width: 448,
        delimiter: /(,|;)\s*/,
        lookup: 'Andorra,Azerbaijan,Bahamas,Bahrain,Bangladesh,Barbados,Belarus,Belgium,Belize,Benin,Bhutan,Bolivia,Bosnia Herzegovina,Botswana,Brazil,Brunei,Bulgaria,Burkina,etc'.split(',')
    });

    a2 = $('#months').autocomplete({
        width: 448,
        delimiter: /(,|;)\s*/,
        lookup: 'January,February,March,April,May,etc'.split(',')
    });

    $('#navigation a').each(function () {
        $(this).click(function (e) {
            var element = $(this).attr('href');
            $('html').animate({ scrollTop: $(element).offset().top }, 300, null, function () { document.location = element; });
            e.preventDefault();
        });
    });

});

Answer 1

in response to Pez's answer above, the suggested PHP array structure did not work for me. I had to structure my array like so:

    $reply = array();
    $reply['query'] = $term;
    $reply['suggestions'] = array();
    foreach ($items as $item) {
        $reply['suggestions'][] = array('value' => htmlentities(stripslashes($item['value'])), 'data' => $item['id');
    }

then

return json_encode($reply);

I am using v1.2.7 of the library available here: https://github.com/devbridge/jQuery-Autocomplete

Answer 2

New

Based on this auto complete plugin http://www.devbridge.com/projects/autocomplete/jquery/

Your JavasSript will need to look something like:

//Start auto complete
a1 = $("#query").autocomplete({
    serviceUrl:'search.php', //tell the script where to send requests
    width: 448, //set width

    //callback just to show it's working
    onSelect: function(value, data){ alert('You selected: ' + value + ', ' + data); } 
});

And your PHP (search.php) will need to be:

///
/*** connect to your DB here ***/
///

//retrieve the search term and strip and clean input
$term = trim(strip_tags($_GET['query'])); 

//try to make user input safer
$term = mysqli_real_escape_string($term);

//build a query on the database
$qstring = "SELECT description as value,id FROM test WHERE description LIKE '%".$term."%'";

//query the database for entries containing the term
$result = mysql_query($qstring);

//array to return
$reply = array();
$reply['query'] = $term;
$reply['suggestions'] = array();
$reply['data'] = array();

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))//loop through the retrieved values
{
    //Add this row to the reply
    $reply['suggestions'][]=htmlentities(stripslashes($row['value']));
    $reply['data'][]=(int)$row['id'];
}

//format the array into json data
echo json_encode($reply);

The plugin expects json like the below which this PHP should provide

query:'Li',
suggestions:['Liberia','Libyan Arab Jamahiriya','Liechtenstein','Lithuania'],
data:['LR','LY','LI','LT']

Note I haven't tested this but it should be fine!

---

Old Answer

See here: http://www.simonbattersby.com/blog/jquery-ui-autocomplete-with-a-remote-database-and-php/

First of all if you're not using the jQuery autocomplete plugin (the one supported by jQuery as part of jQuery UI) set that up. http://jqueryui.com/demos/autocomplete/#remote

You're asking how to completely rework the system.

For a start you'll need to use Ajax to send the match string to the database via PHP as a proxy, then PHP will need to return the results and have the Javascript read them.

So you'll need to use (as the config):

a1 = $("#query").autocomplete({
source: "search.php"
width: 448  
});

And something like (as your PHP)

//connect to your database

$term = trim(strip_tags($_GET['term']));//retrieve the search term that autocomplete sends

$qstring = "SELECT description as value,id FROM test WHERE description LIKE '%".$term."%'";
$result = mysql_query($qstring);//query the database for entries containing the term

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))//loop through the retrieved values
{
        $row['value']=htmlentities(stripslashes($row['value']));
        $row['id']=(int)$row['id'];
        $row_set[] = $row;//build an array
}
echo json_encode($row_set);//format the array into json data

Answer 3

Instead of using the plugin you have used, use the plugin available on following link :

Jquery UI Autocomplete

Using this plugin you can get access to the data from database using php. It will work surely.

Answer 4

Since you're already using a jQuery plugin I assume that you know jQuery. In the code below I'm using a jQuery method called $.post which is used to load data from the server without reloading the page(ajax as you might call it)

$.post('yourphp.php', function(data){
     var obj = JSON.parse(data);
      $('#months').autocomplete({
                width: 448,
                delimiter: /(,|;)\s*/,
                lookup: obj
            });
}); 

On your php:

<?php
$months = ['jan', 'feb'...'dec'];
echo json_encode($months);
?>

I don't really know what jQuery plugin you're using for the autosuggest. But you might want to try autocomplete from jquery ui or datalist in html5. Not really sure about the browser support though. But here's an example:

<datalist id="yo_list">
<?php foreach(){ //loop through the result of your query here ?>
  <option value="<?php echo $somevalue; ?>"><?php echo $somevalue; ?></option>
<?php } ?>
</datalist>

-Didn't really test the code, so let me know if it doesn't work or there's something you don't understand. Here's a link to the $.post method if you want to learn more: http://api.jquery.com/jQuery.post/