Live Data Search with Multiselect Dropdown using Ajax, PHP & MySQL

Live Data Search functionality is very useful to filter data to see only required data. In this tutorial we have implemented live data search functionality with multi-select drop-down without using checkbox to filter data. The multi-select with checkbox functionality handled using Bootstrap-select plugin with PHP, MySQL and Ajax.



We will cover this tutorial in easy steps with live demo to work live data search functionality without page refresh with Ajax.



As we will cover this tutorial with live example to implement live data search using multiselect dropdwon with Ajax, PHP & MySQL, so the major files for this example is following.

• index.php


• search.js


• live_search.php

Step1: Create MySQL Database Tables
As we will implement functionality to filter live data, so first we will create MySQL database table developers using below query to store data to display.

CREATE TABLE `developers` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`gender` varchar(255) NOT NULL,
`designation` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
`image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


We will insert few records into developers table using below query.

INSERT INTO `developers` (`id`, `name`, `address`, `gender`, `designation`, `age`, `image`) VALUES
(1, 'Garrett Winters', 'Newyork', 'Male', 'Software Engineer', 34, 'image_1.jpg'),
(2, 'Sonya Frost', 'London', 'Female', 'Web Developer', 28, 'image_2.jpg'),
(3, 'Laeeq Khan', 'Delhi', 'Male', 'Web Developer', 32, 'image_3.jpg'),
(4, 'Smith', 'London', 'Male', 'Perl Developer', 27, 'image4.jpg'),
(5, 'William', 'Paris', 'Male', 'Java Developer', 28, 'image5.jpg'),
(6, 'Jhon', 'Sydney', 'Male', 'UI Developer', 30, 'image6.jpg'),
(7, 'Steven', 'London', 'Male', 'UI Developer', 34, 'image7.jog'),
(8, 'Rhodes', 'Newyork', 'Male', 'Web Developer', 25, 'image8.jpg');



Step2: Create Multiselect Dropdown List
Now in index.php file, we will create Multiselect dropdown list with location data from MySQL database table. The multiselect drodown list created with Bootstrap-select plugin without using checkbox.

<select name="multiSelectSearch" id="multiSelectSearch" multiple class="form-control selectpicker" title="Live data search by location...">
<?php
include_once("../db_connect.php");
$sql_query = "SELECT DISTINCT address as location FROM developers LIMIT 10";
$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
while( $developer = mysqli_fetch_assoc($resultset) ) {
echo '<option value="'.$developer["location"].'">'.$developer["location"].'</option>';
}
?>
</select>



We will create HTML table to display records on Ajax request.

<div class="table-responsive">
<table class="table table-striped table-bordered">
<thead>
<tr>
<th>Name</th>
<th>Age</th>
<th>Gender</th>
<th>Location</th>
<th>Designation</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>




Step3: Display Search Records from Multiselect Dropdown List
In search.js file, we will make Ajax request to live_search.php to load searched records when select/unselect item from multiselect dropdown list.
$(document).ready(function() {
listRecords();
$('#multiSelectSearch').change(function() {
console.log($('#multiSelectSearch').val());
$('#location').val($('#multiSelectSearch').val());
var searchQuery = $('#location').val();
listRecords(searchQuery);
});
});
function listRecords(searchQuery='') {
$.ajax({
url:"live_search.php",
method:"POST",
dataType: "json",
data:{query:searchQuery},
success:function(response) {
$('tbody').html(response.html);
}
});
}


Step4: Load Records from MySQL Database Table
In live_search.php file, we will load records from MySQL database table developers according to multiselect dropdown list selection. We will create HTML of searched records and return as JSON response.

<?php
include_once("db_connect.php");
if($_POST["query"] != '') {
$searchData = explode(",", $_POST["query"]);
$searchValues = "'" . implode("', '", $searchData) . "'";
$queryQuery = "
SELECT id, name, gender, address as location, designation, age
FROM developers
WHERE address IN (".$searchValues.")";
} else {
$queryQuery = "
SELECT id, name, gender, address as location, designation, age
FROM developers";
}
$resultset = mysqli_query($conn, $queryQuery) or die("database error:". mysqli_error($conn));
$totalRecord = mysqli_num_rows($resultset);
$htmlRows = '';
if($totalRecord) {
while( $developer = mysqli_fetch_assoc($resultset) ) {
$htmlRows .= '
<tr>
<td>'.$developer["name"].'</td>
<td>'.$developer["gender"].'</td>
<td>'.$developer["age"].'</td>
<td>'.$developer["location"].'</td>
<td>'.$developer["designation"].'</td>
</tr>';
}
} else {
$htmlRows .= '
<tr>
<td colspan="5" align="center">No record found.</td>
</tr>';
}
$data = array(
"html" => $htmlRows
);
echo json_encode($data);
?>


You can view the live demo from the Demo link and can download the full script from the Download link below.
Demo [sociallocker]Download[/sociallocker]