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.

Also, read:

• Build Invoice System with PHP & MySQL
• Build Live Chat System with Ajax, PHP & MySQL
• Build Comment System with Ajax, PHP & MySQL

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 may also like:

• Star Rating System with Ajax, PHP and MySQL
• Create Event Calendar with jQuery, PHP and MySQL
• Build Your Own CAPTCHA Script with PHP
• Convert Unix Timestamp To Readable Date Time in PHP
• Inventory Management System with Ajax, PHP & MySQL
• Create Live Editable Table with jQuery, PHP and MySQL
• Live Add Edit Delete datatables Records with Ajax, PHP and MySQL
• Stripe Payment Gateway Integration in PHP
• Export Data to Excel with PHP and MySQL
• Star Rating System with Ajax, PHP and MySQL
• Create Dynamic Bootstrap Tabs with PHP & MySQL
• How To Create Simple REST API in PHP

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