Server Side Filtering using jQuery Ajax PHP and MySQL

When you have records or data in thousands or million, it is difficult to find any data or record Manually.  For that type of problem, we use filtering to find record from thousands or millions. If you are working with lot of data, the you will obviously need of filtering. There are lot of benefits of using filter like you can save your time and you can find all the user which name starts from specific character. You can also apply sorting by ascending, descending, Newest record and more. Today we are going to explore server side filtering using jQuery, Ajax, PHP and MYSQL. Let's start it step by step:


1.    Create Database with name filter from phpmyadmin or run following SQL command as shown below:

CREATE DATABASE filter

2. Create table with name users or run SQL query as shown below.

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  `email` varchar(40) NOT NULL,
  `phone` varchar(10) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

3. Now we will Insert some dummy records to show data in a table which will display by default as shown below:

INSERT INTO `users` (`id`, `name`, `email`, `phone`, `status`) VALUES
(1, 'discussdesk', 'discussdesk@gmail.com', '9999999999', 1),
(2, 'mohit', 'mohit@gmail.com', '9874890874', 1),
(3, 'manish', 'manish@gmail.com', '9734786527', 1),
(4, 'suraj', 'suraj@gmail.com', '9864873787', 0),
(5, 'Hitesh', 'hitesh@gmail.com', '9873278654', 0);

4. Create db_con.php file where you will put your Database Configuration as shown below:

<?php
$con = mysql_connect("localhost","root","");
if(!$con)
{
	echo "Database Not Connected";
}

$db = mysql_select_db('filter');
if(!$db)
{
	echo "Database Not Selected";
}
?>

5. Create index.php file with following code seeing below. In this file, we create simple table with some dummy data inserted in database. At the top, we add jQuery file and created form which contain a text box and button. At the bottom, you will see JavaScript function which runs when you click on submit button.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<?php include('db_con.php'); ?>
<form align="center" method="POST" action="#">
<input type="text" name="filter_input" id="filter_input">
<input type="button" name="search" value="Search" onclick="filter_data();">
</form>

<table  border="1" align="center" cellpadding="5" cellspacing="5">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Status</th>
</tr>
</thead>
<tbody id="userData">
<?php
				$sql = mysql_query("select * from users");
				$num_rows = mysql_num_rows($sql);
				while($result=mysql_fetch_array($sql)){
				$status = ($result['status'] == 1)?'Active':'Inactive';
				?>
<tr>
<td><?php echo $result['name']; ?></td>
<td><?php echo $result['email']; ?></td>
<td><?php echo $result['phone']; ?></td>
<td><?php echo $status; ?></td>

</tr>
				<?php } ?>
</tbody>
</table>

<script>
function filter_data()
{
varfilter_input = $('#filter_input').val();
 // alert(filter_input);
  $.ajax({
	  'type': 'POST',
	  'url' : 'get_data.php',
	  'data' : 'filter_input='+filter_input,	
	  success : function(result)
	  {
		  $('#userData').html(result);
	  }
  });
}
	
</script>


<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<?php include('db_con.php'); ?>
<form align="center">
<input type="text" name="filter_input" id="filter_input">
<input type="button" name="search" value="Search" onclick="filter_data();">
</form>

<table  border="1" align="center" cellpadding="5" cellspacing="5">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Status</th>
</tr>
</thead>
<tbody id="userData">
<?php
				$sql = mysql_query("select * from users");
				$num_rows = mysql_num_rows($sql);
				while($result=mysql_fetch_array($sql)){
				$status = ($result['status'] == 1)?'Active':'Inactive';
				?>
<tr>
<td><?php echo $result['name']; ?></td>
<td><?php echo $result['email']; ?></td>
<td><?php echo $result['phone']; ?></td>
<td><?php echo $status; ?></td>

</tr>
				<?php } ?>
</tbody>
</table>

<script>
function filter_data()
{
varfilter_input = $('#filter_input').val();
 // alert(filter_input);
  $.ajax({
	  'type': 'POST',
	  'url' : 'get_data.php',
	  'data' : 'filter_input='+filter_input,	
	  success : function(result)
	  {
		  //alert(result);
		  //$("#new_row").html(result);
		  $('#userData').html(result);
		  //vartr = $("#new_row").parent();
		  //tr.replaceWith(result);
	  }
  });
}
	
</script>

6. Create file get_data.php with following code as shown below. In this file, we run select with like query whenever ajax function call and return result to index.php which append tbody tag of table. Each time you change filtration then you will get different result.

<?php
include('db_con.php');
$filter_input = $_POST['filter_input'];
$where = "where name like '%".$filter_input."%' or email like '%".$filter_input."%' or phone like '%".$filter_input."%'";
$sql = "select * from users $where";
$res = mysql_query($sql);
$num_rows = mysql_num_rows($res);

if ($num_rows> 0) {
    // output data of each row
    while($result = mysql_fetch_array($res)) {
		$status = ($result['status'] == 1)?'Active':'Inactive';
        echo $html = "<tr id='new_row'>
		<td> ".$result['name']."</td>
						<td>".$result['email']."</td>
						<td>".$result['phone']."</td>
						<td>".$status."</td>
					</tr>";
    }
} else {
    echo "No Record Found";
} 

?>

7. Run index.php file will have the following screen. Now you can search anything whichever you think it might in your database otherwise you will get message No Record Found.

Share this Article on Social Media

All of my Scripts are ready to customized as per your requirement. Feel free to contact for script customization.

Contact me at discussdesk@gmail.com

"Note : It will be charged as per your customization requirement :)"

Get Updates, Scripts & Other Tutorials to Directly to your Email

Over 20000+ Happy Readers already subscribed. (We don't send spam email). Every email subscriber can get our latest updates and download our 100+ scripts.

Comments