MySQL ORDER BY Clause Using PHP Scripts

MySQL ORDER BY Clause Using PHP Scripts

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    |
+—-+————+———–+————————–+
Post a Comment