on Basics

Simple search with PHP, jQuery and MySQL

48 comments
compass
Tweet about this on TwitterShare on FacebookShare on Google+Share on LinkedInShare on RedditShare on StumbleUpon

You can see the demo for this tutorial here.

The best way to keep your web site visitors, is to help them find what they are looking for. If you manage to do it in a simple and beautiful way, their feeling of loyalty will increase and they will come back and search for more great articles.

I will show you how to build a simple, yet powerful, search form from which you will show your users search results without refreshing the page, giving your web site a nice touch.

I will create two files: search.php which will contain HTML and JavaScript and do_search.php which will contain the PHP side. First, our HTML and jQuery file:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>PHP, jQuery search demo</title>
<link rel="stylesheet" type="text/css" href="my.css">

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript">

$(function() {

    $(".search_button").click(function() {
        // getting the value that user typed
        var searchString    = $("#search_box").val();
        // forming the queryString
        var data            = 'search='+ searchString;
        
        // if searchString is not empty
        if(searchString) {
            // ajax call
            $.ajax({
                type: "POST",
                url: "do_search.php",
                data: data,
                beforeSend: function(html) { // this happens before actual call
                    $("#results").html(''); 
                    $("#searchresults").show();
                    $(".word").html(searchString);
               },
               success: function(html){ // this happens after we get results
                    $("#results").show();
                    $("#results").append(html);
              }
            });    
        }
        return false;
    });
});
</script>

</head>
<body>
<div id="container">
<div style="margin:20px auto; text-align: center;">
<form method="post" action="do_search.php">
    <input type="text" name="search" id="search_box" class='search_box'/>
    <input type="submit" value="Search" class="search_button" /><br />
</form>
</div>      
<div>

<div id="searchresults">Search results :</div>
<ul id="results" class="update">
</ul>

</div>
</div>
  
</body>
</html>

It is a normal HTML form that will do the POST to our back end file do_search.php.
For explaining the jQuery part, please read the comments above.

And here is our do_search.php:

<?php
//if we got something through $_POST
if (isset($_POST['search'])) {
    // here you would normally include some database connection
    include('db.php');
    $db = new db();
    // never trust what user wrote! We must ALWAYS sanitize user input
    $word = mysql_real_escape_string($_POST['search']);
    $word = htmlentities($word);
    // build your search query to the database
    $sql = "SELECT title, url FROM pages WHERE content LIKE '%" . $word . "%' ORDER BY title LIMIT 10";
    // get results
    $row = $db->select_list($sql);
    if(count($row)) {
        $end_result = '';
        foreach($row as $r) {
            $result         = $r['title'];
            // we will use this to bold the search word in result
            $bold           = '<span class="found">' . $word . '</span>';    
            $end_result     .= '<li>' . str_ireplace($word, $bold, $result) . '</li>';            
        }
        echo $end_result;
    } else {
        echo '<li>No results found</li>';
    }
}
?>

PHP code is commented so you can easily follow what is going on. If you got some results from the database, you will show them to your user and even bold the word that the user searched for inside results.

This is some simple CSS I used to format the form and the results:

body{ font-family:Arial, Helvetica, sans-serif; }
*{ margin:0;padding:0; }
#container { margin: 0 auto; width: 600px; }
a { color:#DF3D82; text-decoration:none }
a:hover { color:#DF3D82; text-decoration:underline; }
ul.update { list-style:none;font-size:1.1em; margin-top:10px }
ul.update li{ height:30px; border-bottom:#dedede solid 1px; text-align:left;}
ul.update li:first-child{ border-top:#dedede solid 1px; height:30px; text-align:left; }
#flash { margin-top:20px; text-align:left; }
#searchresults { text-align:left; margin-top:20px; display:none; font-family:Arial, Helvetica, sans-serif; font-size:16px; color:#000; }
.word { font-weight:bold; color:#000000; }
#search_box { padding:4px; border:solid 1px #666666; width:300px; height:30px; font-size:18px;-moz-border-radius: 6px;-webkit-border-radius: 6px; }
.search_button { border:#000000 solid 1px; padding: 6px; color:#000; font-weight:bold; font-size:16px;-moz-border-radius: 6px;-webkit-border-radius: 6px; }
.found { font-weight: bold; font-style: italic; color: #ff0000; }
h2 { margin-right: 70px; }

I showed the simplest way to build modern search form and get the results without refreshing the page. I hope you enjoyed this tutorial. You can ask questions in comments.

You can see the demo for this tutorial here.

Tweet about this on TwitterShare on FacebookShare on Google+Share on LinkedInShare on RedditShare on StumbleUpon



  • Pingback: Tweets that mention Simple search with PHP, jQuery and MySQL | CodeForest -- Topsy.com

  • Pingback: uberVU - social comments

  • http://www.musiclyricsnow.net/ lyric

    Great Story, hey I stumbled on to this story while surfing the web for random downloads. Thanks for sharing I’ll tell my friends about this too.

  • Hyder

    very nice article Zvonko .i love the effect ! I have created a similar turorial using php,mysql and jquery here :

    http://youhack.me/2010/04/28/creating-a-fancy-search-feature-with-php-mysql-and-jquery/
    I hope this will help someone .enjoy !

  • Zulian

    when I did as shown, it is saying an error;

    Fatal error: Class ‘db’ not found in C:\inetpub\wwwroot\do_search.php on line 6.

    I am using below code for db.php file

    • Zvonko

      @Zulian You can not post code here, could you send the code for db.php to coder@codeforest.net and I will help you.

  • Zvonko

    @Zulian You can not post code here, could you send the code for db.php to coder@codeforest.net and I will help you.

    • http://therippleffect.com/india/search.php Pradeep

      Hi Zvonko:
      First of all thank you for the code and the wonderful explanation. However, when I use your code I get this error: Fatal error: Call to undefined function india() in D:\Hosting\7520570\html\india\do_search.php on line 6.
      Could you please help me solve it?

    • http://therippleffect.com/india/search.php Pradeep

      Hi Zvonko:

      Just a minute ago or so I said I had an error with Fatal error: Call to undefined function india() in D:\Hosting\7520570\html\india\do_search.php on line 6. But I found your d.php file and so that error is gone and it works. However, I would like to get the 2nd column (url) info also posted next to to the title – Is that possible?

      Thanks again

  • Zulian

    Hi Zvonki,
    Can you show the db class code for this tutorial please?

  • Zulian

    Hi Zvonki,
    I tried with a db class I took from net and it is NOTWORKING, can you give us a source code file?

    • Zvonko

      Hi Zulian,

      you can download the small class file for this tutorial on http://codeforest.net/demo/db.rar .

      Put it in the same directory as above files, and modify the HOST, USER, PASS and DB_NAME to match your database.

      This class is only for this tutorial. I will release my full db class in one of future tutorials, so stay tuned.

  • Zulian

    Hi Zvonko,
    Thank you so much, I will test this today . and will look forward for complete db class.

    I have a very huge request… can you write a tutorial, where we can use as a complete CRUD system with file uploading, resizing? If you can write a tutorial using jquery, it will be really fantastic.

    Thank you again.

    Zulian

  • Zulian

    Hi Zvonko,
    I tested it and there is no error, but I think I need some more tution on this, because, now it shows a result only if it is not matched, but when I type an existing text, it is not showing anything. what I need is to get an exact match. I want a query like “SELECT * FROM mytable WHERE search == $word;, but I am not sure how I can do it with your lesson, I am very new yet.

    Thanks

    Zulian

    • Zvonko

      @Zulian try SELECT * FROM table_name WHERE name = ‘$word’

  • Acland

    Hi Zvonko, this is fantastic and though I’m not great at php or jquery I have it working on my database but the result I get returned to the form are not formatted with the tag so they just spill across the page. Can you tell me where the script is injecting the LI tags for the returned results?

    My work around was to simply add them into the $return_results as part of the text which works but I think I’m missing something with the jquery or script side – for the life of me I can’t figure out what that is.

    If you could explain how the script knows to add the LI to the returned results that would be great. I’m using your CSS file but I would have thought the script should at least call for the LI tag.

    thanks in advance,

    Acland

    • Zvonko

      @Acland I had a small error on the do_search.php, so now I added the li tags. Try to copy and paste now.

  • Chris

    I want to create a dopdown and a text search engine for my site. I have looked everywhere on the net for a tutorial. How can I modify the script to the text searches for a particular category while searches in another category to find an exact data from MYSQL.

    • Zvonko

      Could you explain more what exactly do you need?

  • tranvantin

    thank you tutorial ,but I think you don’t want share file database
    include(‘db.php’);
    $db = new db();

    please tell me reason .

    • Zvonko

      This was not the theme of the tutorial. Anyway, you can download the file here

  • curiosity

    Hi,
    I need to create a similar search tool but i need to present the user with hyperlinks for downloading the resources in my library..basically, my database contains paths to the files stored on the server..user should be able to search the doc with name and then get results as hyperlinks for downloading the doc…can you point me to a good resource to read for this or any kind of help would be appreciated….

    • Zvonko

      Basicaly you can use the whole example from above, just adjust the query to the database. Then when you print out the results echo the links.

  • sadirdin

    i probably please explaint fully how to create mysql database table
    step by step tutor us
    thanks very much

    • Zvonko

      OK, I will make a small tutorial on this in a few days, so you will have a step-by-step tutorial. Stay tuned

  • mohamed

    hello sir…….

    i got a error on this line wt can i do for this.
    $row = $db_sel->select_list($sql);

    the error is
    Fatal error: Call to a member function select_list() on a non-object in C:\wamp\www\library\details.php on line 63

  • Craig

    Hi Zvonko,

    Thank you for a great tutorial.

    I have the same issue as Zulian.

    No errors, no display of results just the bullets.

    Can you please help?

    • Zvonko

      If there are no results, probably something is wrong with query to the database.

    • Ray

      Hello every one for those who does not get any results i got the problem

      foreach($row as $r) {
      $result = $row['title']; // Here is the error

      Just Change $row['title'] by $r['title'] because here says that $row is now $r

      foreach($row as $r) {
      $result = $r['title']; // Here is the error Fixed

      • http://codeforest.net Zvonko

        Hi Ray,
        thanks for noticing the typo in my code. It is corrected now.

        Thanks again

  • philip

    how i use this to search image on my site ? im a new on php programing how do i put keywords on the images to make it searchable ? hope u can help me

    thank you

  • Peter Verkooijen

    The following variation works and looks great in Firefox, but IE, Safari and Chrome all try to open the custom_search.php file:

    jQuery(function() {

    jQuery(“.search_button”).click(function() {
    // getting the value that user typed
    var searchString = jQuery(“#search_box”).val();
    // forming the queryString
    var data = ‘search=’+ searchString;

    // if searchString is not empty
    if(searchString) {
    // ajax call
    $.ajax({
    type: “POST”,
    url: “/wp-content/themes/mytheme/custom_search.php”,
    data: data,
    beforeSend: function(html) { // this happens before actual call
    jQuery(“#results”).html(‘loading…’);
    jQuery(“#searchresults”).show();
    jQuery(“.word”).html(searchString);
    },
    success: function(html){ // this happens after we get results
    jQuery(“#results”).show();
    jQuery(“#results”).empty().append(html);
    Cufon.replace(‘h3′);
    }
    });
    }
    return false;
    });
    });

    Can you spot the problem?

    And is there a way to activate the script on enter, instead of clicking the search button? I prefer search fields without buttons – just put in a word and enter.

  • AbopliltRob

    thanks for this nice post

  • Pingback: Simple search with PHP, jQuery and MySQL

  • Dudu

    great tutorial !!!

    i used it in my webapp and looks great.

    i wanna add a link at the end of the table to allow delete of the row from the table and from the db.

    i gave the link a class=”delBtn” and did a onclick selector…but its not responding….

    any idea ???

    • http://codeforest.net Zvonko

      I need more info to be able to help.

  • don

    im planning to use this great work of yours sir..
    what im trying to put up is when for example the data to be shown are contact details of a person
    includes:
    full name
    contact
    location
    email address
    so when i enter the key word, i need it to be like per row result, if the keyword matches 2 matches, i need the 2 matches be listed 2 rows with the full contact details..
    for example

    i search for ajax
    then
    =========================================
    NAME | Location | Contact | Email ADD| ETC…
    =========================================
    ajax | random | 12321 | fdf@df.org | etc….
    =========================================
    don |ajax world| 4r334e4|43@dd.dd | ettc…….

    so the are obviously static…
    there 2 matches w.c have ajax as name and ajax world as the loc.. and if i add (“space” world) next to ajax world..
    the result will be filtered out and remains the don | ajax world etc… info..

    im looking forward to your reply!!
    thanks in advance!! :)

  • dan

    I know this was posted a while ago but I found it incredibly useful. Truly a fantastic tutorial; however, I am struggling with adding pages to this. The two things I’m trying to add to this are: 1) Display results in a table with multiple columns of information per record, instead of a list, and 2) add pages, i.e. only 20 results are shown, to see the next 20 the use clicks next or page 2 etc… . I have done pages before with searches, but never while utilizing jQuery and Ajax. Any help would be great!

    Thanks

  • Simon

    Thanks for this useful script! I use it, but I don’t know how to add listing of pages…

  • http://anders-studio.com Andy

    Hello. I have the same issue as Mohammed: Fatal error: Call to a member function select_list() on a non-object in …

    I’m not familiar with select_list. Is there something else that needs to go here?

    • http://codeforest.net Zvonko

      Hi Andy,

      you can download the small class file for this tutorial on http://codeforest.net/demo/db.rar .

      Put it in the same directory as above files, and modify the HOST, USER, PASS and DB_NAME to match your database.

      • http://www.anders-studio.com Andy

        That worked. I must have been using the wrong password info. Interesting that the error wasn’t more clear.

        I got it working and would like it customized to only show X characters on either side of the keyword (essentially, link to the page name and show some of the results from another field), but don’t trim the middle of a link (maybe strip all that instead). Do you have something in place for this? If not, is it something you’d be willing to program and I’ll hook you up with a donation? Let me know. Thanks.

  • http://www.explorelabs.com Tushar

    awesome tutorial.. explained very well.. Thanks a ton..
    What if I want to have a linked result (URL for every searched string) ..?

    • http://codeforest.net Zvonko

      To have links around titles, you would have to take it from your db and render it inside do_search.php.

  • Rajandran R

    Hi Zvonko,

    I tried the code and its working fine for me. But the code execution of search.php is little slower. How can be fasten?

    Rajandran R

  • Yogesh Yadav

    Hey Hi, This is Yogesh Yadav what does $db= new db(); means here…..is that an object of database configuration file

  • MadFly_something

    I would like to use this search function on a “offline” project I am currently trying to contruct. The rest of the website is done, and all the database stuff is working great. When i try to implement this search function, it only return what I searched for… how do i get it to display the entire row where it found the word i was searching for in a table. I’m guessing with some while loop thing. No idea how to do this… any help?