To sort the records in ascending or descending order, we have to use MYSQL ORDER BY Clause. Syntax:
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
Consider our table has the following records:
+—-+————+———–+————————–+
| id | first_name | last_name | email |
+—-+————+———–+————————–+
| 1 | Sabbir | Rahaman |sabbirrahaman@mail.com |
| 2 | Mahin | Chowdhury |mahinchowdhury@mail.com |
| 3 | Sadman | Faijul | sadmanfaijul@mail.com |
| 4 | Mr. | Zami | zami009@mail.com |
| 5 | John | Potter | johnpotter@mail.com |
+—-+————+———–+————————–+
MySQLi Procedural
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
// Attempt select query execution with order by clause
$sql = "SELECT * FROM persons ORDER BY first_name";
if($result = $mysqli->query($sql)){
if($result->num_rows > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first_name</th>";
echo "<th>last_name</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = $result->fetch_array()){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
$result->free();
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}
// Close connection
$mysqli->close();
?>
MySQLi Object Oriented
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
// Attempt select query execution with order by clause
$sql = "SELECT * FROM persons ORDER BY first_name";
if($result = $mysqli->query($sql)){
if($result->num_rows > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first_name</th>";
echo "<th>last_name</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = $result->fetch_array()){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
$result->free();
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}
// Close connection
$mysqli->close();
?>
PDO
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt select query execution
try{
$sql = "SELECT * FROM persons ORDER BY first_name";
$result = $pdo->query($sql);
if($result->rowCount() > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first_name</th>";
echo "<th>last_name</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = $result->fetch()){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
unset($result);
} else{
echo "No records matching your query were found.";
}
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
// Close connection
unset($pdo);
?>
After running the above code our table looks like this:
+—-+————+———–+————————–+
| id | first_name | last_name | email |
+—-+————+———–+————————–+
| 5 | John | Potter | johnpotter@mail.com |
| 2 | Mahin | Chowdhury |mahinchowdhury@mail.com |
| 4 | Mr. | Zami | zami009@mail.com |
| 1 | Sabbir | Rahaman |sabbirrahaman@mail.com |
| 3 | Sadman | Faijul | sadmanfaijul@mail.com |
+—-+————+———–+————————–+