Posted By
Bajarangi soft ,
Posted On 18-09-2020
Syntax of ORDER BY Clause and Usage:
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
Lets learn
1.Create database and table
Student table
| StudentID | student_name | Address | Age | Section |
Branch |
Percentage | Fee |
1 |
Liam | 23 MG road Banglore |
24 | A | CSE | 66 | 66,000 |
| 2 | Andrew | 2415 Anthony market str | 25 | A | ISE | 80 | 65,000 |
| 4 | Ryan | 18 Krishna sq. Banglore |
26 | B | EE | 67 | 70,000 |
| 5 | Charles | 878 Eli Berguvsvägen 8 | 24 | C | ECE | 52 | 75,000 |
for above table we use ORDER BY query
Example for ascending order
The SQL statement selects all student from the "student" table, sorted by the "student_name" column in ascending order
SELECT * FROM student
ORDER BY student_name;//default ascending order
or
SELECT * FROM student
ORDER BY student_name ASC;
Example for Descending order
The SQL statement selects all student from the "student" table, sorted by the "student_name" column in descending order
SELECT * FROM student
ORDER BY student_name DESC;// descending order
1.Order By Clause using MySQLi Procedural Method :
<?php
$connect = mysqli_connect("localhost", "root", "", "school_management");
if ($connect->connect_error) {
die("Connection failed: " . $connect->connect_error);
}
$sql = "SELECT * FROM student ORDER BY student_name";// you can also use DESC
if($asc = mysqli_query($connect, $sql)){
if(mysqli_num_rows($asc) > 0){
echo "<table class='table'>
<tr>
<th>student_name</th>
<th>age</th>
</tr>
";
while($row = $asc->fetch_assoc()){
echo "<tr>
<td>" . $row['student_name'] . "</td>";
echo "<td>" . $row['age'] . "</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
?>
2. Order By Clause using MySQLi Object Oriented Method :
<?php
$connect = new mysqli("localhost", "root", "", "school_management");
if ($connect->connect_error) {
die("Connection failed: " . $connect->connect_error);
}
$sql = "SELECT * FROM student ORDER BY student_name DESC";
if($desc = mysqli_query($connect, $sql)){
if(mysqli_num_rows($desc) > 0){
echo "<table class='table' style='width: 35%; max-width: 0%; margin-bottom: 0;'>
<tr>
<th>student_name</th>
<th>age</th>
</tr>
";
while($row = $desc->fetch_assoc()){
echo "<tr>
<td>" . $row['student_name'] . "</td>";
echo "<td>" . $row['age'] . "</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
?>
3.Order By Clause using PDO :
<?php
try {
$pdo = new PDO("mysql:host=localhost;
dbname=school_management", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("ERROR: Could not connect. "
. $e->getMessage());
}
try {
$sql = "SELECT * FROM student ORDER BY student_name";
$result = $pdo->query($sql);
if ($result->rowCount() > 0) {
echo "<table class='table' style='width: 35%; max-width: 0%; margin-bottom: 0;'>
<tr>
<th>student_name</th>
<th>age</th>
</tr>
";
while ($row = $result->fetch()) {
echo "<tr>";
echo "<td>" . $row['student_name'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "</tr>";
}
echo "</table>";
unset($result);
} else {
echo "No matching records are found.";
}
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql. "
. $e->getMessage());
}
unset($pdo);
?>
selects the StudentID, student_name and age columns from the student table of school_management. The records will be ordered by the age column.num_rows() checks for more than zero rows returned.If there are more than zero rows returned, the function fetch_assoc() will call and that goes through the while() loop loops through the result set and outputs the data from the StudentID, student_name and age columns.