PHP MySQL – ORDER BY Clause

The order by clause is used to fetch data in ascending order or descending order on the basis of column.

The ORDER BY clause sorts the records in ascending order by default.

To sort the records in descending order, use the DESC keyword.

Syntax

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC 

Examples

The following example selects the id, firstname and lastname columns from the Students table. The records will be ordered by the lastname column.

Example – MySQLi Procedural

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, firstname, lastname FROM Students ORDER BY lastname";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}

mysqli_close($conn);
?>

Explanation

• First, we set up the SQL query that selects the id, firstname and lastname columns from the Students table.
• The records will be ordered by the lastname column.
• The next line of code runs the query and puts the resulting data into a variable called $result.
• Then, the function mysqli_num_rows() checks if there are more than zero rows returned.
• If there are more than zero rows returned, the function mysqli_fetch_assoc() puts all the results into an associative array that we can loop through.
• The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.

Example – MySQLi Object-oriented

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM Students ORDER BY lastname";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  echo "<table><tr><th>ID</th><th>Name</th></tr>";
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>";
  }
  echo "</table>";
} else {
  echo "0 results";
}
$conn->close();
?>