Welcome to our comprehensive guide on using the ORDER BY statement in PHP MySQLi! This tutorial is designed for both beginners and intermediates, so let's dive right in. 🐟
ORDER BY Statement 📝The ORDER BY statement is a SQL command used to sort the result-set in ascending or descending order. It's a fundamental tool for managing databases and is widely used in PHP development.
SELECT column1, column2 FROM table_name ORDER BY column1;In the example above, column1 will be sorted in ascending order (alphabetical or numerical, depending on the data type). To sort in descending order, simply add the DESC keyword:
SELECT column1, column2 FROM table_name ORDER BY column1 DESC;Let's consider a simple database of users with the following structure:
CREATE TABLE users (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
);To sort the users by name in ascending order, you could use the following PHP code:
<?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 query
$sql = "SELECT name FROM users ORDER BY name";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo $row["name"] . "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>To sort the users in descending order, simply change the ORDER BY clause to ORDER BY name DESC:
$sql = "SELECT name FROM users ORDER BY name DESC";You can also sort using multiple columns. For example, to first sort users by name and then by email, you could use:
SELECT name, email FROM users ORDER BY name, email;What is the purpose of the `ORDER BY` statement in SQL?
That's it for our introduction to the ORDER BY statement in PHP MySQLi! As you practice more, you'll find yourself sorting data like a pro. 🚀 Stay tuned for more tutorials on CodeYourCraft. Happy coding! 🤖