For this tutorial, we will sort table columns with HTML, PHP, and MySQL. Our table will contain records from our MySQL database. The HTML table headers will be clickable so the user can toggle if they want to either sort by ascending or descending (lowest or highest).
Why would you want to do this, you may ask? It's just one of those features that will allow your users to interact with your tables. Perhaps you have a table with multiple pages that contain hundreds or thousands of records, and the user might want to sort by a specific column, such as a column with the name Date or a column with the name Age.
Creating the MySQL Database
A database is required to populate records in our HTML table, so we'll cover that in this section. You can either use an existing database or run the SQL statement below either with phpMyAdmin or your preferred database management tool.
CREATE DATABASE IF NOT EXISTS `tablesort` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `tablesort`;
CREATE TABLE IF NOT EXISTS `students` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` tinyint(100) NOT NULL,
`joined` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;
INSERT INTO `students` (`id`, `name`, `age`, `joined`) VALUES
(1, 'David Deacon', 23, '17/08/2023'),
(2, 'Sheri Warner', 19, '03/05/2023'),
(3, 'Sean Glover', 24, '24/07/2023'),
(4, 'John West', 17, '13/08/2023'),
(5, 'Rufus Clarke', 20, '28/07/2023'),
(6, 'Roosevelt Myers', 20, '25/07/2023'),
(7, 'Elvira Andrews', 22, '02/07/2023'),
(8, 'Richard Cook', 26, '19/07/2023'),
(9, 'Lorenzo Harris', 23, '01/07/2023'),
(10, 'Eduardo Hoffman', 17, '03/07/2023'),
(11, 'Jeanne Fisher', 20, '13/08/2023'),
(12, 'Tracy Bowers', 30, '07/07/2023'),
(13, 'Heidi Lawrence', 18, '04/06/2023'),
(14, 'Tara Holland', 25, '01/07/2023'),
(15, 'Grant Edwards', 22, '22/06/2023'),
(16, 'Bradford Green', 29, '02/05/2023'),
(17, 'Gwen Schultz', 20, '02/05/2023'),
(18, 'Hope Dawson', 28, '21/08/2023'),
(19, 'Florence Osborne', 19, '17/05/2023'),
(20, 'Rickey Poole', 26, '28/06/2023'),
(21, 'Casey Sutton', 28, '06/07/2023'),
(22, 'Willie Lowe', 23, '11/05/2023'),
(23, 'Stephen Schultz', 28, '15/07/2023'),
(24, 'Eileen Lynch', 18, '12/06/2023'),
(25, 'Aaron Ruiz', 29, '02/05/2023'),
(26, 'Mae Murray', 30, '24/06/2023'),
(27, 'Regina Hanson', 21, '26/07/2023'),
(28, 'Cameron Mclaughlin', 20, '29/07/2023'),
(29, 'Earl Hale', 17, '30/06/2023'),
(30, 'Marta Blair', 24, '10/06/2023'),
(31, 'Alberta Silva', 22, '05/06/2023'),
(32, 'Joanna Holmes', 20, '20/05/2023'),
(33, 'Alex Brock', 30, '12/05/2023'),
(34, 'Colin Wright', 19, '28/05/2023'),
(35, 'Peter Schmidt', 25, '10/07/2023'),
(36, 'Joshua Price', 27, '13/07/2023'),
(37, 'Elias Chandler', 22, '19/07/2023'),
(38, 'Stanley Ross', 21, '02/06/2023'),
(39, 'Vera Cole', 26, '02/05/2023'),
(40, 'Johnny Daniels', 29, '19/07/2023'),
(41, 'Yvonne Hopkins', 21, '16/07/2023');
ALTER TABLE `students` ADD PRIMARY KEY (`id`);
ALTER TABLE `students` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=42;The above SQL statement will create the database tablesort with the table students, which we'll be using for this tutorial. In addition, it's prepopulated with dummy data that we can utilize later on.
Sorting Tables with PHP and MySQL
Now we can go ahead and create our PHP file, create a new PHP file called tablesort.php.
Connect to our MySQL database:
$mysqli = mysqli_connect('localhost', 'root', '', 'tablesort');Let's define some variables that we will use later on:
$columns = array('name','age','joined');
$column = isset($_GET['column']) && in_array($_GET['column'], $columns) ? $_GET['column'] : $columns[0];
$sort_order = isset($_GET['order']) && strtolower($_GET['order']) == 'desc' ? 'DESC' : 'ASC';Below, I'll explain what each variable will be used for.
$columns - This will be the array of columns we will use for our table, which is basically a security check to prevent SQL injection. We want to ensure that the user doesn't try anything suspicious. Feel free to add/remove the columns in the array to reflect your MySQL table.
$column - This variable will determine which column we will sort by. If it appears in the URL, then it will appear in our script. We also check if the requested column name is in our $columns array. The default will be the first column in the $columns array if there is no GET request.
$sort_order - This variable will determine the sort order. Basically, we can either sort by ascending (ASC) or descending (DESC). If there is no GET request associated with this particular variable, it will default to ascending.
We can now retrieve the records from our database table.
if ($result = $mysqli->query('SELECT * FROM students ORDER BY ' . $column . ' ' . $sort_order)) {And create some more variables:
$up_or_down = str_replace(array('ASC','DESC'), array('up','down'), $sort_order);
$asc_or_desc = $sort_order == 'ASC' ? 'desc' : 'asc';
$add_class = ' class="highlight"';
What these variables will do:
$up_or_down - This variable will be used with font awesome, as we want those small up and down icons to appear next to table column names, so the variable basically determines which way the column is sorted and will display the appropriate icon to the user.
$asc_or_desc - This variable will be used to determine the toggle state for the active column, if it should be sorted ascending or descending, basically, if the user has already clicked a column name, we want to make sure that when they click the column name again, it will sort in the opposite order.
$add_class - This will help the user identify which column is highlighted by applying a CSS class to the HTML element.
Now we can display the records in their respective order.
<table>
<tr>
<th><a href="tablesort.php?column=name&order=<?php echo $asc_or_desc; ?>">Name<i class="fas fa-sort<?php echo $column == 'name' ? '-' . $up_or_down : ''; ?>"></i></a></th>
<th><a href="tablesort.php?column=age&order=<?php echo $asc_or_desc; ?>">Age<i class="fas fa-sort<?php echo $column == 'age' ? '-' . $up_or_down : ''; ?>"></i></a></th>
<th><a href="tablesort.php?column=joined&order=<?php echo $asc_or_desc; ?>">Join Date<i class="fas fa-sort<?php echo $column == 'joined' ? '-' . $up_or_down : ''; ?>"></i></a></th>
</tr>
<?php while ($row = $result->fetch_assoc()): ?>
<tr>
<td<?php echo $column == 'name' ? $add_class : ''; ?>><?php echo $row['name']; ?></td>
<td<?php echo $column == 'age' ? $add_class : ''; ?>><?php echo $row['age']; ?></td>
<td<?php echo $column == 'joined' ? $add_class : ''; ?>><?php echo $row['joined']; ?></td>
</tr>
<?php endwhile; ?>
</table>
As you can see we make use of the variables we have created, and we also check which table column is being sorted. The while loop will iterate our records from our table students and sort them accordingly.
If we want to implement code to escape special characters to prevent XSS attacks, etc., we can wrap the column value with the htmlspecialchars() function, like so:
<?php echo htmlspecialchars($row['name'], ENT_QUOTES); ?>Let's add some style to our HTML table. Add the code to your CSS file or in the head section of your document:
table {
border-collapse: collapse;
width: 500px;
}
th {
background-color: #54585d;
border: 1px solid #54585d;
}
th:hover {
background-color: #64686e;
}
th a {
display: block;
text-decoration:none;
padding: 10px;
color: #ffffff;
font-weight: bold;
font-size: 13px;
}
th a i {
margin-left: 5px;
color: rgba(255,255,255,0.4);
}
td {
padding: 10px;
color: #636363;
border: 1px solid #dddfe1;
}
tr {
background-color: #ffffff;
}
tr .highlight {
background-color: #f9fafb;
}
We also require the Font Awesome library for the icons, so let's go ahead and add the Font Awesome CDN code in the head section of the document:
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.3.1/css/all.css" integrity="sha384-mzrmE5qonljUremFsqc01SB46JvROS7bZs3IO2EmfFsd15uHvIt+Y8vEf7N7fWAU" crossorigin="anonymous">Our table should now resemble the following:
Full Source Code
If somehow it didn't go according to plan, you can use the full source code below.
<?php
// Below is optional. Remove if you have already connected to your database.
$mysqli = mysqli_connect('localhost', 'root', '', 'tablesort');
// For extra protection these are the columns that the user can sort by (in your database table).
$columns = array('name','age','joined');
// Only get the column if it exists in the above columns array, if it doesn't exist the database table will be sorted by the first item in the columns array.
$column = isset($_GET['column']) && in_array($_GET['column'], $columns) ? $_GET['column'] : $columns[0];
// Get the sort order for the column, ascending or descending, default is ascending.
$sort_order = isset($_GET['order']) && strtolower($_GET['order']) == 'desc' ? 'DESC' : 'ASC';
// Get the result...
if ($result = $mysqli->query('SELECT * FROM students ORDER BY ' . $column . ' ' . $sort_order)) {
// Some variables we need for the table.
$up_or_down = str_replace(array('ASC','DESC'), array('up','down'), $sort_order);
$asc_or_desc = $sort_order == 'ASC' ? 'desc' : 'asc';
$add_class = ' class="highlight"';
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP & MySQL Table Sorting by CodeShack</title>
<meta charset="utf-8">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.3.1/css/all.css" integrity="sha384-mzrmE5qonljUremFsqc01SB46JvROS7bZs3IO2EmfFsd15uHvIt+Y8vEf7N7fWAU" crossorigin="anonymous">
<style>
html {
font-family: Tahoma, Geneva, sans-serif;
padding: 10px;
}
table {
border-collapse: collapse;
width: 500px;
}
th {
background-color: #54585d;
border: 1px solid #54585d;
}
th:hover {
background-color: #64686e;
}
th a {
display: block;
text-decoration:none;
padding: 10px;
color: #ffffff;
font-weight: bold;
font-size: 13px;
}
th a i {
margin-left: 5px;
color: rgba(255,255,255,0.4);
}
td {
padding: 10px;
color: #636363;
border: 1px solid #dddfe1;
}
tr {
background-color: #ffffff;
}
tr .highlight {
background-color: #f9fafb;
}
</style>
</head>
<body>
<table>
<tr>
<th><a href="tablesort.php?column=name&order=<?php echo $asc_or_desc; ?>">Name<i class="fas fa-sort<?php echo $column == 'name' ? '-' . $up_or_down : ''; ?>"></i></a></th>
<th><a href="tablesort.php?column=age&order=<?php echo $asc_or_desc; ?>">Age<i class="fas fa-sort<?php echo $column == 'age' ? '-' . $up_or_down : ''; ?>"></i></a></th>
<th><a href="tablesort.php?column=joined&order=<?php echo $asc_or_desc; ?>">Join Date<i class="fas fa-sort<?php echo $column == 'joined' ? '-' . $up_or_down : ''; ?>"></i></a></th>
</tr>
<?php while ($row = $result->fetch_assoc()): ?>
<tr>
<td<?php echo $column == 'name' ? $add_class : ''; ?>><?php echo $row['name']; ?></td>
<td<?php echo $column == 'age' ? $add_class : ''; ?>><?php echo $row['age']; ?></td>
<td<?php echo $column == 'joined' ? $add_class : ''; ?>><?php echo $row['joined']; ?></td>
</tr>
<?php endwhile; ?>
</table>
</body>
</html>
<?php
$result->free();
}
?>If you're looking for a more advanced solution, consider the advanced CRUD application at the end of this post. It includes a lot more unique features, enhanced security, and will help us write more tutorials like this.
Conclusion
That's essentially how you sort tables with PHP and MySQL. You should now have a general understanding of how to create your own or modify the existing code in this tutorial.
It might take a bit more work, but if you want to implement a solution where the page doesn't reload, you could integrate JavaScript and AJAX.
Don't forget to share this post and follow us on social media if you haven't already. More useful tutorials will come. Enjoy coding!
If you would like to support us, consider purchasing the advanced crud application below as it will greatly help us create more tutorials and keep our website up and running. The advanced package includes improved code and more features.