If you want to learn how make html table column sort in ascending order or descending order without page refresh by using Ajax with jquery as front end and as a back end by using PHP and Mysql database. Ajax Column sorting is a aspect of something where user can make sort the html table results into either in ascending or descending order. You are work with large report and many column then at that time you have many time for finding particular data but by using this feature it will be quite simple and you can easily figure out data from large amount of data. In this feature when user click on column name of table then it will be load data according to that column in descending or ascending order. When user click on table column name at that time ajax request will generate and it will send request to server for fetching data according to order of that particular column and server send back html data to the ajax request and it will show on web page without page refresh event. This way Ajax column sort feature work with JQuery PHP and Mysql.
Source Code
Database
--
-- Table structure for table `tbl_employee`
--
CREATE TABLE IF NOT EXISTS `tbl_employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`gender` varchar(10) NOT NULL,
`designation` varchar(100) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;
--
-- Dumping data for table `tbl_employee`
--
INSERT INTO `tbl_employee` (`id`, `name`, `gender`, `designation`, `age`) VALUES
(1, 'Bruce Tom', 'Male', 'Driver', 36),
(5, 'Clara Gilliam', 'Female', 'Programmer', 24),
(6, 'Barbra K. Hurley', 'Female', 'Service technician', 26),
(7, 'Antonio J. Forbes', 'Male', 'Faller', 32),
(8, 'Charles D. Horst', 'Male', 'Financial investigator', 29),
(9, 'Beau L. Clayton', 'Male', 'Extractive metallurgical engin', 33),
(10, 'Ramona W. Burns', 'Female', 'Electronic typesetting machine operator', 27),
(11, 'Jennifer A. Morrison', 'Female', 'Rigging chaser', 29),
(12, 'Susan M. Juarez', 'Female', 'Control and valve installer', 25),
(13, 'Ellan D. Downie', 'Female', 'Education and training manager', 26),
(14, 'Larry T. Williamson', 'Male', 'Teaching assistant', 30),
(15, 'Lauren M. Reynolds', 'Female', 'Internet developer', 22),
(16, 'Joseph L. Judge', 'Male', 'Refrigeration mechanic', 35),
(17, 'Eric C. Lavelle', 'Male', 'Model', 21),
(18, 'Cheryl T. Smithers', 'Female', 'Personal banker', 23),
(19, 'Tonia J. Diaz', 'Female', 'Facilitator', 29),
(20, 'Stephanie P. Lederman', 'Female', 'Mental health aide', 27),
(21, 'Edward F. Sanchez', 'Male', 'Marine oiler', 28);
index.php
<?php
//index.php
$connect = mysqli_connect('localhost', 'root', '', 'testing');
$query = "SELECT * FROM tbl_employee ORDER BY id DESC";
$result = mysqli_query($connect, $query);
?>
<!DOCTYPE html>
<html>
<head>
<title>Webslesson Tutorial | Ajax Jquery Column Sort with PHP & MySql</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
<br />
<div class="container" style="width:700px;" align="center">
<h3 class="text-center">Ajax Jquery Column Sort with PHP & MySql</h3><br />
<div class="table-responsive" id="employee_table">
<table class="table table-bordered">
<tr>
<th><a class="column_sort" id="id" data-order="desc" href="#">ID</a></th>
<th><a class="column_sort" id="name" data-order="desc" href="#">Name</a></th>
<th><a class="column_sort" id="gender" data-order="desc" href="#">Gender</a></th>
<th><a class="column_sort" id="designation" data-order="desc" href="#">Designation</a></th>
<th><a class="column_sort" id="age" data-order="desc" href="#">Age</a></th>
</tr>
<?php
while($row = mysqli_fetch_array($result))
{
?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["gender"]; ?></td>
<td><?php echo $row["designation"]; ?></td>
<td><?php echo $row["age"]; ?></td>
</tr>
<?php
}
?>
</table>
</div>
</div>
<br />
</body>
</html>
<script>
$(document).ready(function(){
$(document).on('click', '.column_sort', function(){
var column_name = $(this).attr("id");
var order = $(this).data("order");
var arrow = '';
//glyphicon glyphicon-arrow-up
//glyphicon glyphicon-arrow-down
if(order == 'desc')
{
arrow = ' <span class="glyphicon glyphicon-arrow-down"></span>';
}
else
{
arrow = ' <span class="glyphicon glyphicon-arrow-up"></span>';
}
$.ajax({
url:"sort.php",
method:"POST",
data:{column_name:column_name, order:order},
success:function(data)
{
$('#employee_table').html(data);
$('#'+column_name+'').append(arrow);
}
})
});
});
</script>
sort.php
<?php
//sort.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$output = '';
$order = $_POST["order"];
if($order == 'desc')
{
$order = 'asc';
}
else
{
$order = 'desc';
}
$query = "SELECT * FROM tbl_employee ORDER BY ".$_POST["column_name"]." ".$_POST["order"]."";
$result = mysqli_query($connect, $query);
$output .= '
<table class="table table-bordered">
<tr>
<th><a class="column_sort" id="id" data-order="'.$order.'" href="#">ID</a></th>
<th><a class="column_sort" id="name" data-order="'.$order.'" href="#">Name</a></th>
<th><a class="column_sort" id="gender" data-order="'.$order.'" href="#">Gender</a></th>
<th><a class="column_sort" id="designation" data-order="'.$order.'" href="#">Designation</a></th>
<th><a class="column_sort" id="age" data-order="'.$order.'" href="#">Age</a></th>
</tr>
';
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td>' . $row["id"] . '</td>
<td>' . $row["name"] . '</td>
<td>' . $row["gender"] . '</td>
<td>' . $row["designation"] . '</td>
<td>' . $row["age"] . '</td>
</tr>
';
}
$output .= '</table>';
echo $output;
?>
Hello, I get this error when I use your code. can you help me, please?
ReplyDeleteWarning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\wamp64\www\Super\test.php on line 32
thanks, it was really useful
ReplyDeleteThis is almost perfect, I would just like to be able to modify it so that it only shows 100 records at a time and if I click on a link it will show the next 100 etc. Each 100 would be able to be sorted ASC/DESC as in your example.
ReplyDeleteI get an error while sorting numbers. Example #1 5.65 #2 5.59 #3 55.81 #4 5.39 Apparently it does not make a difference between 1 digit (#1, #2, #4) and 2 digits (#3 witch is wrong). How to fix this? Thank you
ReplyDelete