Post image for Creating a Fancy Search Feature with PHP,Mysql and Jquery

Creating a Fancy Search Feature with PHP,Mysql and Jquery

by Hyder on April 28, 2010

in Jquery, PHP

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. Create fancy contact form with CSS 3 and jQuery
  5. Creating Message boxes using css with fadein Effects using jquery
After Post

{ 1 trackback }

Creating a Fancy Search Feature with PHP,Mysql and Jquery « php
April 29, 2010 at 12:06 am

{ 116 comments… read them below or add one }

ndech March 20, 2012 at 11:57 am

and what about pagination

Reply

deamon March 28, 2012 at 2:06 pm

thank you worked perfectly for my project =D

Reply

Henri April 6, 2012 at 10:40 am

hi. how can i search using the url? index.php?keyword=something doesn’t work.

Reply

Tayo pat May 10, 2012 at 8:07 am

Nice article…love it..it works fine..

Reply

I am July 7, 2012 at 8:45 am

worst code ever seen.
Thats the reason PHP developer are so bad, writing code in 10 lines but
1) no unit test
2) no OO
3) sql injection possible
4) full text search engine from mysql is so bad….. (use lucene instead)

Reply

Jeroen Diel August 10, 2012 at 10:18 am

Hi,

The script is really neat, however it is vulnerable to Cross Site Scripting (XSS) attacks because it lacks input and output validation. Perhaps a modified/fixed version of this script should be updated here since people are using it.

If you want me to help you with that, please feel free to contact me.

Regards, Jeroen.

Reply

Juan August 27, 2012 at 5:00 pm

Hi there, first of all let me thank you for such a great tutorial.
My question is, is it possible to include this as it is within a wordpress site? Under the wordpress structure of course.
Thanks a lot.

Reply

Hashi September 15, 2012 at 7:48 am

Thanks.

Reply

soiqualang-chentreu September 24, 2012 at 2:13 am

Thanks so much!!!

Reply

Alex Mulchinock October 13, 2012 at 12:44 am

This works great!
The only problem is our tables contains a LOT of data to search through (currently sitting at 500 entries and counting).
As a result, the script seems to freeze, and wont display any results (or a message to say “I can’t find anything”).

I’ve tested with a sample table of a much smaller size, and it works absolutely fine. So- it seems to be the table size causing the issue.
Any suggestions?

Reply

Fredrik November 6, 2012 at 2:35 pm

How can i put in swedish characters? lika “åäöÅÄÖ”
like there is now it wont find anything with that chars within serach string…

Reply

Leave a Comment

Previous post:

Next post: