| Previous | Home | Next |
In any program or a database it would be nice to make a MySQL results easier to read and understand. In database the simple or common way to do this in the real world is to order a big list of items by name or amount. In this condition the way of to order your result in MySQL is to use the ORDER BY statement.
If you are specify and sort alphabetical order or a numeric order just like as only numbers then what is ORDER BY statement does is take the a column name. Then when you use mysql_fetch_array to print out the result, the values are already sorted and easy to read.
The 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.
How can sorting a MySQL Query with 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 & MySQL Code:
<?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 "tablefirst" table
$result = mysql_query("SELECT * FROM tablefirst")
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>";
?>
Output
Name Age Amit 23 Akash 21 Aniket 15
What we need to do is add on to the existing MySQL statement "SELECT * FROM tablefirst" 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 & MySQL Code:
<?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>";
?>
Output
Name Age Aniket 15 Akash 21 Amit 23
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.
| Previous | Home | Next |