Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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();
        });
    });

});
share|improve this question
which autocomplete plugin are you using? I can see that you're using jquery, you may want to look at the $.post method. – Kyokasuigetsu Aug 31 '12 at 9:53
I'd assume it was the jQuery UI one, however it looks like it is devbridge.com/projects/autocomplete/jquery – Pez Cuckow Aug 31 '12 at 10:30
@PezCuckow yes it is based on devbridge autocomplete. – webdev.gk Sep 3 '12 at 6:00
See my updated answer below – Pez Cuckow Sep 4 '12 at 10:05

3 Answers

up vote 2 down vote accepted

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
share|improve this answer
i tried your suggestion but it is now showing any results. but when i manually tried the search.php?term=countryname it does show result. – webdev.gk Sep 3 '12 at 6:11
Then there is something wrong with your PHP, check for syntax errors. Have you connected the script to your DB? – Pez Cuckow Sep 3 '12 at 8:33
hi, yes when i manually tried the php script i was able to get results example: localhost/search.php?query=li then I got a list of results but on the autocomplete search input it doesn't show anything. – webdev.gk Sep 4 '12 at 0:38
In that case see my reworked answer – Pez Cuckow Sep 4 '12 at 9:42
1  
Hi Pez, thank you very much. The problem is on the PHP script, the plugin requires the format that you have provided. thank you. – webdev.gk Sep 5 '12 at 2:29
up vote 2 down vote

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.

share|improve this answer
up vote 0 down vote

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/

share|improve this answer
The auto complete plugin has this built in? – Pez Cuckow Aug 31 '12 at 10:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.