PHP Programing language

adplus-dvertising
PHP MySQL ORDER BY
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