Design · Culture · Spirituality

jQuery Auto Suggest with MySQL

Recently, I was building a site at work that benefited from an auto suggest feature in some of the form fields. This, of course, is the kind of thing that you see when you do Google searches. I’ve done some similar things in the past, but I’ve never had a solid, consistent solution that I could use for this kind of thing.

In knowing this, I spent some time looking at a large number of jQuery plugins that do this. For some time, I couldn’t find any that fit the specific need I had. There was one plugin that I thought would work, but I couldn’t get it to work with a MySQL database, as it was designed to work with plain text lists in PHP. It may seem pathetic, but I couldn’t translate it.

So finally, I found the specific solution I wanted, and also manipulated the code examples enough to get it to work with MySQL. In light of this, and in light of my difficulty finding good examples for this, I want to give an example here, for myself and any others with the issue.

Steps

  1. Download the latest jQuery.
  2. Download the latest Autocomplete Plugin from bassistance.de.
  3. Include the bgiframe plugin for the sake of Internet Explorer users, in case this appears within a larger form. You can include this with a conditional comment if you wish.
  4. Create a MySQL database that contains the items from which you’ll be suggesting. There are many great tutorials out there for this kind of thing.
  5. Include your JavaScript files in a page with the following HTML, additional JavaScript, PHP, and CSS.

HTML form markup

So let’s say we have the following XHTML. I’m not concerned with specific areas of content at the moment, just a basic auto-suggest form that does not submit to anything in particular.

  1. <div id="wrapper">
  2. <form id="suggest-form">
  3. <div>
  4. <label for="field">Search for something</label>
  5. <input type="text" name="field" id="field" />
  6. </div>
  7. </form>
  8. </div>

jQuery Auto Suggest code

The jQuery plugin for this technique works in such a way that it examines the contents of the text field as users type in it, and as it does this it sends requests to PHP to see what contents in the database (or other PHP content) match what is being typed. Each time a letter is pressed or deleted, this occurs again. jQuery then creates HTML that displays the results to the user.

After the plugin is loaded, the following JavaScript is loaded. For myself, I normally put this into a main.js file, though for the purposes of this example I have kept it inside the source, and you can view it there if you wish.

  1. function suggestValues() {
  2. $("#field").autocomplete("http://design.jonathanstegall.com/autosuggest/suggestions.php", {
  3. width: 260,
  4. selectFirst: false
  5. });
  6. }
  7. $(document).ready(function(){
  8. suggestValues();
  9. });

PHP search code (suggestions.php)

In this particular example, we’ll use PHP to connect to a MySQL database and get search results. I have a specific test database here, with keywords that are used in this blog. Obviously, you’ll need to modify the code for your own purposes, and there is no reason to use suggestions.php; you’ll just need to take note of the filename in order to include it in the JavaScript code above.

  1. First, you will need to have an active connection to your database. I can’t possibly predict all the ways that this can happen, so I will assume that you are already connected to it and can run queries on it.
  2. function autosuggest() {
  3. $q = strtolower($_GET["q"]);
  4. if (!$q) return;
  5. $query = "SELECT name FROM keywords";
  6. $results = mysql_query($query);
  7. while($result = mysql_fetch_array($results)) {
  8. $name = $result['name'];
  9. if (strpos(strtolower($name), $q) !== false) {
  10. echo "$name\n";
  11. };
  12. };
  13. }
  14. autosuggest();

CSS example

There is also some CSS that needs to be present to account for the default behavior of the plugin. The JavaScript, as I said, creates HTML on the fly, and this HTML can be styled very easily. Here is an example of what I have used. You can also see this in the source, if you view it, though again it would normally be inside a CSS file.

  1. .ac_results {
  2. padding: 0;
  3. border: 1px solid #333;
  4. background-color: #fff;
  5. overflow: hidden;
  6. z-index: 99999;
  7. text-align: left;
  8. }
  9. .ac_results ul {
  10. width: 100%;
  11. list-style-position: outside;
  12. list-style: none;
  13. padding: 0;
  14. margin: 0;
  15. }
  16. .ac_results li {
  17. margin: 0;
  18. padding: 2px 5px;
  19. cursor: default;
  20. display: block;
  21. font: menu;
  22. font-size: 10px;
  23. line-height: 16px;
  24. overflow: hidden;
  25. }
  26. .ac_results {
  27. background-color: #eee;
  28. }
  29. .ac_over {
  30. background-color: #0e2221;
  31. color: #fff;
  32. }

Put it all together

I have created a file at http://design.jonathanstegall.com/jquery/autosuggest/ that demonstrates the jQuery auto suggest technique that I have discussed. This is a very foundational concept, and the plugin does come with some configuration options, but I feel that this is important since most of us will be using this plugin with a database of some kind.

Feel free to leave any additional thoughts or questions in the comments.

39 Comments

  1. This is great! Finally a good example of using the autocomplete function with Jscript.

    Only issue I have is referring to the files when on my own webserver. Cant seem to get the installation/placement of the javascript files correct between the jquery, autocomplete, and bgiframe. any help would be appreciated.

    Thanks!

    Joe

  2. @Joseph: Sure. Try this, first of all, and feel free to post an example link that we can look at if questions remain.

    1. <script type="text/javascript" src="http://{your domain}/jquery/jquery.js"></script>
    2. <script type="text/javascript" src="http://{your domain}/jquery/autosuggest/autocomplete.js"></script>
    3. <script type="text/javascript" src="http://{your domain}/jquery/bgiframe.js"></script>
    4. <script type="text/javascript">
    5. function suggestValues() {
    6. $("#field").autocomplete("{your domain without http, then the path to the PHP file}/suggestions.php", {
    7. width: 260,
    8. selectFirst: false
    9. });
    10. }
    11. $(document).ready(function(){
    12. suggestValues();
    13. });
    14. </script>

    Hope that helps. But again, feel free to send a link if there are further questions. Thanks for the comment.

  3. This is great, and works flawless.

    But how would you go about showing some results that you don’t necessarily want to search for.

    Say I’m searching for a co-worker and I set up the autosuggest dropdown to show “worker name – email” but I actually only want the email to be submitted on send. How would you go about doing that?

  4. @Burfelt: I’m not sure if I understand your question completely. At first I thought you wanted to display the name, for example, while using the email as the search string. If this is the case, you could simply change the SQL statement to look for ‘name’ and ’email’. You would cause the echo to display the name value like it does, but change the strpos(strtolower($name), $q to use the email value instead.

    But I’m not sure this is what you want. Can you rephrase it, if not? Maybe give an example?

  5. I see that I’m not making much sense there sorry.
    What I’m doing is:

    1. I’m searching for an email address or name of a co-worker in the search input.

    2. As I type I want the autosuggest to show a list as formatted as “[Co-worker name] – [email]”.

    3. Then when I select a co-worker from the autosuggest list. I only want to have the [email] value returned to the search input, as the co-worker name is really only listed as a help to find the email.

    I’m not sure how this is done though.

  6. @Burfelt: I believe the jQuery plugin would have to be expanded a bit to make the autosuggest value different from the form’s value after it was clicked. This would require more Ajax, certainly, in order to get both values available to the script.

    As far as I know, this is the closest you could come with this plugin, though I am confident it could be expanded by someone with the right degree of JavaScript expertise (probably not me):

    1. function autosuggest() {
    2. $q = strtolower($_GET["q"]);
    3. if (!$q) return;
    4. $query = "SELECT name,email FROM keywords";
    5. $results = mysql_query($query);
    6. while($result = mysql_fetch_array($results)) {
    7. $name = $result['name'];
    8. $email = $result['email'];
    9. $match = "$name $email";
    10. if (strpos(strtolower($match), $q) !== false) {
    11. echo "$email\n";
    12. };
    13. };
    14. }
    15. autosuggest();

    The idea is that you are combining the name and email into a string for which the database is searched, and the results can match either one. Only the email is displayed in the autosuggest, though, and thus it becomes the value of the form field.

  7. hey jonathan,
    I’m doing some very simular but in my autocomplete drop down instead of just displaying the static name of the matched items, i want to enable a url link, so the user can do second query. i’m just wondering have you come across any resources on how to use jquery to customise the selected items list.
    regards,
    P

  8. @Paul: Hi Paul. I’m not sure exactly what you are looking to do. Do you have an example that shows this working on another site?

    If not, I can say this: the echo "$name\n"; in the post’s example shows what is sending content to the JavaScript. You could try to change it to something like this: echo "<a href="somelink">link text</a>\n"; and see how it works.

    The script takes this variable and renders it for each result inside an HTML list. It’s possible that this would work for what you want to do.

  9. hey jonathan,

    Thank you very much for nice code,

    I am new to Jquery and php both. I am doing same thing like you ecept i am passing array instead of .php file in autocomlete function.
    But in autocomplete box i want to search for any latter at any position from result.
    I have one array like below….

    array([0]=> “APOP/RDU,Server,Server Turn up Port”
    [1]=> “APOP/RDU,Server,Server Turn offport”
    [2]=> “APOP/RDU,RAN,RAN Launch”
    [3]=> “APOP/RDU,RAN,RAN Maintenance”
    )

    And in my java script i am doing as below…

    $(document).ready(function(){

    $(“#example”).autocomplete(data, {
    selectFirst: false
    });
    }

    This code is working fine but i want like if user type ser…than i want to show “APOP/RDU,Server,Server Turn up/down Port” and “APOP/RDU,Server,Server Turn up/down Circuit”
    from array to text box because ser…is there in both the value after comma. My current code is showing matching character for first position only means if user type AP..than it will show value stating from AP..but i want like if
    user type AP..and if AP…is any where in my string than i want to display that value to user.

    Please help me with this one. I tried a lots of things but i am not able to do it.

    Thank you

  10. @regal: Look into the autosuggest function again, as it does not require the user to type the first character of a value.

    The example code simply creates an array from the database results, and when the user types it looks to see if the letters that have been typed match any of the array values. It seems to be exactly what you want, so you may just want to back up and try again.

  11. thanks for your perfect idea,
    i have a problem after writing in the
    text box, the Auto Suggest work OK, but when
    i insert more letters not in the list the Auto Suggest return to appear displaying all items.
    i need the Auto Suggest to disappear
    like your example :
    http://design.jonathanstegall.com/jquery/autosuggest/
    and thanks again.

  12. This is great!!! Is there also an example available on how to use this with a database search and then show the result to the user?

  13. @Arno: There isn’t a direct example of this here, though all you would have to do is write the correct SQL query in place of $query = "SELECT name FROM keywords"; and this technique would work with search results. This post is all about the display of the data; getting the data itself is up to you.

  14. Works like advertised. Very nice. However, it works only if you have a hard coded sql query in the suggestions.php file. I would love to be able to have the query change dynamically by passing in $_POST[‘value’] values into the query so that the suggestions change depending on the, let say, customer. For example, if the customer is a roof contractor, only specific insurance rates should be available to choose from, or if a customer’s phone carrier is AT&T, only specific phones should be available…and so on. Probably poor examples, but you get the point. Since the query is fixed, all rates will show, regardless of what type of contractor you are, and all phones will be available regardless of what carrier the customer use. Much tweaking must be done I assume…I’m just saying that it would be sweet 🙂

  15. @MockY: Quite right. The point of this post was simply to demonstrate it from the perspective of jQuery, while incorporating some MySQL. Any query could be used, of course, within any CMS/framework. The only important part from the jQuery perspective is the $results array, in this case.

    Hope that makes sense. Other tutorials teach SQL much better, but there aren’t many folks that combine it with jQuery for a display like this, and it is a common display that folks need.

  16. I am using DISTINCT in a query thats searching half a million+ records it takes about 10-20 seconds for the auto complete to query finish and display. I am going off of using the core sample here with the only change being the query itself

    SELECT DISTINCT Title, ISBN13 FROM book GROUP BY Title

    is my query do you have any suggestions as to what may speed it up? I am using distinct as I want to return only single results and the database has many many duplicate entries with different data

  17. @Chris: Thanks for your comment. I have to admit that optimizing MySQL queries is not something I’m incredibly well-versed in, but I do know that there are lots of options, from editing the queries themselves to caching query results, that you can use to speed them up.

    I’ve done some of these in various projects, but there’s a great list of tips at this article that may help you. I’d feel much more comfortable pointing you there, and to similar resources, than I would trying to be a database engineer. 🙂

  18. Hi, thanks for your good work. It works nicely. I have one query regarding this .
    when i search it gives a list of releated keywords its ok ,
    but i want to show only 10-15 recodrs with drop-down select bar is it possible .

    check this : http://www.sansoft.co.in/webdev/Villa-Holiday/
    search with a , m
    Its important.
    Plz reply

    Thanks
    Pankaj

  19. Hi…
    I am trying to apply the plugin, however I am not sure whether my PHP code has problems or not. The problem is I cannot select the specific item on the suggestion list, when I do the click select, it select the whole thing.
    for example:
    the list of data when I key “d”
    Dawn
    Danny
    Dark
    I cannot select just Dawn, or Danny… but I am forced to select Dawn, Danny, Dark.
    Could you please help me out. I am not quite sure how to fix this.

    Thanks in advance.

  20. Hi, not sure your query is very optimized here, have you tried running it against a large data set (100’s of 1000’s or rows)?

    Try something like this instead:

    “SELECT name FROM keywords WHERE name LIKE ‘%$q%'”

    I’m no SQL expert but this i surely faster than retrieving all records and looping them in PHP.

  21. […] a database using php. Some good examples: Jetlogs.org jQuery: Auto-suggest with keyup() event jQuery Auto Suggest with MySQL | jonathan stegall: creative tension Also a nice easy to use plugin: Code | Drew Wilson.com __________________ Feel free to thank […]

  22. Hi,
    Is there a solution for turkish characters? For example when I enter a word that includes a turkish character, auto-suggest can not get and show the word from the database. But for testing, when I try the word directly to suggestions.php (like writing url suggestions.php?q=doÄŸ to address bar), it can get the word from database. I think word can not go to suggestions.php from inputfield correctly if it has a turkish character. Any solution??
    Thanks

  23. Hey looks great so thanks! But is it possible to hide autosuggest when the user enter just one character? Thanks

  24. @Sovereignty,

    you need to use two solutions for turkish characters:

    1) use in index.php

    2) use these lines before the query in suggestions.php:

    mysql_query(“SET NAMES ‘utf8′”);
    mysql_query(“SET CHARACTER SET utf8”);
    mysql_query(“SET COLLATION_CONNECTION=’utf8_general_ci'”);

  25. My first solution disappeared because of html tag usage.

    I meant you have to use meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />

    (put a < in the beginning)

  26. Hi Jonathan,
    I’m thankfull for the article as i’ve been searching for a good example of autocomplete feature with jquery though the steps are much tough to take just to display a autocomplete box from database. However, unfortunately there are some problems related to CSS, actually related to browsers. I set the width paramater of plugin to a desirable value that fits well on Opera, whereas Firefox displays autocomplete field longer than i set, or conversely, it displays shorther textbox input. IE displays close to Opera. I didn’t tested Safari and Chrome.

    What the hell can we set CSS parameters cross-browser?

    Any idea?

    Thanks.

  27. what about form auto submit, when clicking one item in suggestion list? 🙂

  28. KEY.RETURN:if(selectCurrent()){document.form.submit();}

    :-))

  29. I Hi Jonathan, it’s a great script. I had a table contain code_id and name_product column. Question is, how to implement an autocomplete which is show the name_product but then store the code_id value. Thank you

  30. @ndhol: If I understand you correctly, you’ll need to update the SQL and what actually gets sent to the browser. Have a look at this earlier in the comments for this post.

  31. It is not working in IE 9
    Works good in Firefox 4

  32. Jonathan,

    I would be glad to know how to implement the auto suggest.
    i have a text area , users enter some long description in that and whenever user enter @ symbol i need to autosuggest user the email followed by @ example

    abcdef@ some words , some words no autosuugest
    xyz@

    in the above whenever @ is their i need to display set of users autosuggest that start s from abcdef and again aftre some text if user enters xyz@ autosuggest when @ is entered

    Please give me some pointers how to do this , as i am ne wto jquery and wannna try out this.

    i hope you will have some time for me to resolve this.

    Thanks in Advance

  33. the problem with me is that in this code of auto complete,after the textbox is populated with the input,the list is not disappearing.it is going behind the text boxes and remining there.also the listbox is not getting to the length and width of the suggestions list.Can anyone help please
    ———————————————–
    jquery code
    **********************

    Ajax Auto Suggest

    function lookup(inputString)
    {
    if(inputString.length == 0)
    {
    // Hide the suggestion box.
    $(‘#suggestions’).hide();
    }
    else
    {
    $.post(“rpc.php”, {queryString: “”+inputString+””}, function(data)
    {
    if(data.length >0)
    {
    $(‘#suggestions’).show();
    $(‘#autoSuggestionsList’).html(data);
    }
    });
    }
    } // lookup

    function fill(thisValue) {
    $(‘#inputString’).val(thisValue);
    setTimeout(“$(‘#suggestions’).hide();”, 200);
    }

    body {
    font-family: Helvetica;
    font-size: 11px;
    color: #000;
    }

    h3 {
    margin: 0px;
    padding: 0px;
    }

    .suggestionsBox {
    position: absolute;
    top: 85px;
    left: 450px;
    margin: 10px 0px 0px 0px;
    width: 200px;
    background-color: #323943;
    -moz-border-radius: 7px;
    -webkit-border-radius: 7px;
    border: 2px solid #000;
    color: #fff;
    }

    .suggestionList {
    margin: 0px;
    padding: 0px;
    }

    .suggestionList li {

    margin: 0px 0px 3px 0px;
    padding: 3px;
    cursor: pointer;
    }

    .suggestionList li:hover {
    background-color: #323943;
    }

    Type your name:

     

    #############################################
    php-mysql database code
    $$$$$$$$$$$$$$$$$$$$$$$$$$$$
    real_escape_string($_POST[‘queryString’]);

    // Is the string length greater than 0?

    if (strlen($queryString) > 0)
    {
    // Run the query: We use LIKE ‘$queryString%’
    // The percentage sign is a wild-card, in my example of countries it works like this…
    // $queryString = ‘Uni’;
    // Returned data = ‘United States, United Kindom’;

    // YOU NEED TO ALTER THE QUERY TO MATCH YOUR DATABASE.
    // eg: SELECT yourColumnName FROM yourTable WHERE yourColumnName LIKE ‘$queryString%’ LIMIT 10

    $query = $db->query(“SELECT first_name FROM info WHERE first_name LIKE ‘$queryString%’ LIMIT 5″);
    if ($query)
    {
    // While there are results loop through them – fetching an Object (i like PHP5 btw!).
    while ($result = $query->fetch_object())
    {
    // Format the results, i m using for the list, you can change it.
    // The onClick function fills the textbox with the result.

    // YOU MUST CHANGE: $result->value to $result->your_colum
    echo ‘first_name . ‘\’);”>’ . $result->first_name . ”;
    }
    }
    else
    {
    echo ‘ERROR: There was a problem with the query.’;
    }
    }
    else
    {
    // Dont do anything.
    } // There is a queryString.
    }
    else
    {
    echo ‘There should be no direct access to this script!’;
    }
    }

    ?>
    =================================================

  34. Hi Jonathan, it work great! thanks for post these examples. But i must do a submit for the name i found, but sending another variable, not the name, an numder id. In the query i found and show the two var, but i dont know how send it.

    Thanks!

    Diego

  35. hi
    how can i use this for combo box… as you see in http://www.justdial.com
    once we get auto suggest on city box, i want to populate the locality box in same manner, how do i pass the city value to locality query.

  36. THANK YOU !!

  37. Hi Johnathon :

    Thanks for the script. But I want to do something more that I can’t find anywhere.

    When I type something in the input field and several matches popup and I click the desired one. So, instead of clicking the search button, direct clicking on the matched one, open the filtered results.

    I hope, you understand what I am talking about.

    Please check the following websites
    http://www.timeanddate.com/worldclock/city.html?n=179
    http://www.sunrisemoonrise.com/daily/sunrisemoonrise.html?l=10645

    Can you do an autosuggest like this ?

    I want an autosuggest like timeanddate.

    Let me know.

    S. Das

  38. Hi Jonathan,

    Thanks for sharing the script here but I want also different. I want this autocomplete/autosearch be embedded in my joomla module? or in my template? I really dont know what do to?

    Can you help me do this? thanks

  39. Wow! i’m so glad that if found your web site I will be marking this for later!
    – jquery is a passion of mine and and your “jQuery Auto Suggest with MySQL | jonathan stegall: creative tension” article is surely great
    work.
    keep it comming, I’ll check back again soon!

    My weblog … realestatezurichswitzerland

About the Designer

Jonathan Stegall is a web designer and emergent / emerging follower of Jesus currently living in Atlanta, seeking to abide in the creative tension between theology, spirituality, design, and justice.

Elsewhere