January 17, 2024 5:02 am

How to Integrate live search in PHP and MySQL with jQuery

I have received many requests from my readers for live search in PHP and MySQL with jQuery, so today i have decided to write a tutorial how to integrate live search in PHP and MySQL with jQuery. Searching is one of the most required feature in web sites and if it will be live searching it will show you fast result on page. In this tutorial we will create a database and a table insert records and start searching in it.

live-search-php-mysql-with-jquery

[wpdm_file id=30]DEMO

Database Details:

database name => phpgang
table name => live_search
column names => id, name, email

db.sql

Database file run in your mysql to create database and add data in table.

create database `phpgang`;
use `phpgang`;

CREATE TABLE `live_search` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `live_search` VALUES (1, 'Huzoor Bux', '[email protected]', '2013-08-29 11:04:36');
INSERT INTO `live_search` VALUES (2, 'Sameer Khan', '[email protected]', '2013-08-29 11:04:36');
INSERT INTO `live_search` VALUES (3, 'Ravi Khana', '[email protected]', '2013-08-29 11:05:31');
INSERT INTO `live_search` VALUES (4, 'Neelam Ara', '[email protected]', '2013-08-29 11:05:31');

 db.php

Database configuration file edit database name, user and password as per your configuration.

<?php
    $connection = mysqli_connect('localhost','phpgang_usr','******','phpgang');
    if (mysqli_connect_errno())
    {
         echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
?>

 index.php

Your index file where you have to add text box and include js and css.

<div class="content">
<input type="text" class="search" id="searchid" placeholder="Search for people" />&nbsp; &nbsp; Ex: <b><i>huzoor bux, neelam, ravi or sameer</i></b><br /> 
<div id="result"></div>
</div>

 JavaScript

Here is the javascript you need to add in you file which can show you data on key press.

<script type="text/javascript" src="jquery-1.8.0.min.js"></script>
<script type="text/javascript">
$(function(){
$(".search").keyup(function() 
{ 
var searchid = $(this).val();
var dataString = \'search=\'+ searchid;
if(searchid!=\'\')
{
    $.ajax({
    type: "POST",
    url: "result.php",
    data: dataString,
    cache: false,
    success: function(html)
    {
    $("#result").html(html).show();
    }
    });
}return false;    
});

jQuery("#result").on("click",function(e){ 
    var $clicked = $(e.target);
    var $name = $clicked.find(\'.name\').html();
    var decoded = $("<div/>").html($name).text();
    $(\'#searchid\').val(decoded);
});
jQuery(document).live("click", function(e) { 
    var $clicked = $(e.target);
    if (! $clicked.hasClass("search")){
    jQuery("#result").fadeOut(); 
    }
});
$(\'#searchid\').click(function(){
    jQuery("#result").fadeIn();
});
});
</script>

 CSS

CSS i have used in this tutorial  to show result well decorated as below.

<style type="text/css">
    .content{
        width:900px;
        margin:0 auto;
    }
    #searchid
    {
        width:500px;
        border:solid 1px #000;
        padding:10px;
        font-size:14px;
    }
    #result
    {
        position:absolute;
        width:500px;
        padding:10px;
        display:none;
        margin-top:-1px;
        border-top:0px;
        overflow:hidden;
        border:1px #CCC solid;
        background-color: white;
    }
    .show
    {
        padding:10px; 
        border-bottom:1px #999 dashed;
        font-size:15px; 
        height:50px;
    }
    .show:hover
    {
        background:#4c66a4;
        color:#FFF;
        cursor:pointer;
    }
</style>

Last file you have to create to fetch results from database.

result.php

I have used direct mysql queries to fetch records but you can also use mysqli or PDO as mysql_connect deprecated in PHP 5.5.

<?php
include('db.php');
if($_POST)
{
    $q = mysqli_real_escape_string($connection,$_POST['search']);
    $strSQL_Result = mysqli_query($connection,"select id,name,email from live_search where name like '%$q%' or email like '%$q%' order by id LIMIT 5");
    while($row=mysqli_fetch_array($strSQL_Result))
    {
        $username   = $row['name'];
        $email      = $row['email'];
        $b_username = '<strong>'.$q.'</strong>';
        $b_email    = '<strong>'.$q.'</strong>';
        $final_username = str_ireplace($q, $b_username, $username);
        $final_email = str_ireplace($q, $b_email, $email);
        ?>
            <div class="show" align="left">
                <img src="https://fbcdn-sphotos-e-a.akamaihd.net/hphotos-ak-prn1/27301_312848892150607_553904419_n.jpg" style="width:50px; height:50px; float:left; margin-right:6px;" /><span class="name"><?php echo $final_username; ?></span>&nbsp;<br/><?php echo $final_email; ?><br/>
            </div>
        <?php
    }
}
?>

Please share your comments and feedback.

Author Huzoor Bux

I am Huzoor Bux from Karachi (Pakistan). I have been working as a PHP Developer from last 5+ years, and its my passion to learn new things and implement them as a practice. Basically I am a PHP developer but now days exploring more in HTML5, CSS and jQuery libraries.


Tutorial Categories:

40 responses to “How to Integrate live search in PHP and MySQL with jQuery”

  1. Bala says:

    What if needed more than 1 input?

  2. Hafujin.Desu says:

    Hi, I want to direct url when I search? Please help me.

  3. file needed says:

    Trying to download the file. I’ve subscribed and clicked the link you emailed me but I still get:

    Sorry no email found subscribe below

    when trying to download the file.

  4. aneet says:

    Your other scripts are easy and pretty but not this one. Requires much work it doesn’t work on arrow up and down and also it should get selection in text box on enter

  5. Husni's Elemento says:

    I hope for the next tutorial you should use secure code. like OOP or PDO.

  6. Gus says:

    when i click the picture or $final_username, nothing happens.
    everything is fine only if i click everywhere else.

    any ideas?

  7. Mickz says:

    hello! how can i get primary key id of selected value from database using this method? thanks for your help. 😀

  8. mohicanin says:

    and again – simple tutorial which should help people, and such dumb mistakes?
    type: “POST”, url: “search.php”….
    but there’s no “search.php” file, because you called it…. result.php?

  9. gobind says:

    when you click the bold part of the search query result it dosent select the query…any solution to this please?…..it works if there is no formatting ie around the $q in the javascript file

    • Morten Hjerl-Hansen says:

      Workaround:

      jQuery(“#result”).live(“click”,function(e){
      var $clicked = $(e.target);
      if (e.target.nodeName == “STRONG”)
      $clicked = $(e.target).parent().parent();
      else if (e.target.nodeName == “SPAN”)
      $clicked = $(e.target).parent();
      var $name = $clicked.find(‘.name’).html();
      var decoded = $(“”).html($name).text();
      $(‘#searchid’).val(decoded);
      });

  10. zohaib says:

    i can’t download file plz give another link

  11. Kristiqn Aleksandrov Terziev says:

    Hi how can i get the id of the element that is clocked?

  12. Nick says:

    I do also have this problem:

    “when i click the picture or $final_username, nothing happens.
    everything is fine only if i click everywhere else.

    any ideas?”

  13. Habib ullah says:

    thanks for sharing this tutorial

  14. Ramesh says:

    it is working only if you add html.inc file….but if i remove this file it does not work

  15. kamal says:

    Please update the jquery code

    • Pro says:

      Here you go…

      $(function(){
      $(“.search”).keyup(function()
      {
      var searchid = $(this).val();
      var dataString = ‘search=’+ searchid;
      if(searchid!=”)
      {
      $.ajax({
      type: “POST”,
      url: “result.php”,
      data: dataString,
      cache: false,
      success: function(html)
      {
      $(“#result”).html(html).show();
      }
      });
      }return false;
      });
      jQuery(“#result”).live(“click”,function(e){
      var $clicked = $(e.target);
      var $name = $clicked.find(‘.name’).html();
      var decoded = $(“”).html($name).text();
      $(‘#searchid’).val(decoded);
      });
      jQuery(document).live(“click”, function(e) {
      var $clicked = $(e.target);
      if (! $clicked.hasClass(“search”)){
      jQuery(“#result”).fadeOut();
      }
      });
      $(‘#searchid’).click(function(){
      jQuery(“#result”).fadeIn();
      });
      });

  16. Marcello Patto says:

    I got this error: Warning: mysql_fetch_assoc() expects parameter 1 to be resource, string given in /home/webrokercom/public_html/simulador/result.php on line 12
    What is wrong, please?

    Tks!

    • Chris Serella says:

      That error means there is likely an error in your query you need to add the following between the closing bracket and semicolon in the mysql_query line:

      or die(mysql_error())

  17. Mahmoud says:

    Thanks a lot. It is working fine but I want to display the candidate’s rest details from table after clicking on his name from the drop down list. How can I call the other script doing that and passing the selected candidate id to it?

  18. Pro says:

    Fixed JavaScript:

    $(function(){
    $(“.search”).keyup(function()
    {
    var searchid = $(this).val();
    var dataString = ‘search=’+ searchid;
    if(searchid!=”)
    {
    $.ajax({
    type: “POST”,
    url: “result.php”,
    data: dataString,
    cache: false,
    success: function(html)
    {
    $(“#result”).html(html).show();
    }
    });
    }return false;
    });
    jQuery(“#result”).live(“click”,function(e){
    var $clicked = $(e.target);
    var $name = $clicked.find(‘.name’).html();
    var decoded = $(“”).html($name).text();
    $(‘#searchid’).val(decoded);
    });
    jQuery(document).live(“click”, function(e) {
    var $clicked = $(e.target);
    if (! $clicked.hasClass(“search”)){
    jQuery(“#result”).fadeOut();
    }
    });
    $(‘#searchid’).click(function(){
    jQuery(“#result”).fadeIn();
    });
    });

  19. Brock says:

    the site example doesn’t even work there…

  20. taj says:

    hi ! when click on image and text then it’s not display result in text box.. i display in image..pls give me code for this thank you for old code it’s nice

  21. taj says:

    hi ! when click on image and text then it’s not display result in text box.. i display in image..pls give me code for this thank you for old code it’s nice

  22. taj says:

    hi ! when click on image and text then it’s not display result in text box.. i display in image..pls give me code for this thank you for old code it’s nice

  23. taj says:

    hi ! when click on image and text then it’s not display result in text box.. i display in image..pls give me code for this thank you for old code it’s nice

  24. Dwayne says:

    This is a really good tutorial. How ever have you ever thought about writing a tutorial were you break each major line down and explain what it’s doing? For those of us who read this to learn and not too steal your work for our site, it would be beneficial. And once word got out that you are doing that you would have a lot more readers.

  25. name says:

    Thanks Huzoor for share the code 😉

  26. amit says:

    Hi Huzoor,

    I have country and city database [cities more then 2 L]. My Ajax search is very slow i have used indexing as well. can you please give me any idea.

  27. TALHA AHMED says:

    i want to use it in wordpress

  28. daniel says:

    Great tutorial!

    If i have in database “HTC Desire” and i search for “htc” it will lowercase the term and brings “htc Desire”… but i want it to appear as it is in database “HTC Desire”. How can i do that?

    And another thing. Now we select the result and then click on search … I want to be able to click directly on the results from search list. How can i do that?

    Thanks

  29. Pratik Patel says:

    I am not able see clicked option in textbox

  30. Bob Mars says:

    pdo?

Leave a Reply

Your email address will not be published. Required fields are marked *