How to sort rows of HTML table that are called from MySQL
I know it’s such a basic thing, but a Google search hasn’t shown me how to re-sort the rows after clicking the th links. I’ve got this:
Type: Description: Recorded Date: Added Date:
mysql_close(); ?>
I need to be able to click type and sort alphabetically, and click on either Recorded Date or Added Date and sort by date. I see that I need to have the MySQL queries do this, but do I set them up as conditionals with a href tags?
6 Answers 6
The easiest way to do this would be to put a link on your column headers, pointing to the same page. In the query string, put a variable so that you know what they clicked on, and then use ORDER BY in your SQL query to perform the ordering.
The HTML would look like this:
Type: Description: Recorded Date: Added Date:
And in the php code, do something like this:
elseif ($_GET['sort'] == 'desc') < $sql .= " ORDER BY Description"; >elseif ($_GET['sort'] == 'recorded') < $sql .= " ORDER BY DateRecorded"; >elseif($_GET['sort'] == 'added') < $sql .= " ORDER BY DateAdded"; >$>
Notice that you shouldn’t take the $_GET value directly and append it to your query. As some user could got to MyPage.php?sort=; DELETE FROM MyTable;
I can’t edit it myself, but I found the errors. Main one is you need $sql not sql. Also, my table names are different on a couple columns. But, THANK you for doing this! It got me with the right answer!
editted to fix the syntax problems. PHP isn’t a language I use everyday. Haven’t used it in a while. Those $ always get me. I always found it really annoying to have to start variables with a special character. Can’t think of why it would be required. No other language I can think of requires anything like that.
Nice. You can dry it out a bit with this map: $order = array( «type» => array(«Type»,»type»), «desc» => array(«Description», «Description»), . ); Looping through it you can both generate in loop the table headers and directly map the order parameter to the column.
That’s actually pretty easy, here’s a possible approach:
Type: Description: Recorded Date: Added Date:
$query = 'SELECT * FROM aTable ORDER BY '.$order; // retrieve and show the data :) ?>
There’s definitely NO possible SQL injection, look closer! Whitelisted the possible fields to order by.
I didn’t downvote, but for some reason, I don’t remember the checks being there when I first saw your answer. However I may just have missed it, and some others may have missed it as well. I like your style better than mine, so I’m giving you an upvote too.
@Kibbee you didn’t mistake it for Sarfraz’s attempt did you? Which he’s now deleted but had sql injection issues?
No, I still see Sarfraz’s answer. I might have had the 2 confused. For some reason I remember a straight concatenation of the query and the $_GET. Guess I just missed something, others may have also.
I like this answer best, however I just appended DELETE added_date=’some date’ and the SQL INJECTION deleted the record. Can someone help with preventing this.
A SIMPLE TABLE SORT PHP CODE:
(the simple table for several values processing and sorting, using this sortable.js script )
tbody tr td ?> Hi User. PUT your values projectX The due amount ProjectX Due amount $value) < echo ''.$First[$indx].' '.$Second[$indx].' '; > ?> TOTAL = 111111111 Still to spend = 5555555
//this is a php file a:link a:visited A:active A:hover table < width:50%; height:50%; >table,th,td < border:1px solid black; >th,td < text-align:center; background-color:yellow; >th ------------------------------------------------------------------------ that is another php file a:link a:visited A:active A:hover table < width:50%; height:50%; >table,th,td < border:1px solid black; >th,td < text-align:center; background-color:yellow; >th Id Name Email Password "; echo ""; while ($row=mysql_fetch_array($sql)) "; > echo "";?> that will sort the table using ajax What if i have dynamic tables for example. Each records or rows is a different table. Will this solution work for the same?
This is the most simple solution that use:
// Use this as first line upon load of page
// Then simply sort according to that variable
$sql="SELECT * FROM tracks ORDER BY $sort"; echo ''; echo 'Title'; echo 'Album'; echo 'Artist'; echo 'Count'; echo ' ';
It depends on nature of your data. The answer varies based on its size and data type. I saw a lot of SQL solutions based on ORDER BY . I would like to suggest javascript alternatives.
In all answers, I don’t see anyone mentioning pagination problem for your future table. Let’s make it easier for you. If your table doesn’t have pagination, it’s more likely that a javascript solution makes everything neat and clean for you on the client side. If you think this table will explode after you put data in it, you have to think about pagination as well. (you have to go to first page every time when you change the sorting column)
Another aspect is the data type. If you use SQL you have to be careful about the type of your data and what kind of sorting suites for it. For example, if in one of your VARCHAR columns you store integer numbers, the sorting will not take their integer value into account: instead of 1, 2, 11, 22 you will get 1, 11, 2, 22 .
How to Sort Table Columns with PHP and MySQL
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 = ' >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.
">Name ">Age ">Join Date fetch_assoc()): ?> > > >
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
We also need Font Awesome for the icons, add the Font Awesome CDN code in the head section of your document:
Our table should now look something like this:
Full Source
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 = ' html> 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 ">Name ">Age ">Join Date fetch_assoc()): ?> > > > 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!
About Author
David Adams
Enthusiastic website developer, I've been designing and developing web applications for over 10 years, I enjoy the creativity I put into my projects and enjoy what others bring to the awesome web. My goal is to help newcomers learn the ways of the web.