Sort MySQL tables like a pro with this step-by-step guide. Learn how to sort a MySQL table by a specific column using SQL queries and various sorting techniques. Improve the functionality of your web applications by incorporating efficient table sorting features that enhance user experience.
Web development and content management is essentially the alpha and the omega. Manipulating data can be a rather daunting task and requires a good grasp of overall app flow in terms of how data changes when X things happen during its use. In this post we’re going to learn how to create a container that shows all those popular blog posts that we have in our app, sorted in ascending or descending fashion.
So where do we start? The simplest answer to that it’s the famous phrase “divide and conquer” which is the technique that essentially breaks down the concept into small understandable and well defined chunks. The first problem that we’re called to solve it’s the data creation. We’re going to assume that we already have data to work with. However, we still need to run the necessary queries to create the table structure of our posts and then use Lorem Ipsum generator to insert sample posts.
Create the Table Structure and Insert Sample Data
Let’s create a table named Posts and add our primary key and post’s structural definition. In this example I’m using phpmyadmin, creating a database artisan with encoding type of utf8_general_ci and then the table posts by running the SQL query below:
CREATE TABLE `artisan`.`posts` (`id` INT NOT NULL AUTO_INCREMENT , `title` TEXT NOT NULL , `body` TEXT NOT NULL , `property` INT NOT NULL COMMENT '(Likes/Comments/Views)' , PRIMARY KEY (`id`)) ENGINE = InnoDB;
As you can see there’s a field that serves the purpose of our designator property which can be either the number of likes and comments or the number of views. We’re going to use this field to create our sorting. Before we continue, we need to insert some text alongside the posts that we’re going to create. Fire up the lorem ipsum text generator and grab some text.
INSERT INTO `posts` (`id`, `title`, `body`, `property`) VALUES (NULL, 'Section 1.10.32 of \"de Finibus Bonorum et Malorum\", written by Cicero in 45 BC', 'Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt. Neque porro quisquam est, qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit, sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaerat voluptatem. Ut enim ad minima veniam, quis nostrum exercitationem ullam corporis suscipit laboriosam, nisi ut aliquid ex ea commodi consequatur? Quis autem vel eum iure reprehenderit qui in ea voluptate velit esse quam nihil molestiae consequatur, vel illum qui dolorem eum fugiat quo voluptas nulla pariatur?', '32'), (NULL, 'Neque porro quisquam est qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit..', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nunc feugiat elit non eleifend cursus. Suspendisse sit amet tempus lectus. Nullam volutpat arcu ac ipsum consectetur feugiat. Quisque nec nibh velit. Maecenas tortor libero, hendrerit at dapibus et, rutrum et leo. In hac habitasse platea dictumst. Nunc nisi velit, tincidunt et purus sit amet, molestie vulputate libero. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Integer blandit mi nisl, tincidunt molestie leo iaculis a. Phasellus maximus eget lacus sed convallis. Aliquam lacinia nisl et lacus fringilla, nec condimentum dolor luctus. Nullam nec augue at ante venenatis commodo. Maecenas pretium elementum tortor, id commodo tellus sagittis eu. Curabitur id accumsan enim, rutrum luctus erat. Nunc malesuada magna bibendum, ultricies lectus in, auctor nisl. Etiam nec enim ac magna molestie pharetra. Curabitur blandit fringilla iaculis. Curabitur vitae tellus enim. Donec in volutpat leo, iaculis tristique quam.', '221'), (NULL, 'Ut mi quam, interdum eu diam nec, consequat pretium eros. Praesent a elit ante.', 'Ut mi quam, interdum eu diam nec, consequat pretium eros. Praesent a elit ante. Mauris venenatis, tortor vitae cursus laoreet, metus eros imperdiet nisl, nec scelerisque elit augue in leo. Orci varius natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Praesent sed nisi augue. Vestibulum condimentum, lectus ac facilisis vehicula, dui ex porttitor dolor, quis pretium mauris ipsum non tellus. Maecenas sed commodo dolor, ac accumsan erat. Aliquam eleifend blandit arcu, a dictum massa porttitor id. Praesent a massa laoreet, finibus sapien sit amet, pharetra felis. Cras in orci gravida, faucibus purus ac, tempus sapien.', '54'), (NULL, 'Donec vulputate eu ipsum in rhoncus. Phasellus a volutpat mauris, eget rhoncus velit.', 'Donec vulputate eu ipsum in rhoncus. Phasellus a volutpat mauris, eget rhoncus velit. Proin ornare, quam ut tempus tempor, arcu odio pulvinar felis, ut imperdiet sapien lectus quis lorem. Proin placerat hendrerit elit, vel pellentesque dui tempus quis. Nullam ornare purus non risus maximus, ut tempor magna venenatis. Praesent quis interdum augue, vitae auctor erat. Donec sed congue mauris. Aenean in turpis sem. Vestibulum vitae dolor tristique, auctor dolor a, pellentesque mauris. Vivamus rutrum malesuada metus.', '105')
Create the main UI of the table using Bootstrap 5
The next order of business is to create a simple user interface to showcase the sorting actions. We’re going to bring up the Bootstrap 5 content delivery network and include it into our PHP file alongside the database connector which I’m going to assume that you already have your own.
<?php include 'config.php'; ?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Sort Posts By Views</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script
</head>
Implement the main logic and the filter handler
The idea here is simple. We use the URL to pass the filters and construct the query appropriately so we get the data fetched from the database already sorted so we can display it without modifying the array further. Since we construct a query that’s going to be executed to the database and we don’t manually sort the array, we absolutely need to sanitize our values -escape them essentially- to avoid MySQL Injections that can literally cripple our database structure.
Once we have collected the field and mode from the URL, it’s time to construct the query. The field variable represents the table column that we want to sort the data by and the mode variable represents the sort method, meaning we want the data to be in descending or ascending order. The query is now ready for execution. We format the fetched data using a foreach template structure so we can easily display the table columns or the components of each blog post. Each post may have an id, a title, a body and a property field. The id is of type integer (and the primary key) as the property and the title, body are both of type TEXT.
<?php
if (isset($_GET['field']) & isset($_GET['mode'])){
$field = mysqli_real_escape_string($con, $_GET['field']);
$mode = mysqli_real_escape_string($con, $_GET['mode']);
$query = 'select * from posts order by ' . $field . ' ' . $mode;
} else {
$query = 'select * from posts';
}
$result = mysqli_query($con, $query);
if ($result->num_rows>0):
$posts = array();
while($row = mysqli_fetch_assoc($result)): ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['title'] ?></td>
<td style="font-size: 14px;"><?= $row['body'] ?></td>
<td><?= $row['property'] ?></td>
</tr>
<?php
endwhile;
endif;
?>
Create the filter handler using a BS5 dropdown and finish UI
Now that the functionality is set, it’s time to add the filter handler that’s going to change the URL depending the operation that we choose. Bootstrap 5 provides an exceptional structure for such things, called dropdowns. You can read more here. Notice how you can use the href of the anchor tags to pass those two variables of interest via the URL. The formula is rather simple.
- You take the path to the file and you add the question mark next to it e.g index.php?
- Then you can pass your own variables using the key value pair notation e.g:
index.php?name=Karl - To add more variables to the URL you just use the & between those key value pairs e.g:
index.php?name=Karl&lastname=G
<div class="dropdown">
<button class="btn btn-dark dropdown-toggle" type="button"
id="dropdownMenuButton1" data-bs-toggle="dropdown" aria-expanded="false">Sort By Views
</button>
<ul class="dropdown-menu" aria-labelledby="dropdownMenuButton1">
<li>
<a class="dropdown-item" href="./index.php?field=property&mode=desc">
Views Descending
</a>
</li>
<li><a class="dropdown-item" href="./index.php?field=property&mode=asc">
Views Ascending</a>
</li>
</ul>
</div>
Merge all together and preview your results
Since you have the filter handler and the basic functionality, you can add some table headings to the UI and your sorting table page is complete. Use the code below to complete the example. You may change portions of it should you want to use another filter or what have you.
<?php include 'config.php'; ?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Sort Posts By Views</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"
</script>
</head>
<body>
<div class="container p-5">
<div class="dropdown">
<button class="btn btn-dark dropdown-toggle" type="button" id="dropdownMenuButton1"
data-bs-toggle="dropdown" aria-expanded="false">Sort By Views</button>
<ul class="dropdown-menu" aria-labelledby="dropdownMenuButton1">
<li>
<a class="dropdown-item" href="./index.php?field=property&mode=desc">
Views Descending
</a>
</li>
<li>
<a class="dropdown-item" href="./index.php?field=property&mode=asc">
Views Ascending
</a>
</li>
</ul>
</div>
<hr>
<table class="table table-bordered">
<tr>
<th>ID</th>
<th>Title</th>
<th>Content</th>
<th>Views</th>
</tr>
<?php
if (isset($_GET['field']) & isset($_GET['mode'])){
$field = mysqli_real_escape_string($con, $_GET['field']);
$mode = mysqli_real_escape_string($con, $_GET['mode']);
$query = 'select * from posts order by ' . $field . ' ' . $mode;
} else {
$query = 'select * from posts';
}
$result = mysqli_query($con, $query);
if ($result->num_rows>0):
$posts = array();
while($row = mysqli_fetch_assoc($result)): ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['title'] ?></td>
<td style="font-size: 14px;"><?= $row['body'] ?></td>
<td><?= $row['property'] ?></td>
</tr>
<?php
endwhile;
endif;
?>
</table>
</div>
</body>
</html>
