Proper MySQL sorting using ORDER BY with PHP

When working with PHP and MySQL, an integral part is being able to sort through your records. When using MySQL, the ORDER BY clause is an essential tool in retrieving and displaying the correct rows. In this tutorial you will learn how to sort and display records based on specific criteria.

Download the database structure with entries
This is the database structure we’ll be using.

<code>
CREATE TABLE `friends` (
	`id` int (11),
	`first_name` varchar (75),
	`last_name` varchar (150),
	`food` varchar (150),
	`video_game` varchar (150),
	`phone` int (10)
); 
</code>
id first_name last_name food video_game phone
1 Joe Smith Sushi Skate 2 1234567890
2 Erin Lowe Pizza NBA2K11 2147483647
3 Larry Fitzgerald Sushi Skate 2 2134156890
4 Nancy Perlmen Chinese Halo 3 9876543210

If you need a copy of the INSERT data, download the full tutorial above.

First, let’s refresh ourselves with a basic query, and then we’ll follow by adding the ORDER BY clause.

<code>
$sql = "SELECT * FROM friends";
</code>

This query will pull all of the data and organize it from the most recent entry to the last, just like how the data above is organized. When adding the ORDER BY clause, you’re telling the database to retrieve the data by a certain column name.

<code>
$sql = "SELECT * FROM friends ORDER BY food DESC";
</code>

In this query the database is going to order the results by the food column in descending order. Since the food column is letters and not numbers, the results will be displayed in descending alphabetical order, beginning with Z.

id first_name last_name food video_game phone
1 Joe Smith Sushi Skate 2 1234567890
3 Larry Fitzgerald Sushi Skate 2 2134156890
2 Erin Lowe Pizza NBA2K11 2147483647
4 Nancy Perlmen Chinese Halo 3 9876543210

That’s it, but ORDER BY becomes a much more efficient query when adding a WHERE clause with it.

Leave a Comment

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