Creating a Fancy Search Feature with PHP,Mysql and Jquery

• Tweet

• Tweet

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 :

1. 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 .
2. An Ajax call will be triggered and passed to the file “ajax-search.php”  only if the user type a minimum of 4 Characters.
3. During this Asynchronous search ,an animated  loading image will be displayed in the right side of the text box.
4. 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 .

Related Posts

1. Auto Load Page content every X second using Jquery
2. Username Availability Check in Registration Form using Jquery/PHP
3. An Alternative to Pagination : Facebook and Twitter Style
4. Creating Message boxes using css with fadein Effects using jquery
5. Create fancy contact form with CSS 3 and jQuery

Tagged as: Jquery, PHP, Search System