Auto Suggest with PHP & jQuery
Building a database driven auto suggestion box with PHP/MySql and jQuery. We’re going to check what a user has typed in, check against what is in our database and where there is a match, pass the results back in a suggestion list, all via an AJAX call in jQuery.
An Autocomplete widget using PHP and jQuery. We’re going to connect to check what a user has typed in, then go away and check from our database whether there is a match and then pass it back into a suggestion list, all via an AJAX call from jQuery.
Overview
- Program: PHP/MySql & jQuery
- Difficulty:Beginner/Intermediate
- Estimated Completion Time: 25-30 mins
The Aim
The aim of this tutorial is to create an input box which users can search our products database. If you’ve followed any of my other tutorials you’ll know I use bands as an example.
What we want to do is when the user types a letter, it triggers some jQuery which will use an AJAX call to our php page which simply searches the database for a match using a wildcard. In SQL, the percentage(%) sign is the wildcard so if we wanted to search for a band then typing “Michael” would bring up all the matching artists that start with Michael - so you might get Michael Jackson, Michael Jordan, Michael Moore. You get the idea.
When the search is complete, each matched record is put into it’s own list items and presented to the user. The user can click any of the results and it will automatically fill our input box with the records text.
Here is a graphical diagram of what we are going to be building:
Step 1 - Layout
Now you have seen what we are going to build, create a new file called “index.htm’” and if you haven’t already, fill it out with all your usual HTML markup (doc types, head, body etc).
Okay so first of all we need to link to our “scripts.js” file and also the jQuery file. You can use a local version of jQuery if you want, I generally use the one hosted by Google.
We also need to include our “styles.css” file. You can copy the below into your HEAD section of the page.
You’ll need to create a styles.css file and put in a css folder.
<script type="text/javascript" src="js//jquery.min.js"></script> <script type="text/javascript" src="js/scripts.js"></script> <link href="css/styles.css" rel="style sheet" type="text/css" />
Now that we have dealt with the links to our external files, we can concentrate on setting up our container divs.
We’re going to use a container div to float everything to the center of the page. Within our container div we then need a form and an input box - this is where the user will enter their search string.
We then put in our suggestion div, but we are careful to set it’s display property to “none”. This means that it will not be shown on the page to begin with. We’ll be using jQuery to set its visibility later. I’ll put the code below, but effectively all we are doing is having a container for the search results, then another div to hold all our list items. I’ve added a little up-arrow in between for aesthetics but it’s not really essential.
The full code for our “index.html” file looks like this:
<html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Autosuggest with jQuery and PHP</title> <script type="text/javascript" src="js/jquery.min.js"></script> <script type="text/javascript" src="js/scripts.js"></script> <link href="css/styles.css" rel="style sheet" type="text/css" /> </head> <body><!-- Container to hold our Suggestion Box--> <div id="container"> <form> <div> <h3>Band Name</h3> <input type="text" size="30" value="" id="inputString" onkeyup="lookup(this.value);" onblur="fill();" /> </div> <!-- hide our suggestion box to begin with--> <div class="suggestionsBox" id="suggestions" style="display: none;"> <img src="upArrow.png" style="position: relative; top: -18px; left: 30px;" alt="upArrow" /> <div class="suggestionList" id="autoSuggestionsList"></div> </div> </form> </div></body> </html>
So that’s our “index.html” file sorted - easy right? It’s important to note the name our our input box - “inputString” as we’re about to use jQuery to latch onto this and then pass it to our php file.
Step 2 - The jQuery
The jQuery is really the middle-man of the process, it’s going to handle all the front-end stuff e.g. what the user see’s.
Lets have a look at what we want the jQuery to do:
- Check for an inputted string and hide the suggestion box if < 0
- If not blank, make an AJAX call t our “string_search.php” file and pass it a variable containing the string.
- If the search is successful, return the data and show our suggestion box.
Okay so that’s not so hard right? Lets get started.
Check for input string - we’re going to use inputString.length == 0 and if true, we’ll hide our “#suggestionbox”. The beauty of jQuery is it uses similar syntax to css!
If there is a string (e.g. the user has typed something) then we’ll use the $.post AJAX call to pass the string the user entered to our “search_string.php” file. Since this I function, we can use a call back when the database search is complete that will make our suggestion box visible, and then append the results into it.
Finally, when the user clicks an item we’re going to fill our input box with that value. To do this, we’re going to use the onClick event in our search_string.php file that will call this function. This is how jQuery knows when to trigger the code.
All this code is below, along with comments to talk you through it.
function lookup(inputString) {
if(inputString.length == 0) {
// Hide the suggestion box.
$('#suggestions').hide();
} else {
// post data to our php processing page and if there is a return greater than zero
// show the suggestions box
$.post("string_search.php", {mysearchString: ""+inputString+""}, function(data){
if(data.length >0) {
$('#suggestions').show();
$('#autoSuggestionsList').html(data);
}
});
}
} //end
// if user clicks a suggestion, fill the text box.
function fill(thisValue) {
$('#inputString').val(thisValue);
setTimeout("$('#suggestions').hide();", 200);
}
As you can see, jQuery is our link between our front end (what the user sees) and our back end (the php file that will search the database and return the files).
Step 2 - CSS Styling
I’m going to just dump the full CSS that I used below and you can modify as you see fit. There are some points to remember though:
- suggestionsBox is the box that your list items will end up in
- suggestsions li is the list items themselves
- suggestions li:hover is the styling applied on hover
That’s it, aside from making sure the input is the same width as our suggestions box, and I’ve made its position absolute to ensure it doesn’t push any content underneath down the page - this is optional depending on your needs.
So here is the final CSS:
body {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 11px;
color: #000;
background-color: #eeeeee;
}h3 {
padding: 0px;
font-size: 23px;
margin-top: 0px;
margin-right: 0px;
margin-bottom: 5px;
margin-left: 0px;
color: #000000;
}#container {
width: 250px;
margin-right: auto;
margin-left: auto;
margin-top: 25px;
margin-bottom: 0px;
padding: 20px;
-moz-border-radius: 10px;
-webkit-border-radius: 10px;
}
/* Styling for Suggestion Box Container */
.suggestionsBox {
position: absolute;
width: 240px;
background-color: #212427;
border: 2px solid #000;
color: #fff;
padding: 5px;
margin-top: 10px;
margin-right: 0px;
margin-bottom: 0px;
margin-left: 0px;
-moz-border-radius: 8px;
-webkit-border-radius: 8px;
}#inputString {
width: 240px;
padding: 5px;
font-size: 18px;}
.suggestionList {
margin: 0px;
padding: 0px;
}
/* Individual Search Results */
.suggestionList li {
margin: 0px 0px 3px 0px;
padding: 7px;
cursor: pointer;
-moz-border-radius: 3px;
-webkit-border-radius: 3px;
list-style-type: none;
}/* Hover effect */
.suggestionList li:hover {
background-color: #009900;
font-weight: bold;
}
So now we have our index.html file which has our input box, we have the css file to style the box and the returned suggestiions, we have our jQuery file listening for user keystrokes and we have our php file - string_search.php which handles the database connections and searching.
To give you a bit of a visual of the process, here is a very basic diagram of what is happening here:
Step 3 - The PHP Magic
Okay so we now have our front end page - index.html, our jQuery file - scripts.js and now we need to write the php that will connect to our database, search for any records containing the input string and then echo out a list item for each result.
Create a file called “string_search.php” and we’ll be using this for this part of the tutorial.
Before we do anything, we need to connect to database. We could use a separate file for this, and it’d be good practice to do so however for sake of simplicity I’ll include it in the same file.
// since this is a one page tutorial // we're not separating out the database connection $db_host = 'localhost'; $db_user = 'root'; $db_password = ''; $db_name = 'products'; $db = new mysqli($db_host , $db_user ,$db_password, $db_name);
Now we are connected, we need to:
- Assign the “mysearchString” passed from jQuery to a variable.
- Use this variable in our SQL statement - we can use the percent symbol as or wildcard.
- Loop through the results and put each one into it’s own list item.
That’s basically it, there’s nothing really complicated in this approach although I am sure many of you will have much more robust methods of achieving the same results.
Lets first check to see what what the user entered, and then put this into a variable:
// Check the user has typed something in our input box.
if(isset($_POST['mysearchString'])) {
$mysearchString = $db->real_escape_string($_POST['mysearchString']);
So now we’ve stored the search string to a variable, we need to run a query on our database:
$query = $db->query("SELECT product_title
FROM products
WHERE product_title
LIKE '$mysearchString%'
LIMIT 10"); // limits our results list to 10.
This query is only pulling the product_title from our products table - in a real world situation you’d obviously change these to be your own field and table names, and you’d most likely use some form of protection against sql injection. For the sake of keeping things simple, I’m going to leave this as it is.
Finally, we need to take each record returned from the query and echo it out into a list item that will be in our suggestionbox. We also need to attach an onClick event to the list items, which will trigger our jQuery for filling in the input box with our result.
if($query) {
// so while there are results from the query
// we loop through the results and fill out our list items
while ($result = $query ->fetch_object()) {
// create a list item, but also listen for the user clicking
// the result so we can fill the text box.
echo '<li onClick="fill(\''.$result->product_title.'\');">'.$result->product_title.'</li>';
}
} else {
echo 'ERROR: There was a problem with the query.';
}
And that’s it! The final string_search.php file is below which you might want to copy and paste as a whole as I’ve been selective in the code snippets I’ve shown you.
<?php
// since this is a one page tutorial
// we're not separating out the database connection$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'sampledb';
$db = new mysqli($db_host , $db_user ,$db_password, $db_name);if(!$db) {
// If there is an error, show this message.
echo 'There was a problem connecting to the database';
} else {
// Check the user has typed something in our input box.
if(isset($_POST['mysearchString'])) {
$mysearchString = $db->real_escape_string($_POST['mysearchString']);
// Is the string length greater than 0?
if(strlen($mysearchString) >0) {
// Now we have the string the user entered, we want to
// be able to use this to search in our database
// so we use the percentage as the wildcard and use a variable
// in the query.
$query = $db->query("SELECT product_title
FROM products
WHERE product_title
LIKE '$mysearchString%'
LIMIT 10"); // limits our results list to 10.
if($query) {
// so while there are results from the query
// we loop through the results and fill out our list items
while ($result = $query ->fetch_object()) {
// create a list item, but also listen for the user clicking
// the result so we can fill the text box.
echo '<li onClick="fill(\''.$result->product_title.'\');">'.$result->product_title.'</li>';
}
} else {
echo 'ERROR: There was a problem with the query.';
}
} else {
// Dont do anything.
}
} else {
echo 'Access denied.';
}
}
?>
Summary
We now have a working autocomplete input box that uses a database for it’s suggestions. It’s probably not a great idea to use it on a large scale database since we are doing lots of db calls every time a user enters a letter but for small to medium size sites, or maybe a web application then it’s idea.
It’s important to note that features like this are enhancements to a website but shouldn’t be relied upon, for if JavaScript is switched off none of this would show up to the user.
It always amazes me how little coding we had to do to achieve this, and that’s mainly down to AJAX and how simple jQuery makes it for us. It wasn’t so long ago that we had to rely on separate search and results pages.
I hope you’ve enjoyed this tutorial, feel free to leave some comments as I’m sure alot of you will have your own improvements to make.
Great tutorial! But could you give the ZIP files? Thank You.
Sure! I’ve put the zip file right at the end of this post!
I am getting the error “no element found” in firefox, can you please help me how to solve this?
hi Yamini - I would help if I knew more about the error, does it give you any specifics like line numbers etc?
Great tutorial! It took me a while to find a guide that broke auto complete down for me and make it easy to get up & running. Thanks
the source file is different with the tutorial, but it is great work
Hello Jeff Adams:
This is sheik from india working as a php developer.Thank you so much for your useful post..Keep up the good work jeff.
Nice succint tutorial, well done!
Hello jeff adams:
This is sheik from india.everything is working fine in firefox. the page is not opening in IE because of jquery.Thanks for your help in advance.
Hello jeff:
i solved the problem jeff.first i included jquery-1.2.1.pack js file.that file makes problem in IE.But jquery.min js file works fine in IE.Thanks.
@ sheik mohamed - always great to see people using this, feel free to post a link for others to see it in action!
Hi, this is a great script and thanks. My one issue with it, which I cannot figure out how to do, is to be able to use the arrow keys to scroll down the dropdown options. Is there a way to implement this? I feel like it is essential for these kind of search bars..
Thanks!
hai jeff:
arrow is a png image..its not supporting in IE6 or lower.Anyway thanks a lot for your post
Hi,
Great tutorial, everything works fine.
Just one question - how could you implement this twice on one page for example I would need one textbox to search for ISBN of a book and a separate textbox to search a different mysql table for member names.
Any ideas would be great
Kind regards
Tom
Hi Thomas!
To implent two of these on the same page you would first need 2 seperately named input boxes, then change the jquery - one points to a php file that you can leave alone, so point the other to a new php file thats effectively a copy of the first - only this new one has all your new query parameters in it.
Hope that helpss, there’s going to be more gracious ways of doing this but for simplicity, I’d have two seperate php files doing the work.
Can someone please let me know, the hover doesnt work on IE 7 and as I move over the suggestions box the arrow key doest show i just see the cursur like a capital “i”. Also how to I enable the suggestions box to use the top and bottom arrows on the key board and enable the selection with the enter key.
Your help would be greatly appreciated.
Thanks,
@admin Just came to post that I had got it working, exactly in the way you described! cheers anyway!
Why you call to the function fill without the parameter “thisValue”? Here:
onblur="fill();"But function is declared with parameter “thisValue”:
function fill(thisValue)How the function get this parameter?
Thanx a lot!
Hi,
Awesome tut and everything is working well.
How could you implement this on one page and on option is selected display the description in the result area below?
Any ideas would be helpful
Thanks
GP
does anybody have an application “addressbook” like thinf using jquery + php + mysql
Thanks in advance
Great!
How can I put a scroll bar in the ballon to see all database results in the page ?
(Sorry my english !)
Thank you.