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:
- Create mysql database connection
- Get records from mysql database
- Display all those records in html table
- When click on any column heading then we use jquery ajax function and sort that column accordingly.
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