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.

15 Comments

  1. Joseph Mayes

    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. Jonathan

    @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. Burfelt

    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. Jonathan

    @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. Burfelt

    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. Jonathan

    @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. Paul O'Connell

    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. Jonathan

    @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. regal

    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. Jonathan

    @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. Ayman

    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. Arno

    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. Jonathan

    @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. MockY

    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. Jonathan

    @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.

Post a Comment

Please provide your name, email (will not be published), and a message.

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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