To implement sorting patterns using AJAX.
Prerequisite: Basic Understanding HTML, CSS, JavaScript, jQuery, PHP and MySQL.
In this project:
- We will create a table displaying student’s information i.e. Name, Percentage and Date.
- All the table heads are created as hyperlinks which is clicked to sort the data column wise.
- When user clicks on Name, data is sorted name-wise in ascending order and when is clicked again, data is sorted name-wise in descending order.
- Ajax is used to sort and display the sorted data accordingly.
index.php ⬇
<!DOCTYPE html>
<html>
<head>
<title>Sorting</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
</head>
<body>
<?php
include_once 'includes/conn.php';
$sql = "SELECT * FROM student_details";
$result = mysqli_query($con, $sql);
echo '<table class="table" id="student_details">
<tr>
<th><a href="javascript:void(0)" onclick="sorting(1, this)" sort_value="1">S No.</a></th>
<th><a href="javascript:void(0)" onclick="sorting(2, this)" sort_value="1">Name</a></th>
<th><a href="javascript:void(0)" onclick="sorting(3, this)" sort_value="1">Percentage</a></th>
<th><a href="javascript:void(0)" onclick="sorting(4, this)" sort_value="1">Date</a></th>
<tr>';
while($row = mysqli_fetch_assoc($result)){
echo '<tr>';
echo '<td>'.$row['id'].'</td>';
echo '<td>'.$row['name'].'</td>';
echo '<td>'.$row['percentage'].'</td>';
echo '<td>'.$row['date'].'</td>';
echo '</tr>';
}
mysqli_close($con);
echo '</table>';
?>
</body>
<script>
function sorting(col_no, el){
var num = col_no;
var order = $(el).attr("sort_value");
$.ajax({
url: 'process.php',
type: 'POST',
data: {column:num, order:order},
success: function(data) {
var html = "";
var result = JSON.parse(data);
if(result.length > 0){
$("#student_details").find("tr:not(:first)").remove();
for(var i = 0; i < result.length; i++){
html += '<tr>';
html += '<td>'+result[i]['id']+'</td>';
html += '<td>'+result[i]['name']+'</td>';
html += '<td>'+result[i]['percentage']+'</td>';
html += '<td>'+result[i]['date']+'</td>';
html += '</tr>';
}
}
$("#student_details").append(html);
if(order == 1){
order = 2;
}else{
order = 1;
}
$(el).attr("sort_value", order);
}
});
}
</script>
</html>
Start the localhost services and run index.php in your web browser to get the following output.
Explanation of Index.php
- Connection file
conn.phpis included using PHP file include method. It connects to the “db” database.
include "includes/conn.php";- Below we have mentioned the code for
Conn.phpfile:
<?php
$con = new mysqli("localhost","root","","mydb");
if ($con->connect_errno) {
echo "Failed to connect to MySQL: ".$con->connect_error;
exit();
}
?>
If there is any error in connecting to the database, error message is displayed on the screen and exit method is called to stop further execution of the php code. Otherwise, connection is successfully built and further execution is proceeded.
- Records are fetched from “student_details” table using SQL statement and is executed as follows.
$sql = "SELECT * FROM student_details";
$result = mysqli_query($con, $sql);
- Table is displayed with table heads described as follows. Javascript function is also called onclick event i.e.
sorting().
<table class="table" id="student_details">
<tr>
<th><a href="javascript:void(0)" onclick="sorting(1, this)" sort_value="1">S No.</a></th>
<th><a href="javascript:void(0)" onclick="sorting(2, this)" sort_value="1">Name</a></th>
<th><a href="javascript:void(0)" onclick="sorting(3, this)" sort_value="1">Percentage</a></th>
<th><a href="javascript:void(0)" onclick="sorting(4, this)" sort_value="1">Date</a></th>
<tr>
- To use jQuery Functions jQuery file is included in the head tag. You can use either an online link or download it from the
jQuery.comand can include in the page accordingly.
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>- All the jQuery functions are put inside the document ready function to make sure that all the events are executed after the DOM gets ready for it.
$(document).ready(function(){
…
});
- Sorting is performed using Ajax which is defined inside the sorting method. Two parameters are passed where
col_no carries the column number and
object is passed in el parameter.
function sorting(col_no, el){
var num = col_no;
var order = $(el).attr("sort_value");
}
- Query Ajax method defines the following parameters.
|
url: |
url parameter specifies the url you want to request. |
|
type: |
HTTP GET and POST are used to request the data from the server. |
|
data: |
It specifies the data to send with the request. |
|
success: |
Success is invoked upon successful completion of Ajax request. |
|
error: |
Error is invoked upon the failure of Ajax request. |
$.ajax({
url: 'process.php',
type: 'POST',
data: {column:num, order:order},
success: function(data) {
var html = "";
var result = JSON.parse(data);
if(result.length > 0){
$("#student_details").find("tr:not(:first)").remove();
for(var i = 0; i < result.length; i++){
html += '<tr>';
html += '<td>'+result[i]['id']+'</td>';
html += '<td>'+result[i]['name']+'</td>';
html += '<td>'+result[i]['percentage']+'</td>';
html += '<td>'+result[i]['date']+'</td>';
html += '</tr>';
}
}
$("#student_details").append(html);
if(order == 1){
order = 2;
}else{
order = 1;
}
$(el).attr("sort_value", order);
}
});
- Column number and Order value of sorting is sent to
process.phpusing HTTP POST method. - On successful fetching of sorted list, the result is decoded using
JSON.parsemethod and is appended to table with ID “student_details”. - If order is 1 it is altered to 2 otherwise it is set to 1 where 1 = Ascending and 2 = Descending.
- The sort order value is then assigned to the corresponding sort_value attribute.
Process.php⬇
<?php
include_once 'includes/conn.php';
$arr = array(1 => "id", 2 => "name", 3 => "percentage", 4 => "date");
$orderArr = array(1 => "ASC", 2 => "DESC");
$column = trim($_POST['column']);
$order = trim($_POST['order']);
if($column != "" && is_numeric($order)){
$sql = "SELECT * FROM student_details ORDER BY ".$arr[$column]." ".$orderArr[$order];
$exe = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($exe);
if($num_rows > 0){
while($row = mysqli_fetch_assoc($exe)){
$arry['id'] = $row['id'];
$arry['name'] = $row['name'];
$arry['percentage'] = $row['percentage'];
$arry['date'] = $row['date'];
$result[] = $arry;
}
}else{
$result = array("");
}
echo json_encode($result);
}
?>
Explanation of process.php
- Connection file
conn.phpis included using PHP file include method. It connects to the “db” database.
include "includes/conn.php";- Below we have mentioned the code for
Conn.phpfile:
<?php
$con = new mysqli("localhost","root","","mydb");
if ($con->connect_errno) {
echo "Failed to connect to MySQL: ".$con->connect_error;
exit();
}
?>
If there is any error in connecting to the database, error message is displayed on the screen and exit method is called to stop further execution of the php code. Otherwise, connection is successfully built and further execution is proceeded.
- We have used numeric values for column names and sorting order which are further related to mysql table. For example: 1 refers to id column, 2 refers to name column etc…
$arr = array(1 => "id", 2 => "name", 3 => "percentage", 4 => "date");
$orderArr = array(1 => "ASC", 2 => "DESC");- If column and sort_order exists, only then the control is passed to the block.
if($column != "" && is_numeric($order)){
} - A table named as “student_details” is created in the MySQL Database mydb to which we are connected. Listed of students is also inserted according to the requirement. For example: the table we have created is as shown below :
- Student Details are fetched using mysql SELECT STATEMENT. If number of rows is greater than 0, then the resultant array is iterated using while loop and the result is assigned to
result[] array.
$sql = "SELECT * FROM student_details ORDER BY ".$arr[$column]." ".$orderArr[$order];
$exe = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($exe);
if($num_rows > 0){
while($row = mysqli_fetch_assoc($exe)){
$arry['id'] = $row['id'];
$arry['name'] = $row['name'];
$arry['percentage'] = $row['percentage'];
$arry['date'] = $row['date'];
$result[] = $arry;
}
}
- Result is encoded using
json_encodemethod as follows.
echo json_encode($result); Summary
In this project, we have learned ajax based Search in HTML textbox. We have applied the search on keyup event. The data is fetched from the database. We have fetched the list of countries using jQuery Ajax Method. Similarly, you can use the same concept or process to apply search on your website by manipulating the things your way.
Always remember to include jQuery file in the head tag of your document, before you are going to use anykind of jQuery method or plugin.
