Ajax Live Data Search using Multi Select Dropdown in PHP

Ajax Live Data search filters are mainly used to reduce data and display only that data which we want to get on any particular query. We have already publish ajax live data search using PHP mysql by enter query in textbox and display result on web page in table format without refresh of web page. But now here we have use select box for filter live data by using Ajax with PHP and Mysql. By using Select box we can filter mysql table data on particular criteria which we can define in select box. But here we have not filter mysql data by select single option from select box but here we have discuss and learn how can we filter mysql data by select multiple option from select box and based on option selected in select box it will filter mysql data and display on web page in table format by using Ajax.

We have create simple PHP search filter by using multi-select box in which we can select country from country list. For make stylish multi-select dropdown box we have use Bootstrap Select plugin. By using this plugin we have make stylish multi select dropdown box for select multiple country from countrylist. Here when we have select option from multi-select dropdown listbox then selected country name has been store in one hidden field and after this ajax request has been fire and it will send request to fetch.php page for select customer data whose country name has been make with selected option country list and it will return only that countries customer data in table format and by using jquery ajax code we have display filter data on web page. So on every country selection selected country name has been added into hidden field and ajax request will filter according country list selected and display on web page. For this whole process we have use Ajax with PHP and Mysql for search or filter data by using multi-select box.

Create Database

For this tutorial we have make simple below sql script, by using this script you can easily import data in mysql database.

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_customer`
--

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

--
-- Dumping data for table `tbl_customer`
--

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

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  ADD PRIMARY KEY (`CustomerID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;

View Demo

index.php

After successfully importing data into database then after we have to make index.php file in which we have write html code and Ajax Jquery code. Here for make multiple option select box we have use bootstrap select plugin and then after this multi-select box has been fill with country name which we have fetch from mysql database and display under this select box for filter mysql table data based on country selection.

After this we have make one table for display all customer data and filtered customer data also. For display all data from mysql table to webpage we have make one function with one argument, if we have called function without argument then it will display all data on web page but if we have pass argument which is name of country which we have select from multi select box then it will display filter data of that country which has been selected under multi-select dropdown list box.

<?php
//index.php

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

$query = "SELECT DISTINCT Country FROM tbl_customer ORDER BY Country ASC";

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

$statement->execute();

$result = $statement->fetchAll();

?>
<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>Ajax Live Data Search using Multi Select Dropdown in PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
  
  <link href="css/bootstrap-select.min.css" rel="stylesheet" />
  <script src="js/bootstrap-select.min.js"></script>
 </head>
 <body>
  <div class="container">
   <br />
   <h2 align="center">Ajax Live Data Search using Multi Select Dropdown in PHP</h2><br />
   
   <select name="multi_search_filter" id="multi_search_filter" multiple class="form-control selectpicker">
   <?php
   foreach($result as $row)
   {
    echo '<option value="'.$row["Country"].'">'.$row["Country"].'</option>'; 
   }
   ?>
   </select>
   <input type="hidden" name="hidden_country" id="hidden_country" />
   <div style="clear:both"></div>
   <br />
   <div class="table-responsive">
    <table class="table table-striped table-bordered">
     <thead>
      <tr>
       <th>Customer Name</th>
       <th>Address</th>
       <th>City</th>
       <th>Postal Code</th>
       <th>Country</th>
      </tr>
     </thead>
     <tbody>
     </tbody>
    </table>
   </div>
   <br />
   <br />
   <br />
  </div>
 </body>
</html>


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

 load_data();
 
 function load_data(query='')
 {
  $.ajax({
   url:"fetch.php",
   method:"POST",
   data:{query:query},
   success:function(data)
   {
    $('tbody').html(data);
   }
  })
 }

 $('#multi_search_filter').change(function(){
  $('#hidden_country').val($('#multi_search_filter').val());
  var query = $('#hidden_country').val();
  load_data(query);
 });
 
});
</script>

fetch.php

This page has received ajax request for fetch data from mysql table. On this page first it has check it has received any data from ajax request or not. If it has not received any data from Ajax request then it will return all data from mysql table. But suppose it has received any data from ajax request then that data will be name of country which has been selected by user. So first it will convert country list into array by using explode() function and then after it will again converted into string by using implode() function and make string which we can use in WHERE IN operator in query for filter data according selected country which has been come in mysql data.

<?php

//fetch.php

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

if($_POST["query"] != '')
{
 $search_array = explode(",", $_POST["query"]);
 $search_text = "'" . implode("', '", $search_array) . "'";
 $query = "
 SELECT * FROM tbl_customer 
 WHERE Country IN (".$search_text.") 
 ORDER BY CustomerID DESC
 ";
}
else
{
 $query = "SELECT * FROM tbl_customer ORDER BY CustomerID DESC";
}

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

$statement->execute();

$result = $statement->fetchAll();

$total_row = $statement->rowCount();

$output = '';

if($total_row > 0)
{
 foreach($result as $row)
 {
  $output .= '
  <tr>
   <td>'.$row["CustomerName"].'</td>
   <td>'.$row["Address"].'</td>
   <td>'.$row["City"].'</td>
   <td>'.$row["PostalCode"].'</td>
   <td>'.$row["Country"].'</td>
  </tr>
  ';
 }
}
else
{
 $output .= '
 <tr>
  <td colspan="5" align="center">No Data Found</td>
 </tr>
 ';
}

echo $output;


?>

This way we can make simple script for filter or search mysql data by selecting multiple option by using Ajax Jquery and PHP.

Download Source Code