Site icon WDB24

How to sort html table columns using PHP jQuery and Ajax

Ahsan Zameer
php featured image

Whenever we use html table to show data in columns we often need some type of data sorting feature. Because sorting save lot of time to find desirable information. Sorting can be either in ascending order or in descending order. In this tutorial I am going to show you how to sort HTML table columns using PHP jQuery and Ajax.

Also read: How to Upload CSV and Save in Mysql database using PHP

Steps involved in PHP Sort html table columns:


Setting Database: (config.php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
<?php
$host = "localhost";
$userName = "root";
$password = "";
$dbName = "my_cart";
 
// Create database connection
$conn = mysqli_connect($host, $userName, $password, $dbName);
// Check connection
if(!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
 
?>
 

Fetch records and display in html table columns: (index.php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
$sql = "select id, user_name, user_email, user_country, created from users";
$rs = mysqli_query($conn,$sql);
 
<table class="table">
<tr>
<th class="sort-heading" id="name-asc" >User Name</th>
<th class="sort-heading" id="email-asc" >User Email</th>
<th class="sort-heading" id="user-asc" >User Country</th>
<th class="sort-heading" id="created-asc" >Created</th>
</tr>
<?php
while($rows = mysqli_fetch_assoc($rs))
{
echo "<tr>";
echo "<td>".$rows['user_name']."</td>";
echo "<td>".$rows['user_email']."</td>";
echo "<td>".$rows['user_country']."</td>";
echo "<td>".$rows['created']."</td>";
echo "</tr>";
}
?>
</table>
 

In the above php and html code I have fetch records from users table and display all the records in respective column in html table. I used first row of table as column heading and all the heading have same class but different id with same order (asc). I will use table heading id for ordering purpose.

jQuery Ajax function for sorting table columns:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
<script>
$(document).ready(function(){
$(".sort-heading").click(function(){
//get data-nex-order value
var getSortHeading = $(this);
var getNextSortOrder = getSortHeading.attr('id');
var splitID = getNextSortOrder.split('-');
var splitIDName = splitID[0];
var splitOrder = splitID[1];
//get current td value
var getColumnName = getSortHeading.text();
$.ajax({
url:'get_order_data.php',
type:'post',
data:{'column':getColumnName,'sortOrder':splitOrder},
success: function(response){
if(splitOrder == 'asc')
{
getSortHeading.attr('id',splitIDName+'-desc');
}
else
{
getSortHeading.attr('id',splitIDName+'-asc');
}
$(".table tr:not(:first)").remove();
$(".table").append(response);
}
});
});
});
</script>
 

When I click on any column heading I will get its id in getNextSortOrder variable and I split that id into two variables. splitIDName has just heading name which i am using just for placeholder. splitOrder holds the current order type. getColumnName holds the column value. $.ajax is a jquery ajax function which will send and receive data from get_order_data.php and remove the previous table records and put new table records.

PHP file for Sorting:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
<?php
require_once('config.php');
if(isset($_POST['column']) && isset($_POST['sortOrder']))
{
$columnName = str_replace(" ","_",strtolower($_POST['column']));
$sortOrder  = $_POST['sortOrder'];
$sql = "select id, user_name, user_email, user_country, created from users order by ".$columnName." ".$sortOrder;
//echo $sql;
$rs = mysqli_query($conn,$sql);
while($rows = mysqli_fetch_assoc($rs))
{
echo "<tr>";
echo "<td>".$rows['user_name']."</td>";
echo "<td>".$rows['user_email']."</td>";
echo "<td>".$rows['user_country']."</td>";
echo "<td>".$rows['created']."</td>";
echo "</tr>";
}
}
?>
 

This file gets post data. $columnName is using 2 functions first is strtolower and second is str_replace. Strtolower function converts $_POST['column'] value into lower and and str_replace function replace the blank space with underscore.

Also read:How to Create Simple Contact Form using Ajax, jQuery, Php and Mysql

Exit mobile version