Wednesday 6 March 2019

PHP Ajax Live Search with Multiple Value



Do you know Search Live Data is required feature for filter Mysql to view only required data which you want to required. So, In this tutorial, we will learn how to make simple application in which we can search or filter Mysql database data with single search query with multiple value. On question aris how to search data with multiple search value in single click. For this here we will use Bootstrap input tags plugin for multiple search value in single search with Ajax jQuery and PHP.

Bootstrap Tags Input plugin is a jQuery plugin for converting input texts into tags format. After converting into tags we can enter another text which will also convert into tags. So, in single input text we will enter multiple tags, and this tags we will use for search data in Mysql database using PHP with Ajax. So, when we have click on search button, then at that time it will send multiple search query at the same time. So, this way we can use Bootstrap tags plugin for search live data with multiple search value and filter data according to multiple search values.

For initialize this plugin functionality, we have to just add data-role="tagsinput" attribute in our input fields, it will automatically convert input fields into tag input fields. So, it is very easy to use this plugin in our existing web development environment.

Here we will step by step learn How to use Bootstrap Tags Input plugin for make Live Data Search feature with Multiple values using Ajax with PHP and Mysql. For this here we will make live demo in which we can search customer data with multiple value.

At the server side how can we use multiple search value for filter data or make search mysql query with multiple input search value. For, this here we will use REGEX operator, this is another type of pattern based operator which is based on regular expression. Which we will use for search data with multiple search value. Below you can find complete step by step of PHP Ajax Live Data search with Multiple Values using Bootstrap Tags Input plugin.




PHP Ajax Live Search with Multiple Value


Step 1 - Mysql Database Table


For implement Live Data Search Functionality with Multiple Search Value for filter data, for this first we want to make tbl_customer table in mysql database. For this you have to run following SQL query for create table in your Mysql Database. This script will not only make tbl_customer but also it will insert some data also. So, it is easy for you implement this feature.


USE `testing`;

/*Table structure for table `tbl_customer` */

DROP TABLE IF EXISTS `tbl_customer`;

CREATE TABLE `tbl_customer` (
  `CustomerID` int(11) NOT NULL AUTO_INCREMENT,
  `CustomerName` varchar(250) NOT NULL,
  `Gender` varchar(30) NOT NULL,
  `Address` text NOT NULL,
  `City` varchar(250) NOT NULL,
  `PostalCode` varchar(30) NOT NULL,
  `Country` varchar(100) NOT NULL,
  PRIMARY KEY (`CustomerID`)
) ENGINE=MyISAM AUTO_INCREMENT=146 DEFAULT CHARSET=latin1;

/*Data for the table `tbl_customer` */

insert  into `tbl_customer`(`CustomerID`,`CustomerName`,`Gender`,`Address`,`City`,`PostalCode`,`Country`) values 
(1,'Maria Anders','Female','Obere Str. 57','Berlin','12209','Germany'),
(2,'Ana Trujillo','Female','Avda. de la Construction 2222','Mexico D.F.','5021','Mexico'),
(3,'Antonio Moreno','Male','Mataderos 2312','Mexico D.F.','5023','Mexico'),
(4,'Thomas Hardy','Male','120 Hanover Sq.','London','WA1 1DP','United Kingdom'),
(5,'Paula Parente','Female','Rua do Mercado, 12','Resende','08737-363','Brazil'),
(6,'Wolski Zbyszek','Male','ul. Filtrowa 68','Walla','01-012','Poland'),
(7,'Matti Karttunen','Male','Keskuskatu 45','Helsinki','21240','Finland'),
(8,'Karl Jablonski','Male','305 - 14th Ave. S. Suite 3B','Seattle','98128','United States'),
(9,'Paula Parente','Female','Rua do Mercado, 12','Resende','08737-363','Brazil'),
(10,'John Koskitalo','Male','Torikatu 38','Oulu','90110','Finland'),
(39,'Ann Devon','Female','35 King George','London','WX3 6FW','United Kingdom'),
(38,'Janine Labrune','Female','67, rue des Cinquante Otages','Nantes','44000','Finland'),
(37,'Kathryn Segal','Female','Augsburger Strabe 40','Ludenscheid Gevelndorf','58513','Germany'),
(36,'Elizabeth Brown','Female','Berkeley Gardens 12 Brewery','London','WX1 6LT','United Kingdom'),
(30,'Trina Davidson','Female','1049 Lockhart Drive','Barrie','ON L4M 3B1','Canada'),
(31,'Jeff Putnam','Male','Industrieweg 56','Bouvignies','7803','Belgium'),
(32,'Joyce Rosenberry','Female','Norra Esplanaden 56','HELSINKI','380','Finland'),
(33,'Ronald Bowne','Male','2343 Shadowmar Drive','New Orleans','70112','United States'),
(34,'Justin Adams','Male','45, rue de Lille','ARMENTIERES','59280','France'),
(35,'Pedro Afonso','Male','Av. dos Lusiadas, 23','Sao Paulo','05432-043','Brazil'),
(100,'Kathryn Segal','Female','Augsburger Strabe 40','Ludenscheid Gevelndorf','58513','Germany'),
(101,'Tonia Sayre','Female','84 Haslemere Road','ECHT','AB32 2DY','United Kingdom'),
(102,'Loretta Harris','Female','Avenida Boavista 71','SANTO AMARO','4920-111','Portugal'),
(103,'Sean Wong','Male','Rua Vito Bovino, 240','Sao Paulo-SP','04677-002','Brazil'),
(104,'Frederick Sears','Male','ul. Marysiuska 64','Warszawa','04-617','Poland'),
(105,'Tammy Cantrell','Female','Lukiokatu 34','HAMEENLINNA','13250','Finland'),
(106,'Megan Kennedy','Female','1210 Post Farm Road','Norcross','30071','United States'),
(107,'Maria Whittaker','Female','Spresstrasse 62','Bielefeld Milse','33729','Germany'),
(108,'Dorothy Parker','Female','32 Lairg Road','NEWCHURCH','HR5 5DR','United Kingdom'),
(109,'Roger Rudolph','Male','Avenida Julio Saul Dias 78','PENAFIEL','4560-470','Portugal'),
(110,'Karen Metivier','Female','Rua Guimaraes Passos, 556','Sao Luis-MA','65025-450','Brazil'),
(111,'Charles Hoover','Male','Al. Tysiaclecia 98','Warszawa','03-851','Poland'),
(112,'Becky Moss','Female','Laivurinkatu 6','MIKKELI','50120','Finland'),
(113,'Frank Kidd','Male','2491 Carson Street','Cincinnati','KY 45202','United States'),
(114,'Donna Wilson','Female','Hallesches Ufer 69','Dettingen','73265','Germany'),
(115,'Lillian Roberson','Female','36 Iolaire Road','NEW BARN','DA3 3FT','United Kingdom'),
(144,'Stephen M. Menzies','Male','577 Hartway Street','Bruie','57325','United States'),
(143,'Nikki G. Pascual','Female','4291 Kinney Street','Agawam','1001','United States'),
(141,'Alpha A. Brookover','Female','3542 Trainer Avenue','Kilbourne','62655','United States'),
(142,'Austin D. Salem','Male','1184 Farland Street','Brockton','2401','United States'),
(140,'Bianca A. Carone','Female','1777 Elkview Drive','Hialeah','33012','United States'),
(139,'Stephen M. Menzies','Male','577 Hartway Street','Bruie','57325','United States'),
(138,'Nikki G. Pascual','Female','4291 Kinney Street','Agawam','1001','United States'),
(136,'Alpha A. Brookover','Female','3542 Trainer Avenue','Kilbourne','62655','United States'),
(137,'Austin D. Salem','Male','1184 Farland Street','Brockton','2401','United States'),
(145,'Bianca A. Carone','Female','1777 Elkview Drive','Hialeah','33012','United States');


index.php


This is main file in this tutorial. On this page we have included all required library like jQuery, Bootstrap and Bootstrap Tag Input library. In this file you can file HTML code and jQuery code of this tutorial. For initialize Bootstrap Tag Input plugin on input textbox, we have to just add data-role="tagsinput" attribute in it. For display customer data here we have make one table, and in this tag data will be filled by using jquery function load_data(). This function has been use Ajax request for received customer data in json format and convert that json data to HTML using jQuery code.


<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>Live Data Search using Multiple Tag in PHP with Ajax</title>  
  <link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-tagsinput/0.8.0/bootstrap-tagsinput.css" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-tagsinput/0.8.0/bootstrap-tagsinput-typeahead.css" />
  <script src="https://code.jquery.com/jquery-2.2.4.min.js" integrity="sha256-BbhdlvQf/xTY9gja0Dq3HiwQF8LaCRTXxZKRutelT44=" crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-tagsinput/0.8.0/bootstrap-tagsinput.min.js" crossorigin="anonymous"></script>
  
  <style>
  .bootstrap-tagsinput {
   width: 100%;
  }
  </style>
 </head>
 <body>
  <div class="container">
   <br />
   <br />
   <br />
   <h2 align="center">Live Data Search using Multiple Tag in PHP with Ajax</h2><br />
   <div class="form-group">
    <div class="row">
     <div class="col-md-10">
      <input type="text" id="tags" class="form-control" data-role="tagsinput" />
     </div>
     <div class="col-md-2">
      <button type="button" name="search" class="btn btn-primary" id="search">Search</button>
     </div>
    </div>
   </div>
   <br />
   <div class="table-responsive">
    <div align="right">
     <p><b>Total Records - <span id="total_records"></span></b></p>
    </div>
    <table class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>Customer Name</th>
       <th>Gender</th>
       <th>Address</th>
       <th>City</th>
       <th>Postal Code</th>
       <th>Country</th>
      </tr>
     </thead>
     <tbody>
     </tbody>
    </table>
   </div>
  </div>
  <div style="clear:both"></div>
  <br />
  
  <br />
  <br />
  <br />
 </body>
</html>


<script>
$(document).ready(function(){
 
 load_data();

 function load_data(query)
 {
  $.ajax({
   url:"fetch.php",
   method:"POST",
   data:{query:query},
   dataType:"json",
   success:function(data)
   {
    $('#total_records').text(data.length);
    var html = '';
    if(data.length > 0)
    {
     for(var count = 0; count < data.length; count++)
     {
      html += '<tr>';
      html += '<td>'+data[count].CustomerName+'</td>';
      html += '<td>'+data[count].Gender+'</td>';
      html += '<td>'+data[count].Address+'</td>';
      html += '<td>'+data[count].City+'</td>';
      html += '<td>'+data[count].PostalCode+'</td>';
      html += '<td>'+data[count].Country+'</td></tr>';
     }
    }
    else
    {
     html = '<tr><td colspan="5">No Data Found</td></tr>';
    }
    $('tbody').html(html);
   }
  })
 }

 $('#search').click(function(){
  var query = $('#tags').val();
  load_data(query);
 });

});
</script>





fetch.php

This is PHP script file, which has handle ajax request for fetch data from Mysql database. In this script it has check there is any value of $_POST['query'] variable. If this variable has some value, then it will make Mysql query for filter data from tbl_customer table, and for search query it has use REGEX operator for search data with multiple value. But $_POST['query'] variable has no any value, then it will make query for fetch all data from tbl_customer table.


<?php

//fetch.php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

$output = '';

$query = '';

if(isset($_POST["query"]))
{
 $search = str_replace(",", "|", $_POST["query"]);
 $query = "
 SELECT * FROM tbl_customer 
 WHERE CustomerName REGEXP '".$search."' 
 OR Address REGEXP '".$search."' 
 OR City REGEXP '".$search."' 
 OR PostalCode REGEXP '".$search."' 
 OR Country REGEXP '".$search."'
 ";
}
else
{
 $query = "
 SELECT * FROM tbl_customer ORDER BY CustomerID
 ";
}

$statement = $connect->prepare($query);
$statement->execute();

while($row = $statement->fetch(PDO::FETCH_ASSOC))
{
 $data[] = $row;
}

echo json_encode($data);

?>


This is complete source code of Ajax PHP Live Data search with Multiple values by using Bootstrap Tags Input plugin. So, you have learn something new from this post. You can also check demo of this tutorial by click on below Demo link.






12 comments:

  1. Can you also include an error/pop up message if the data searched is not found?

    ReplyDelete
  2. Hi, Can you include error message incase a data is not found?

    ReplyDelete
  3. very nice man can you help me with {updating dynamic dropdown in php with ajax}

    ReplyDelete
  4. Can you show me how to connect to sql server? Sincere thanks!

    ReplyDelete
  5. Can you guide me how to connect sql server to php. Thanks very much!

    ReplyDelete
  6. Very Usefull keep it up thankyou very much.

    ReplyDelete
  7. I am so happy to get the code form you. it is really helpful

    ReplyDelete
  8. I have set regex to LIKE and would like to set OR to AND but when I change to AND nothing gets populated

    ReplyDelete
  9. can you update code to display error if the data is not found in database or user enter search without entering any input ?

    ReplyDelete