How to Sort Table Columns with PHP and MySQL
Updated on by David Adams
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
We need a database to connect to so we can display records in our HTML table. You can either use an existing database or run the SQL statement below either with phpMyAdmin or your preferred database management tool.
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
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/2018'),
(2, 'Sheri Warner', 19, '03/05/2018'),
(3, 'Sean Glover', 24, '24/07/2018'),
(4, 'John West', 17, '13/08/2018'),
(5, 'Rufus Clarke', 20, '28/07/2018'),
(6, 'Roosevelt Myers', 20, '25/07/2018'),
(7, 'Elvira Andrews', 22, '02/07/2018'),
(8, 'Richard Cook', 26, '19/07/2018'),
(9, 'Lorenzo Harris', 23, '01/07/2018'),
(10, 'Eduardo Hoffman', 17, '03/07/2018'),
(11, 'Jeanne Fisher', 20, '13/08/2018'),
(12, 'Tracy Bowers', 30, '07/07/2018'),
(13, 'Heidi Lawrence', 18, '04/06/2018'),
(14, 'Tara Holland', 25, '01/07/1991'),
(15, 'Grant Edwards', 22, '22/06/2018'),
(16, 'Bradford Green', 29, '02/05/2018'),
(17, 'Gwen Schultz', 20, '02/05/2018'),
(18, 'Hope Dawson', 28, '21/08/2018'),
(19, 'Florence Osborne', 19, '17/05/2018'),
(20, 'Rickey Poole', 26, '28/06/2018'),
(21, 'Casey Sutton', 28, '06/07/2018'),
(22, 'Willie Lowe', 23, '11/05/2018'),
(23, 'Stephen Schultz', 28, '15/07/2018'),
(24, 'Eileen Lynch', 18, '12/06/2018'),
(25, 'Aaron Ruiz', 29, '02/05/2018'),
(26, 'Mae Murray', 30, '24/06/2018'),
(27, 'Regina Hanson', 21, '26/07/2018'),
(28, 'Cameron Mclaughlin', 20, '29/07/2018'),
(29, 'Earl Hale', 17, '30/06/2018'),
(30, 'Marta Blair', 24, '10/06/2018'),
(31, 'Alberta Silva', 22, '05/06/2018'),
(32, 'Joanna Holmes', 20, '20/05/2018'),
(33, 'Alex Brock', 30, '12/05/2018'),
(34, 'Colin Wright', 19, '28/05/2018'),
(35, 'Peter Schmidt', 25, '10/07/2018'),
(36, 'Joshua Price', 27, '13/07/2018'),
(37, 'Elias Chandler', 22, '19/07/2018'),
(38, 'Stanley Ross', 21, '02/06/2018'),
(39, 'Vera Cole', 26, '02/05/2018'),
(40, 'Johnny Daniels', 29, '19/07/2018'),
(41, 'Yvonne Hopkins', 21, '16/07/2018');
ALTER TABLE `students`
ADD PRIMARY KEY (`id`);
ALTER TABLE `students`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=42;The above statement will create the database tablesort with the table students, we'll be using this table for this tutorial.
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 create some variables that we will use:
$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, this is basically a security check to prevent SQL injection, we want to make sure that the user doesn't try anything suspicious, you can change this variable, add or remove column names in the array.
$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 it will default to ascending.
We can now get 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, we want those small up and down icons to appear next to table column names, this variable basically determines which way the column is sorted and will show that 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 basically highlight the active column, this is optional, you don't really need it, it just makes the table more appealing.
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 loop our records from our table students and sort them accordingly.
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 need Font Awesome for the icons, add the Font Awesome CDN code in the head section of your 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 look something like this:
Full Source
<?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 of which 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();
}
?>Conclusion
That's basically how you sort tables with PHP and MySQL, you should now have a general understanding on how to create your own or modify our existing code.
It might take a bit more work but if you want to implement a solution where the page doesn't reload you could use 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!