Sorting MySQL Column using PHP

In PHP we can sort database results based on a column field using MySQL orderby clause. Previously, we have seen about how to change the MySQL row order using jQuery.

Now, we are going to see an example to sort UI results by clicking on the column name in header. We have a posts table containing sortable post_title, description and post_at columns.


Creating Orderby Query

By default the posts table is sorted by the date column post_at in descending order. If any orderby column is specified in query string then it will override the default values. This PHP script is declaring orderby column name for select query.

$conn = mysqli_connect("localhost", "root", "", "blog_samples");
$orderBy = "post_at";
$order = "asc";

if(!empty($_GET["orderby"])) {
	$orderBy = $_GET["orderby"];
if(!empty($_GET["order"])) {
	$order = $_GET["order"];

$sql = "SELECT * from posts ORDER BY " . $orderBy . " " . $order;
$result = mysqli_query($conn,$sql);

<?php if(!empty($result))	 { ?>
<table class="table-content">
		while($row = mysqli_fetch_array($result)) {
			<td><?php echo $row["post_title"]; ?></td>
			<td><?php echo $row["description"]; ?></td>
			<td><?php echo $row["post_at"]; ?></td>

<?php } ?>

Sortable Header Links

The table header titles are links to send the orderby and order values via query string. This script is for computing next-order value based on the current order.

	$postTitleNextOrder = "asc";
	$descriptionNextOrder = "asc";
	$postAtNextOrder = "desc";

	if($orderBy == "post_title" and $order == "asc") {
		$postTitleNextOrder = "desc";
	if($orderBy == "description" and $order == "asc") {
		$descriptionNextOrder = "desc";
	if($orderBy == "post_at" and $order == "desc") {
		$postAtNextOrder = "asc";
	<th width="30%"><span><a href="?orderby=post_title&order=<?php echo $postTitleNextOrder; ?>" class="column-title">Post Title</a></span></th>
	<th width="50%"><span><a href="?orderby=description&order=<?php echo $descriptionNextOrder; ?>" class="column-title">Description</a></span></th>          
	<th width="20%"><span><a href="?orderby=post_at&order=<?php echo $postAtNextOrder; ?>" class="column-title">Post Date</a></span></th>	  


This PHP code tutorial was published on March 9, 2016.

↑ Back to Top