Sorting MySQL Column using PHP
by Vincy. Last modified on July 10th, 2022.
In PHP we can sort database results based on a column field using MySQL order by 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 the header. We have a posts table containing sortable post_title, description, and post_at columns.
Creating Order by Query
By default, the posts table is sorted by the date column post_at in descending order. If any order by column is specified in query string then it will override the default values. This PHP script is declaring orderby column name for select query.
<?php $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);
if(!empty($result)) { ?>
<table class="table-content">
<tbody>
<?php
while($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row["post_title"]; ?></td>
<td><?php echo $row["description"]; ?></td>
<td><?php echo $row["post_at"]; ?></td>
</tr>
<?php
}
?>
<tbody>
</table>
<?php } ?>
Sortable Header Links
The table header titles are links to send the order by and order values via the query string. This script is for computing next order value based on the current order.
<?php
$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";
}
?>
<thead>
<tr>
<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>
</tr>
</thead>
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.