Live search is starting to become more and more mainstream. With the advent of Ajax the ability to carry out searches without leaving the page have obviously started to appear more and more. The trouble with live searching though, is it can be quite resource intensive. Every keystroke in theory returns a bunch of search results, perhaps relevant, perhaps not. On a busy site this could have quite an impact.
In this tutorial I will demonstrate the same basic theory, live searching (demo) – however I am not showing the user any results until they execute the search. Instead, I am responding with a search result count in a live manner (after every key stroke), but actually returning the results on a button click or [ENTER] key press.
Live MySQL searching with jQuery (Click for demo)
What this does is cut down on the amount of data whizzing around after every keystroke, cutting down database activity and more importantly bandwidth reduction. It also gives the user some relevant feedback on their search so they can see if their search term actually matches any results in the first place before submitting it in a very economical fashion.
Click Here For Demo
The HTML
First up, lets create the basic HTML page. A couple of containers for the form elements and a container for the results.
<div id="form"> <input type="text" id="searchbox" name="searchbox" /> <div id="buttonContainer"> <a class="button" id="submitbutton" href="#"><span id="buttontext">Search</span></a> </div> </div> <div id="resultsContainer"></div>
The PHP
The PHP part of this is very simplistic in its approach, it is returning HTML data rather than XML or JSON objects – it’s up to you to take that next step. The script has two roles, 1 – To return a result count number, and 2 – to return search results. You could seperate these into two scripts if you want, but I’ve put them into one for now…
if(isset($_GET['query'])) { $query = $_GET['query']; } else { $query = ""; }
if(isset($_GET['type'])) { $type = $_GET['type']; } else { $query = "count"; }
if($type == "count")
{
$sql = mysql_query("SELECT count(url_id)
FROM urls
WHERE MATCH(url_url, url_title, url_desc)
AGAINST('$query' IN BOOLEAN MODE)");
$total = mysql_fetch_array($sql);
$num = $total[0];
echo $num;
}
if($type == "results")
{
$sql = mysql_query("SELECT url_url, url_title, url_desc
FROM urls
WHERE MATCH(url_url, url_title, url_desc)
AGAINST('$query' IN BOOLEAN MODE)");
while($array = mysql_fetch_array($sql)) {
$url_url = $array['url_url'];
$url_title = $array['url_title'];
$url_desc = $array['url_desc'];
echo "<div class=\"url-holder\"><a href=\"" . $url_url . "\" class=\"url-title\" target=\"_blank\">" . $url_title . "</a>
<div class=\"url-desc\">" . $url_desc . "</div></div>";
}
}
You’ll need to ignore the table and column names in the MySQL query, however you get the idea. For the “count” section the query is simply returning a result count to the browser, e.g. “3″. For the “results” section the script is returning chunks of HTML that our jQuery will consume and display on the page.
You also need to consider tightening up security on the form input, so validate what is coming in to ensure there is no SQL injections going on etc – I’ve really tried to just give you the basics here.
The jQuery
Finally, the jQuery. I am also using a snipsy bit of jQuery UI which you will need to include (but you can get all that from the download link at the end of this article).
$(document).ready(function() {
$("#searchbox").keyup(function(){
$.get("search.php",{query: $("#searchbox").val(), type: "count"}, function(data){
$("#buttontext").html(data + " Results Available");
});
});
$("#searchbox").keyup(function(event){
if(event.keyCode == "13")
{
getResults();
}
});
$("#submitbutton").click(function(){
getResults();
});
function getResults()
{
$.get("search.php",{query: $("#searchbox").val(), type: "results"}, function(data){
$("#resultsContainer").html(data);
$("#resultsContainer").show("blind");
});
}
});
So on the keyup event on the search box, my live search count is being fired and returning this count number into my form button. Depending on the performance of your hardware this will be instant, or a bit laggy. Once you are satisfied with your search term and you know that there will be results, you can hit the “Enter” key or the button to display the results. At that point, the PHP script is called again and returns the HTML chunks as described before.
Download
Download
As usual, click the box icon on the left to download the code in full including CSS and supporting image files. Hope you enjoy it, you’ll need to change the MySQL database connection variables, as well as the queries against the relevant tables etc…
Tools to help you learn…
 Learning jQuery 1.3 |
 PHP MySQL Development |
 jQuery UI 1.6 |
Similar Articles:
From MySQL to jQuery, via PHP, XML & Ajax
Custom 404 with jQuery and jQuery UI
An Introduction to jQuery UI - Part 2
Q&A with jQuery UI Creator Paul Bakaus
Authenticating Twitter API calls with PHP & jQuery
Tags: jQuery, jQuery UI, Live Search, MySQL, PHP
Great article thank you – I’ve been looking for a good solution to present large amounts of forensically obtained data (up to 1TB) to users for searching. This is great. Thanks again.
Fantastic Script! TY
That’s a smart idea, and the execution is very slick. Thanks!
This is great, nice and elegant.
I’m implementing it slightly differently; using mysqli in the PHP, and filling a separate text node with the number of results, rather than the text of the button. The PHP also echoes “break” when there is an error in connecting to the database or anything along those lines, so I’ve also put a check into the function(data) { } block, to catch that and empty the text node so as not to confuse a user who expects it to still be working.
Hitting enter also triggers submit() on the form to send the data to a separate PHP page for more complicated processing.
Thanks for giving me a few of the basics I needed to get started with jQuery.
hi 10x u for it !
But i have a small problem with it.
When i search in English language there is no problems but if i try to search in bu language – bulgarian i can`t find nothing
Hi, This looks great but when I downloaded the files and set up the database and uploaded them to my FTP it doesn’t work. When I type anything into the text field I get an error message over the button which says “Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/username/public_html/live-search/search.php on line 20″
Can anybody tell me what is wrong?
Thanks
I’ve been trying to solve the problem but I still keep getting the errors:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/user/public_html/live-search/search.php on line 20
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/user/public_html/live-search/search.php on line 33
There seems to be a problem with the file search.php
This is a nice script and I would like to get it working.
Can someone help?
Thanks
Could someone explain how to set the below up which are in Steve’s instructions? Maybe this is the problem I am having (see above)
“as well as the queries against the relevant tables etc…”
Brand new to jQuery, I nevertheless got your code working with Oracle on the 1st try. I then able to bend it to my ends. Thanks much.
I’m having the same problem as Martin
I like th demo, but I’m having the same MYSQL error as the others.
i have been trying and implementation in my own database, but it there not show the result?
there no change script, just the database…
I get the same errors also. Anyone help?
It seems like the software hasn’t been tested and finished properly. Is there anyone out there who could fix it?
Thanks
The script works – the mysql errors likely mean you are not forming your MySQL query correctly, or referencing tables and/or columns that don’t exist.
The livesearch is not working properly due to database …The database creates problem at the time of executing the file..
For my final year project i have a search feature written in PHP and it is linked to a My SQL database which displays a map written in text on my website when searched. What i have to do is have the map currently written in text displayed as a series of images and icons when searched in the database. I was wondering could you please advise me of a possible way to do this!
Thanks
Darragh Nugent
George Herson: How did you get this solution to work in Oracle? I’m assuming that you got it working via PL/SQL, which is exactly what I am trying to accomplish. I get stuck at where to place the PHP code and how to translate the GET commands into PL/SQL. Anyone?
I have implementen this script succesfully. It is great and the way it shows the results with a nice jquery blind effect is fantastic. I placed it for a friend who has a moviedatabase included with pictures. Unfortunately I discovered that the script does not work in IE 7 and 8. It works in Firefox 3.5 and Safari 4.0 . Do you have any ideas about this.
With kind regards.
Rienk
The Netherlands
Cool script, how could you use this to display the number of records only without the search? I would like to have a dynamic counter that will show the number of results of my query then as I add or delete records the counter will go up or down.
I have same errors with the others on line 20 and 33, is someone here to help me solve the problem?
Hello, could help me create the database?
Thank you
The Script Works Fine. It also works fine in IE 8. The problem you are probably all having is with your sql query and not with the script itself.
Great Script Thanks for sharing!
I miss a close button for the Result DIV – Great Work! Greetings from Germany
I was spending hours (im lieing lol) trying to find a tut like this. You save my time bro!
@Eric:
Your web hosing provider should enable this for you. It may cost you tho or it depends what package they offer. If you’re hosting your own website on your local computer/server, then you need to learn SQL.
Nice script! Thank you.
I’m no expert im programming but I like to try some new stuff…
I’ve made some changes to the search.php file to improve the search result.
The WHERE/MATCH/AGAINST expression will only present you the number of results where the input word matches exactly the word on the database.
So using WHERE/LIKE [SELECT field FROM table WHERE field LIKE '%".$query."%' ] you will get more results and the number of results will be getting more accurate as you’re typing.
It worked for me.
I also made some changes on the controller.js…
First I tryed to use a live ‘as you type’ result presentation but it turns out to be a very slow script for big databases [like mine].
Then I’ve added a new ‘if’ that presents results when ever user presses the space bar. This way, if you are searching for an expression with more than one word, results will be presented every time you type a new word.
That’s it. Thanks again
Excellent tutorial Reynold ! I have created a similar tutorial about how to implement a live search using php/mysql and jquery here :
http://youhack.me/2010/04/28/creating-a-fancy-search-feature-with-php-mysql-and-jquery/
Have fun
server say ::
Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘db61448′@’72.47.228.23′ (using password: YES) in /nfs/c04/h02/mnt/61448/domains/demos.reynoldsftw.com/html/live-search/search.php on line 8
Error connecting to mysql Results Available
Check the demo… i think You got problems with mysql user….