Php Mysql Select Orderby
In this article, we’ll guide you through the process of using PHP MySQL select orderby data from a database table. Furthermore, we will also discuss some best practices for using ORDER BY effectively, as well as common mistakes to avoid when you are using it.
If you’re building a web application that uses a MySQL database, you may need to sort your query results in a specific order. This is where the ORDER BY clause in MySQL comes in handy.
The ORDER BY clause is a very useful clause when it comes to sorting data in MySQL.
A table can be sorted on a number of columns, either ascending or descending, based on the values in one or more of the columns in the table.
Select Order MySQL Database
In ORDER BY clause the results are sorted in ascending or descending order according to the criteria you provide.
A record is sorted in ascending order by default when the ORDER BY clause is used.
By using the keyword DESC, you will be able to sort the records in descending order.
SELECT column_name(s)
FROM table_name ORDER BY column_name(s) ASC|DESC
Select Order With MySQLi
In the following example, we are going to select all (*) columns from the Users table.
According to the Age column, the records will be sorted in the following order:
<?php
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";
// Creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Checking connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM Users ORDER BY age";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Printing each data row
while ($row = $result->fetch_assoc()) {
echo "id: " . $row["id"] . "<br>First Name: " . $row["firstname"] . "<br>Last Name: " .
$row["lastname"] . "<br>Email: ".$row["email"]."<br>Age: ".$row["age"]."<br>Gender: ".$row["gender"]."";
}
} else {
echo "0 results";
}
$conn->close();
?>
Using the example above, here are explanations of lines of code:
- Firstly, we need to create an SQL query that will select all the columns from the Users table. According to the age column, the records will be sorted.
- Next, the program runs the query and places the results in a variable called $result, which stores the results of the query.
- After that, num_rows() checks whether there are more than zero rows returned as a result of the query. A function called fetch_assoc() creates an associative array, which can be used to loop through all the rows returned if there are more than zero returned rows. By using the while() function, we loop through the result set and output the data from all the columns in the result set.
As an example, the following is a MySQLi procedural way of displaying the same information as shown above:
<?php
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Checking connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT * FROM Users ORDER BY age";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Printing each data row
while ($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"] . "<br>First Name: " . $row["firstname"] . "<br>Last Name: " .
$row["lastname"] . "<br>Email: ".$row["email"]."<br>Age: ".$row["age"]."<br>Gender: ".$row["gender"]."";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
There is also the option of putting the results into an HTML table as follows:
<?php
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";
// Creating connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Checking connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM Users ORDER BY age";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table><tr><th>ID</th><th>Name</th><th>Email</th><th>Age</th><th>Gender</th></tr>";
//Printing data of each row
while ($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["id"] . "</td><td>" . $row["firstname"] . " " . $row["lastname"] . "</td>
<td>" . $row["email"] . "</td><td>" . $row["age"] . "</td><td>" . $row["gender"] . "</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>
Select Data With PDO (+ Prepared Statements)
Prepared statements with PDO are used in the following example.
Here, we are going to select all the columns that are going to be present in the Users table.
The records will be sorted according to the age column and displayed as a
table in HTML as follows:
<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th><th>Email</th><th>Age</th><th>Gender</th></tr>";
class TableRows extends RecursiveIteratorIterator
{
function __construct($iterable)
{
parent::__construct($iterable, self::LEAVES_ONLY);
}
function current()
{
return "<td style='width:150px;border:1px solid black;'>" . parent::current() . "</td>";
}
function beginChildren()
{
echo "<tr>";
}
function endChildren()
{
echo "</tr>" . " ";
}
}
$servername = "localhost";
$username = "my_user";
$password = "my_pass";
$dbname = "mrx_test_db";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT * FROM Users ORDER BY age");
$stmt->execute();
//resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach (new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $key => $value) {
echo $value;
}
}
catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>
Example Explanation
We can fetch data from a MySQL database and display it in an HTML table sorted by a column using PHP. First, we can create an HTML table with a header row containing the column names. Then, we can define a PHP class called “TableRows” that extends the “RecursiveIteratorIterator” class to iterate over the query results and create table rows.
In our “TableRows” class, we can use the “current” method to return the cell contents wrapped in a <td> element with some inline styling, and the “beginChildren” and “endChildren” methods to wrap each row of the table in a <tr> element.
We can then create a PDO connection to the MySQL database and prepare a SQL statement to select all columns from the “Users” table, sorted by the “age” column. The SQL statement is executed, and the resulting array is converted to an associative array using the “setFetchMode” method of the PDO statement object.
To generate the table rows, we can use a foreach loop to iterate over the query results and call our “TableRows” class, echoing out the results to the HTML page. If any errors occur during the execution of the code, we can catch them using a try-catch block and display an error message.
