Server Side Filtering using jQuery Ajax PHP and MySQL

When you have records or data in thousands or millions, it is difficult to find any data or record Manually.  For that type of problem, we use filtering to find records from thousands or millions. If you are working with a lot of data, you will obviously need server-side filtering. There are a lot of benefits of using a filter like you can save your time and you can find all the user which name starts from a 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 PHP MyAdmin or run the following SQL command as shown below:

CREATE DATABASE filter;

2. Create a table with name users or run an 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 the following code seeing below. In this file, we create a simple table with some dummy data inserted in the database. At the top, we add a jQuery file and created a form which contains a text box and button. At the bottom, you will see the JavaScript function which runs when you click on the 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 the following code as shown below. In this file, we run select with like query whenever ajax function call and return the result to index.php which append body tag of the table. Each time you change filtration then you will get different results.

<?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 for anything whichever you think it might in your database otherwise you will get a message No Record Found.