How to Sorting MySQL Column using PHP

Sorting-Column-using-PHP-and-MySQL

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 ( Sorting MySQL Column using PHP ). 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);

<?php 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>

Full Code

<?php
$conn = mysqli_connect("localhost", "root", "", "blog_eg");

$orderBy = "post_at";
$order = "desc";

if(!empty($_GET["orderby"])) {
$orderBy = $_GET["orderby"];
}
if(!empty($_GET["order"])) {
$order = $_GET["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";
}

$sql = "SELECT * from posts ORDER BY " . $orderBy . " " . $order;
$result = mysqli_query($conn,$sql);
?>
<html>
<head>
<title>Sorting Column using PHP and MySQL</title>
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<style>
.table-content {
border-top:#0000CC 4px solid;
width:50%;
}
.table-content th {
padding:5px 20px;
background: #F0F0F0;
vertical-align:top;
}
.table-content td {
padding:5px 20px;
border-bottom:#0000CC 1px solid;
vertical-align:top;
}
.column-title {
text-decoration:none;
color:#09f;
}
</style>
</head>
<body>
<div class="demo-content">
<h2 class="title_with_link">Sorting Column using PHP and MySQL</h2>
<form name="frmSearch" method="post" action="">
<?php if(!empty($result)) { ?>
<table class="table-content">
<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>
<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 } ?>
</form>
</div>
</body>
</html>

Also Read :

PHP Database Backup Client for MySQL

How to Backup MySQL Database using PHP

Update/Delete Multiple Rows using PHP

Ajax Pagination with Tabular Records using PHP and jQuery

Sendmail in PHP using mail(), SMTP with Phpmailer

PHP session time set unset and check existence

Dynamically load content in Bootstrap Modal with AJAX

How Can I Generate a Random Alphanumeric String in PHP?

Please follow and like us:

By Rodney

I’m Rodney D Clary, a web developer. If you want to start a project and do a quick launch,I am available for freelance work.

Leave a Reply

Your email address will not be published. Required fields are marked *