Ajax autocomplete with jquery and php

Jquery autocomplete script with ajax and phpWith a autocomplete script you can make it easier for your users to provide valid input for a from, by providing suggestions as soon as the users starts to type in the first data.

In this post we’ll implement a autocomplete script, that uses jquery, ajax, PHP and MySQL for a smooth and easy to configure autocomplete solution.

Jquery autocomplete

The autocomplete guide in this post, is based on the autocomplete script by Drew Wilson and the refinement by Devbridge.com. There are many great autocomplete solutions, but the script from Drew Wilson has a few advantages, that makes it my favorite script:

  • Autocomplete data based on a fixed list or ajax requests
  • Great cross browser compatibility
  • Possibility to add multiple values per input field by adding commas as value delimiter
  • Tons of customization features…

See a example of the script here

Implementing the autocomplete script

First, you should ensure that jquery, the javascript file and the CSS files for the auto_suggest functionality is included within your <head></head> tags on the top of your code:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js" type="text/javascript"></script>
<script src="jquery.autocomplete.js" type="text/javascript"></script>
<link rel="stylesheet" href="styles.css" type="text/css" media="screen" />

To keep things simple, we’re just linking to the jquery file hosted by Google.

You can find the files you need to download and include for the auto suggest functionality here.

Next thing is to build the form that includes the autocomplete input field.


<form action="default.aspx" >
 <input type="text" name="q" id="query" />
 <input type="button" value="OK" />
 </form>

The last thing you need to do is insert the following piece of javascript just before the </body> part of your html code:

<script type="text/javascript">
 //<![CDATA[

 var a1;

 jQuery(function() {
 var options = {
 serviceUrl: 'auto_suggest_tags.php',
 width: 300,
 delimiter: /(,|;)\s*/,
 deferRequestBy: 0, //miliseconds
 params: { country: 'Yes' },
 noCache: false //set to true, to disable caching
 };

 a1 = $('#query').autocomplete(options);

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

 });

//]]>
</script>

Please notice that the variable “serviceurl” contains a url to the script that feeds the autocomplete input field with suggestions. Next thing is to build this script.

Creating  json data for the auto suggest field

In this example we’ll build a simple php script that provides the json data required for the input field.

The ajax part of the autocompletescript requests the file with a GET request with the following querystring:  “?query=<letters from autosuggest input field>”

It’s expected that the script returns a json object with the following format:

{
 query:'ph',
 suggestions:['PHP','Photos','Photo'],
}

In this example we’ll build a simple php script that can extract data from a MySQL database and insert them in the required json format.

The example extracts tags from a wordpress database, but you can modify the script to extract whatever data you have in a mysql database.

<?
// insert the connection settings to your mysql database here!!

$counter='0';
echo "{";
echo "query:'$query',";
echo "suggestions:[";
$res = mysql_query("select name from wp2wp_terms where name like '$query%' ORDER BY name desc");
while ($row = mysql_fetch_array($res)) {
$counter++;
if ($counter > 1) {
echo ",";
}
$name=$row["name"];
echo "'$name'";
}
echo "],}";
?>

Save this file as: “auto_suggest_tags.php”, and that’s it you now have a autocomplete functionality, that uses ajax to dynamically extract suggestions from a MySQL database.

See a example of the script here

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
fold-left fold-right
About the author
Jørgen Nicolaisen has been passionately interested in everything online since 1995. His experience is based on working with small hobby projects as well as high volume websites. Jørgen is currently focused on the PHP based programming framework - Codeigniter, and WordPress naturally

23 Replies to Ajax autocomplete with jquery and php

  1. Rico says:

    Good day!
    I don’t understand serviceUrl: ‘auto_suggest_tags.php’,
    Please enlighten us more on this.
    Is it the script to extract MySQL data?

  2. jhnidk says:

    Sorry for being unspecific about this.
    Yes – auto_suggest_tags.php is the script that provides the json data for the suggest list

  3. Rico says:

    Thanks for the reply.
    I still can’t get it to work with MySQL data. Is there a simpler method, say:

    serviceUrl: ‘{query:’ph’, suggestions:['PHP','Photos','Photo'],}’,

    For testing purposes, is the above example possible? Sorry, as I’m really confused on serviceUrl.

    Regards,
    Rico

  4. Rico says:

    jhnidk,
    I figured it out myself….I overlooked the AJAX $GET request. Now it’s displaying MySQL data. Thanks!
    :d
    Awesome script!

    Regards,
    Rico

  5. Mert Şener says:

    Hi. Is there any problem in explorer, it works on safari, mozilla but in ie it does not work?

  6. jhnidk says:

    Hi, the script works fine in IE 6+, so if you’re having problems, you should tjeck if all required files has been downloaded, and included correctly

  7. ciro says:

    hello!!!

    follow the steps in your tutorial
    but my script does not work
    not pass any parameters in the line of serviceUrl??

    my connection and query

    are correct

    sorry my English is not good

  8. jhnidk says:

    Try to test the file “auti_suggest_tags.php” with different parameters to see if the file works, and outputs valid data from your database

  9. KISHOR KV says:

    Hi, i’m using the jquery.autocomplete.js file. but its showing

    Webpage error details

    User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; msn OptimizedIE8;ENIN; AskTB5.6)
    Timestamp: Fri, 29 Oct 2010 18:52:36 UTC

    Message: Permission denied
    Line: 19
    Char: 27021
    Code: 0
    URI: file:///C:/WINDOWS/Resources/Themes/Luna/Shell/wamp/www/sample/jquery.min.js

    Message: Permission denied
    Line: 19
    Char: 27021
    Code: 0
    URI: file:///C:/WINDOWS/Resources/Themes/Luna/Shell/wamp/www/sample/jquery.min.js

    plz tel me solution

  10. jhnidk says:

    Hi, It sounds like there is something wrong with the way you’re including the jquery library. Insted of a local file, maybe you should try one of the online files eg. from google http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js

  11. cuma elp says:

    btw, how can i change the $_GET parameter? for example, i would like to use two different table for two different textfield. first textfield for tags autocomplete and the other for post autocomplete

    sorry for my bad english. since it’s not my native language & i didn’t learn it properly :D

  12. cuma elp says:

    too much comment on this website :D
    i’ve finally made it. i also create a table output from it. not only div tag… so, it similar to datagrid with search option :D

    thanks again man…

  13. Is possible make this ajax in ASP.NET ?

  14. oopeduation says:

    Nice article Thanks for sharing it

  15. Naveed says:

    i am using this tutorial, its works fine but when i enter some characters after calling php script by different parameters, the input fields picks previous list rather than new one. how can i clear previous list and to pick list elements from newly generated list?

  16. ankit mazumdar says:

    plz help as i am not being able to find out why the parameter that is being sent from the view record page to the edit record page via href is not getting accepted in the edit records page on the line mentioned,as a result the whole block is not executing.I have tested and found out that the passing parameter is not getting stored in the specified variable.
    Please point out my mistakes.
    Thanks & Regards.

    View Record

    while ($row = $result->fetch_object())
    {
    // set up a row for each record
    echo “”;
    echo “” . $row->item_invoice_id . “”;
    echo “” . $row->issue_date . “”;
    echo “” . $row->vendor_id . “”;
    echo “” . $row->ven_pers_name . “”;
    echo “” . $row->comp_name . “”;
    echo “item_invoice_id .
    “‘>Edit
    “;
    echo “item_invoice_id .
    “‘>Delete
    “;
    echo “”;
    }

    EDIT RECORD

    */
    // if the ‘id’ variable is set in the URL, we know that we need to edit a record
    if (isset($_GET['item_invoice_id']))
    {
    // if the form’s submit button is clicked, we need to process the form
    if (isset($_POST['submit']))
    {
    // make sure the ‘id’ in the URL is valid
    if (isset($_POST['item_invoice_id'])) //the problem is on this line
    {
    // get variables from the URL/form
    $item_invoice_id = $_POST['item_invoice_id'];
    //$item_invoice_id = htmlentities($_POST['item_invoice_id'], ENT_QUOTES);
    $issue_date = htmlentities($_POST['issue_date'], ENT_QUOTES);

    // check that both are not empty
    if ($item_invoice_id == ” || $issue_date == ”)
    {
    // if they are empty, show an error message and display the form
    $error = ‘ERROR: Please fill in all required fields!’;
    renderForm($item_invoice_id, $issue_date, $error);
    }
    else
    {
    // if everything is fine, update the record in the database
    if ($stmt = $mysqli->prepare(“UPDATE purchase_order_1 SET item_invoice_id = ?, issue_date = ?
    WHERE item_invoice_id=?”))
    {
    $stmt->bind_par(“ss”, $item_invoice_id, $issue_date);
    $stmt->execute();
    $stmt->close();
    }
    // show an error message if the query has an error
    else
    {
    echo “ERROR: could not prepare SQL statement.”;
    }

    // redirect the user once the form is updated
    header(“Location: purchase order 1.php”);
    }
    }
    // if the ‘id’ variable is not valid, show an error message
    else
    {
    echo “Error!”;
    }
    }

  17. Wesley says:

    Hello, I’m using your code, I did a search on the bench and is working properly, but I can not receive $ query variable and the value sent by the field imput.
    assign value to this variable eg $ query = ‘a’;
    Then came the autocomplete, but I can not change the value of the variable.
    Could you help me?
    Congratulations for the work.

  18. CrOMaX says:

    “Hello, I’m using your code, I did a search on the bench and is working properly, but I can not receive $ query variable and the value sent by the field imput.
    assign value to this variable eg $ query = ‘a’;
    Then came the autocomplete, but I can not change the value of the variable.
    Could you help me?
    Congratulations for the work.”

    Same problem…

  19. Kris says:

    Hi Sir jhnidk,

    First of all thank you for putting an article like this. It is really helpful to us.

    I went through your steps but the textbox is not working as intended. My auto_suggest_php looks like this

    <?

    $db_host = 'localhost';
    $db_name = 'test';
    $db_user = 'root';
    $db_pwd = 'toor';

    $db_conn = mysql_connect($db_host, $db_user, $db_pwd);

    if(!$db_conn) { die('Could not connect to MySQL: ' . mysql_error());}

    echo "Connected!”;
    mysql_select_db($db_name, $db_conn);

    // insert the connection settings to your mysql database here!!

    $counter=’0′;
    echo “{“;
    echo “query:’$query’,”;
    echo “suggestions:[";
    $res = mysql_query("select item_name from shop_items where item_name like '$query%' ORDER BY item_name desc");
    while ($row = mysql_fetch_array($res)) {
    $counter++;
    if ($counter > 1) {
    echo ",";
    }
    $name=$row["name"];
    echo “‘$name’”;
    }
    echo “],}”;

    mysql_close($db_conn);
    ?>

    Do you think there is something wrong with my DB connection? I am just a newbie and still learning how to code…but hope you can give me some advice on what to look/check for. Thank You and More Power!

  20. Ron says:

    I think some people may be having problems with the GET request as one of the previous posters mentioned.

    For this to work you have to add something like “$query = ($_GET['query']);” to your PHP script to catch the request.

Trackbacks for this post

  1. 30 Auto Completion Scripts for Better User Experience | Free Web Design Tucson
  2. 30 Auto Completion Scripts for Better User Experience « qeqnes | Designing. jQuery, Ajax, PHP, MySQL and Templates
  3. 30 Auto Completion Scripts for Better User Experience

Comments are now closed for this article.