One of the most popular features of any database driven site is probably a search functionality that allows visitors to search for information within the website. I have written a small article how to implement such a functionality using PHP/MySQL enhanced with Jquery. As you type in the search box , an asynchronous request will be made to query the database and displayed to the user .Below is a screenshot of the search system, an online demo of the application , and the download link for the full source code including the database sql .Type any of these keywords in the online Demo : “Jquery” , “javascript”,”download” for results to be displayed .
View Demo || Download full source code
Overview
This is how the System works :
- A user clicks into the text box,the default text “Begin Typing to Search” will disappear. This effect has been done using Jquery Watermark Plug in .
- An Ajax call will be triggered and passed to the file “ajax-search.php” only if the user type a minimum of 4 Characters.
- During this Asynchronous search ,an animated loading image will be displayed in the right side of the text box.
- Finally if there is atleast one match in the database , it will be displayed in a “div” on the page .
Creating Our Database
I have created a small database that contains a topic title and a topic description .The search system will allow us to search in these 2 fields.
Below is the SQL code to create the table and add some data to it .
CREATE TABLE IF NOT EXISTS `topics` ( `topicid` int(5) NOT NULL AUTO_INCREMENT, `topictitle` varchar(200) NOT NULL, `topicdescription` varchar(500) NOT NULL, PRIMARY KEY (`topicid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; INSERT INTO `topics` (`topicid`, `topictitle`, `topicdescription`) VALUES (1, 'What is jquery ?', 'jQuery is a lightweight cross-browser JavaScript library that emphasizes interaction between JavaScript and HTML. It was released in January 2006 at BarCamp NYC by John Resig. Used by over 27% of the 10,000 most visited websites, jQuery is the most popular JavaScript library in use today.'), (2, 'What is javascript', 'JavaScript is an object-oriented scripting language used to enable programmatic access to computational objects within a host environment. Although also used in other applications, it is primarily used in the form of client-side JavaScript, implemented as part of a web browser, providing enhanced user interfaces and dynamic websites. JavaScript is a dialect of the ECMAScript standard and is characterized as a dynamic, weakly typed, prototype-based language with first-class functions. JavaScript '), (3, 'Where to download Jquery ?', 'You can download this javascript framework at jquery.com ?'), (4, 'How can i develop jquery Plug in ?', 'Well, First of all you need a working brain !');
Create the Form
For the sake of simplicity, I will omit the CSS design part of the search system. Since the Search is done “live” as soon as the user starts typing, a submit button will obviously not be required. Below is the HTML part of text field:
<input name="query" type="text" id="faq_search_input" />
Creating our Database Connection
Filename : database_connection.php
I will create a Database connection file separately that contains username password, host, and Database name.
You should change this info if you want to test this script on your own server.
/*Define constant to connect to database */
DEFINE('DATABASE_USER', 'root');
DEFINE('DATABASE_PASSWORD', '');
DEFINE('DATABASE_HOST', 'localhost');
DEFINE('DATABASE_NAME', 'programming');
// Make the connection:
$dbc = @mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD,
DATABASE_NAME);
if (!$dbc) {
trigger_error('Could not connect to MySQL: ' . mysqli_connect_error());
}
Code to handle the search query
Filename : ajax-search.php
When a user will type in the search box ,an ajax call will be made to this file via GET and the result will be displayed to the user .
include_once ('database_connection.php');//Including our DB Connection file
if(isset($_GET['keyword'])){//IF the url contains the parameter "keyword"
$keyword = trim($_GET['keyword']) ;//Remove any extra space
$keyword = mysqli_real_escape_string($dbc, $keyword);//Some validation
$query = "select topictitle,topicdescription from topics where topictitle like '%$keyword%' or topicdescription like '%$keyword%'";
//The SQL Query that will search for the word typed by the user .
$result = mysqli_query($dbc,$query);//Run the Query
if($result){//If query successfull
if(mysqli_affected_rows($dbc)!=0){//and if atleast one record is found
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){ //Display the record
echo '<p> <b>'.$row['topictitle'].'</b> '.$row['topicdescription'].'</p>' ;
}
}else {
echo 'No Results for :"'.$_GET['keyword'].'"';//No Match found in the Database
}
}
}else {
echo 'Parameter Missing in the URL';//If URL is invalid
}
Using Jquery to make the Asynchronous request
Filename : index.php
This is probably the “meat” of this tutorial .
To make the default text “Begin Typing to Search..” to disappear , i have used the Watermark plug .After downloading the plug in , include the plug in the header section of your page .
<script type="text/javascript" src="js/jquery.watermark.js"></script>
The rest of the code below handles the Ajax submission and displaying the results on the page .
$("#faq_search_input").keyup(function()
{
var faq_search_input = $(this).val();
var dataString = 'keyword='+ faq_search_input;
The code above will capture the data entered by the user and save in the variable faq_search_input .This will be triggered only when the user type into the textbox .The variable datastring prepared the data in the correct format to be passed in the url .
http://example.org/ajax-search.php?keyword=Jquery
The code below will Pass the input data only if is it longer than 3 characters. Before making the request , the class name “loading” will be added to the input field . This will cause an animation loading image to be added .If the ajax request is successfull ,the server response returned by ajax-search.php will be displaced within the <div id=”searchresultdata”></div> .The css “loading” class previously added to the dom is now removed .This will cause the animated loading image to become static . ( The animated and static are 2 different image actually ) .
if(faq_search_input.length>3)
//If length of input field greater than 3
{
$.ajax({//Make the ajax call using GET
type: "GET",
url: "ajax-search.php",
data: dataString,//The data to pass to the file
beforeSend: function() {
$('input#faq_search_input').addClass('loading');
//Code responsible for the animated loaded image
},
success: function(server_response)
{//If successfull ,output server response in div
$('#searchresultdata').html(server_response).show();
//Show the result on the page
$('span#faq_category_title').html(faq_search_input);
if ($('input#faq_search_input').hasClass("loading")) {
$("input#faq_search_input").removeClass("loading");
} //And finally remove the animated loading image to make it static .
}
});
}return false;
});
});
Feel free to modify the code the way you want .If you have any question or need help , do no hesitate to drop a comment below . I hope you enjoyed this tutorial .I did my best to explain the code in this article .


{ 1 trackback }
{ 90 comments… read them below or add one }
← Previous Comments
iam abdullah raza lakhan php developer and i want to live search option like google oh the my website so send me full scripts of live as i can manage the code my website thanks
i will thankful for the script if you all are provide me thanks but very thanks
can i is use this to search images on my site ?
thanks
how do i insert image and clickable links on the result area ?
I have this working from the download files but cannot get it to work with the latest JQuery 1.4.4.
Can this tutorial be updated for 1.4.4?
Ignore my above comment it does work!
ur the best bruv. top script
Hi right now it is using fine but I want it on click and search should open in new page .
Please help……..
I worked my ass out trying to find a clean tutorial regarding this matter. Niiiiice tutorial man. THX!!!
Hi,
The code works well. But when I do a search I encounter the following error;
mysqli_real_escape_string() expects parameter 1to be mysqli
Please help not sure if this is a coding error or a bug in PHP
thanks
SS
@SS : what’s the exact word you typed to get this error ?
yep getting that error (mysqli_real_escape_string() expects parameter 1to be mysqli)
aswel :/
if you type/search for anything you get it @Hyder
HI
is there a way to make the search more broader? LIke when you want users to be able to get a search result for the first 3 letters they type allready?
how do i bold keyword which i search. I want to show this in bold please help me to out.
It’s a great search engine. I have one problem- parts of my database are in Kazakh and the search returns this data as question marks. Any work-around?
My best to you
Hi thanks for the tuttorial.
i have a problem, my mysql has greek and when iam trying to search for something it returns question marks.
thanks
hi thanks for the tuttorial,
i have a problem my database has greek terms and when i am searching it shows me question marks ?????
thanks
Awesome post, thank you for sharing!
@Manti: If you want results to show up sooner than the 4th character, edit this line:
if(faq_search_input.length>3)
//If length of input field greater than 3
@philip
Yes you can add images and click-able links.
Go to ajax-search.php file and after
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
add
echo “<a href = http://www.someplace.com" . $row['titleoflink'] . "“;
do same for image just echo out the path of the image in your root and wrap it with html img tag.
am trying to create a hyperlink to a pdf file on a mysql database (using your live search script), the search results display properly problem is am not sure how to create a hyperlink to the pdf files.
i had a look at the echo “<a href = http://www.someplace.com" . $row['titleoflink'] . "“; line that you posted but i don't follow how i should edit it to allow for it to be linked to the search results
understand?
Great tutorial! How can I search multiple tables? Thanks for any help on this.
Ok, I’m playing around with this example and I changed it to do fulltext searches, but how would i be able to go about when there is nothing in the input field it won’t show any results instead of the previous results. Like when you do a search it shows the results and then you hit backspace it will not clear the previous results.
Hey there,
I’m trying to implement this in a new site im building, so far so good!
I’ve added a couple of extra fields to the query, so it will expand the search. But what my question is this.
Is it possible to display “all” database items, before a search is done. What i’d like to do is “show all” by default, and if some one makes a search, it will change to show the search results. This would be perfect if possible.
Thanks,
Adam
Hyder – I know this tutorial has been around for a while now, but I have just discovered it. With some tweaking it does everything I want. I just wanted to say a huge thanks for something that is so easy to integrate. Fantastic.
This is excellent. Would anybody know how to capture search queries and store them in the database or a log file?
Hey folks- I got clickable links working which rocks but is there any way to have the previous search intact when hitting the back buton? I just need a little push in the proper direction. Thanks in advance!
Excellent!!! Thanks for this great tutorial!!!
Hello please can anyone help i have ä,ü,ö in my database how can i show this in the search result?
please help
Thanks man really cool script!
Although I’m making some alterations to it.
Will add a link to this page in my script to give you credit for the awesome search feature.
you are great!!
Hello, so i was happy with the script but i needed another script to add data so here it is :
http://pastebin.com/F4cyCWSj
Please note that this script is not safe to keep on the server(so you might want to remove it after you add your data).
Nice tut, but there is an error whenever I type into the search bar.
Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli
Is this a bug?
Nevermind, I figured it out. – EXcellent Tutorial!
Is this script able to search other languages? say Japanese?
Here’s something interesting I found…..The script works great with eastern languages, on any other browser than IE…..any ideas anyone? It cant be that the browser doesn’t have the fonts installed. It is after all, a Japanese computer and it is reading other dynamic text just fine…just the search results are little squares…
Hi,
I have UTF-8 problemes with this code, and I see im not alone. Anyone knows what part of this code thats not supporting UTF-8?? That is characters like ÆØÅ ÃƒÂ®, ÃÂ, ü.
This problem does not occur when typing the words in the search bar, but when retreiving them from the database. Database is set to UTF-8 and the index.php file.
Getting characters like ÆØÅ from the database doesnt work, UTF-8 bug? Anyone? @Hyder?
Any ideas why this won’t work this new versions of jquery?
← Previous Comments