PHP & MySQL - Order By Clause
The MySQL ORDER BY clause is used to sort the result table in ascending or descending order. By default, ORDER BY clause sorts the result in ascending order, however it can be specified using ASC keyword. To sort the result in descending order, DESC keyword is used.
The syntax for using ORDER BY keyword is given below:
SELECT column1, column2, column3, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
Along with this, to connect to the MySQL server, mysqli_connect() function can be used. After establishing the connection, mysqli_query() function can be used to perform a query on the database.
The num_rows() function can be used to check if there are more than zero rows returned. Then, the fetch_assoc() function can be used to fetch the result set as an associative array. Later on the free_result() function can be used to free the memory associated with the result.
Select and Order Data - Object-oriented style
Consider a database containing a table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
The example below demonstrates how to use the ORDER BY clause to sort the data of this table by Age using object-oriented style.
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDatabase"; //establishing connection $mysqli = new mysqli($servername, $username, $password, $dbname); //checking connection if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: ". $mysqli->connect_error; exit(); } //getting query result from the database $sql = "SELECT Name, Age FROM Employee ORDER BY Age ASC"; $result = $mysqli->query($sql); //fetching associative array while ($row = $result->fetch_assoc()) { printf("%s, %d\n", $row["Name"], $row["Age"]); } //free result set $result->free_result(); //closing the connection $mysqli->close(); ?>
The output of the above code will be:
Marry, 24 John, 25 Jo, 27 Ramesh, 28 Huang, 28 Kim, 30
Select and Order Data - Procedural style
To obtain the same result using procedural style, the following script can be used.
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDatabase"; //establishing connection $mysqli = mysqli_connect($servername, $username, $password, $dbname); //checking connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: ". mysqli_connect_error(); exit(); } //getting query result from the database $sql = "SELECT Name, Age FROM Employee ORDER BY Age ASC"; $result = mysqli_query($mysqli, $sql); //fetching associative array while ($row = mysqli_fetch_assoc($result)) { printf("%s, %d\n", $row["Name"], $row["Age"]); } //free result set mysqli_free_result($result); //closing the connection mysqli_close($mysqli); ?>
The output of the above code will be:
Marry, 24 John, 25 Jo, 27 Ramesh, 28 Huang, 28 Kim, 30
Complete PHP MySQLi Reference
For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.