Today i will show you how you can make  your own Ajax Filter Application to filter data from a database(source) and show it to user. Filter plays  a very important role, it allow to narrow down the result that the  user want, Filters can be of many types, it can be filtering data in ascending or descending order or alphabetically order or maybe a particular range that the user wants the data.

For this i will be using the jquery script which you can download it from here.

So first lets start with creating our table named ‘students’. The structure is as follows.

create table students
(
  id int(6) unsigned auto_increment PRIMARY KEY ,
  name VARCHAR(200) not null,
  course varchar(200) not null,
  roll_no int(6) not null,
  percentile VARCHAR(100) not null
)

Which contains some information about student’s name,roll number,course etc.

Now we move on to index.php page.

<link href="style.css" rel="stylesheet">
<script src="jquery.js"></script>

<div id="main">

<div id="header">
<h1>Students Database</h1>
</div>


<div id="content">

<table  id="fetch-table">

<tr>

<td>Filter By Course</td>


<td><select name="course" id="fetch-by-course">

<option value="B.COM (H)">B.COM (H)</option>

<option value="B.SC">B.SC</option>

<option value="M.C.A">M.C.A</option>

</select></td>


<td>Filter By percentile</td>


<td>
                    <input type="text" name="percentile" placeholder="Min-Max" id="fetch-by-percentile">
</td>


<td>Roll No.</td>


<td>
                    <input type="search" name="rollno" placeholder="Search For Roll No." id="fetch-by-rollno">
</td>

</tr>

</table>


<div id="output-data"></div>

In this #output-data is the element where the result will be palced. jquery.js is the Jquery script that i have already downloaded. So in the #output-data we will output all the results present in our table, so just place the script in #output-data


<table>

<tr style="background:white">

<th>Id</th>


<th>Name</th>


<th>Roll No.</th>


<th>Course</th>


<th>Percentile</th>

</tr>

    <?php
    $conn = mysqli_connect('localhost','root','','work');
    $query = "select * from students";
    $statement = $conn->prepare($query);
    $statement->execute();
    $statement->store_result();
    $statement->bind_result($id,$fname,$fcourse,$frollno,$fpercent);
    while ($statement->fetch()) //outputs the records
    {
        echo "
<tr>

<td>$id</td>


<td>$fname</td>


<td>$fcourse</td>


<td>$frollno</td>


<td>$fpercent</td>

</tr>
";
    };
    $statement->close();
    $conn->close();
    ?>

</table>

Now this fetches all the data present in the table. Now we will move onto fetch.php

Basic structure


if($_POST || $_GET){

if(isset($_POST['course'])) // if user filters data through course
{
};
if(isset($_GET['rollno'])) // if user filter or searches data through roll number
{
};
if(isset($_POST['percentile'])) // if user filters data by a particular range in percentile
{
};

};

Fetch.php

<?php
 if($_POST || $_GET)
 {
 $conn = mysqli_connect('localhost','root','','work'); //Connection to my database
 if(isset($_POST['course'])) // if user filters data through course
 {
 $course = $_POST['course'];
 $query = "select * from students where course = ?";
 $statement = $conn->prepare($query);
 $statement->bind_param('s',$course);
 };
 if(isset($_GET['rollno'])) // if user filter or searches data through roll number
 {
 $keyword = $_GET['rollno'];
 $keyword="%$keyword%";
 $query = "select * from students where roll_no like ?";
 $statement = $conn->prepare($query);
 $statement->bind_param('s',$keyword);
 };
 if(isset($_POST['percentile'])) // if user filters data by a particular range in percentile
 {
 $data = explode('-',$_POST['percentile']); // to convert the string into array 70-90
 $permin = $data[0];
 $permax = $data[1];
 $query = "select * from students where percentile < ? and percentile > ?";
 $statement = $conn->prepare($query);
 $statement->bind_param('ss',$permax,$permin);
 };
 $statement->execute();
 $statement->store_result();
 echo '
<script>$(document).ready(function(){$("#data-output table tr:even").css(\'background\',\'white\');});</script>
<table>
 <tr>
 <th>Id</th>
 <th>Name</th>
 <th>Roll No.</th>
 <th>Course</th>
 <th>Percentile</th>
 </tr>
 ';
 if($statement->num_rows() == 0) // so if we have 0 records acc. to keyword display no records found
 {
 echo '<div id="item">Ah snap...! No results found :/</div>';
 $statement->close();
 $conn->close();

 }
 else {
 $statement->bind_result($id,$fname,$fcourse,$frollno,$fpercent);
 while ($statement->fetch()) //outputs the records
 {
 echo "<tr>
<td>$id</td>
<td>$fname</td>
<td>$fcourse</td>
<td>$frollno</td>
<td>$fpercent</td>
</tr>";
 };
 $statement->close();
 $conn->close();
 };
 echo '</table>';
 };
?>

So now we are ready with all the pages now it requires ajax. so here is my script.


<script>
$(document).ready(function () {
$("#fetch-table tr td:odd").css('border-right','1px solid lightgrey')
$("#data-output table tr:even").css('background','white');
$("#fetch-by-rollno").on('keyup',function () {
var key = $(this).val();

$.ajax({
url:'fetch.php',
type:'GET',
data:'rollno='+key,
beforeSend:function () {

},
success:function (data) {
$("#data-output").html(data);

}
});
});
$("#fetch-by-percentile").on('change',function () {

var val = $(this).val();
$.ajax({
url:'fetch.php',
type:'POST',
data:'percentile='+val,
success:function(data){
$("#data-output").html(data);
}
});
});
$("#fetch-by-course").on('change',function () {

var val = $(this).val();
$.ajax({
url:'fetch.php',
type:'POST',
data:'course='+val,
success:function(data){
$("#data-output").html(data);
}
});
});
});
</script>

So now what we did here is, for filtering data through ‘Course’ ,’roll number’ and ‘percentile’ we attach an event handler ‘onchange’,’keyup’, ‘onchange’ and send the value to fetch.php to get the data from the database and output the result into #output-data.

You can download a sample project here.

Watch a video tutorial here.

Advertisements