MySQL Order By

It would be nice to be able to make MySQL results easier to read and understand. A common way to do this in the real world is to order a big list of items by name or amount. The way to order your result in MySQL is to use the ORDER BY statement.

What ORDER BY does is take the a column name that you specify and sort it in alphabetical order (or numeric order if you are using numbers). Then when you use mysql_fetch_array to print out the result, the values are already sorted and easy to read.

Ordering is also used quite frequently to add additional functionality to webpages that use any type of column layout. For example, some forums let you sort by date, thread title, post count, view count, and more.

Sorting a MySQL Query - ORDER BY

Let's use the same query we had in MySQL Select and modify it to ORDER BY the person's age. The code from MySQL Select looked like...

                <?php
                // Make a MySQL Connection
                mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
                mysql_select_db("test") or die(mysql_error());

                // Get all the data from the "example" table
                $result = mysql_query("SELECT * FROM example")
                or die(mysql_error());

                echo "<table border='1'>";
                echo "<tr> <th>Name</th> <th>Age</th> </tr>";
                // keeps getting the next row until there are no more to get
                while($row = mysql_fetch_array( $result )) {
                // Print out the contents of each row into a table
                echo "<tr><td>";
                echo $row['name'];
                echo "</td><td>";
                echo $row['age'];
                echo "</td></tr>";
                }

                echo "</table>";
                ?>
              

Display:

What we need to do is add on to the existing MySQL statement "SELECT * FROM example" to include our new ordering requirement. When you choose to order a column, be sure that your ORDER BY appears after the SELECT ... FROM part of the MySQL statement.

                <?php
                // Make a MySQL Connection
                mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
                mysql_select_db("test") or die(mysql_error());

                // Get all the data from the "example" table
                $result = mysql_query("SELECT * FROM example ORDER BY age")
                or die(mysql_error());

                echo "<table border='1'>";
                echo "<tr> <th>Name</th> <th>Age</th> </tr>";
                // keeps getting the next row until there are no more to get
                while($row = mysql_fetch_array( $result )) {
                // Print out the contents of each row into a table
                echo "<tr><td>";
                echo $row['name'];
                echo "</td><td>";
                echo $row['age'];
                echo "</td></tr>";
                }

                echo "</table>";
                ?>
              

Display:

Presto! We have an ordered MySQL result! Notice that we didn't have to change any of our PHP code. Remember this whenever you're editing a PHP script that uses MySQL. Sometimes it may be easier to just tweak your MySQL query instead of trying to mess around in PHP.