A Complete Guide to Database Queries and Sorting in PHP

M66 2025-07-26

Overview of PHP Database Queries and Sorting

Working with databases is an essential part of PHP development. Whether it's reading, processing, or sorting data, mastering these operations significantly improves your application's efficiency and user experience. This guide explains how to perform database queries and sort results using PHP's mysqli and PDO extensions.

Connecting to a Database Using mysqli

The mysqli extension is a common way to interact with MySQL databases in PHP. Here's how you can establish a connection:

$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);

if ($conn->connect_error) {
    die("Database connection failed: " . $conn->connect_error);
}

Once connected, you can run SQL statements to retrieve data from the database.

Executing SQL Queries and Displaying Results

Below is an example of using mysqli to execute a SELECT statement and fetch results:

$sql = "SELECT * FROM table_name";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Field 1: " . $row["column1"] . ", Field 2: " . $row["column2"] . "<br>";
    }
} else {
    echo "No results found";
}

The fetch_assoc() function retrieves each row as an associative array, which can then be displayed or processed as needed.

Sorting Query Results

Sorting results by a specific column is often necessary in real-world applications. You can use the ORDER BY clause for this. Here's an example that sorts results in descending order by column1:

$sql = "SELECT * FROM table_name ORDER BY column1 DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Field 1: " . $row["column1"] . ", Field 2: " . $row["column2"] . "<br>";
    }
} else {
    echo "No results found";
}

To sort in ascending order, simply replace DESC with ASC.

Querying Databases with PDO

Besides mysqli, PHP also offers the PDO (PHP Data Objects) extension, which provides a unified interface for multiple database systems. Here’s how to use PDO for a similar query:

$dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
$conn = new PDO($dsn, $db_user, $db_pass, $options);

$sql = "SELECT * FROM table_name";
$result = $conn->query($sql);

if ($result->rowCount() > 0) {
    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
        echo "Field 1: " . $row["column1"] . ", Field 2: " . $row["column2"] . "<br>";
    }
} else {
    echo "No results found";
}

Using PDO::FETCH_ASSOC ensures that results are returned as associative arrays, similar to mysqli.

Conclusion

Whether you choose mysqli or PDO, PHP provides powerful tools for interacting with databases. You can easily perform queries, retrieve data, and sort results as needed. Mastering these techniques is essential for any PHP developer aiming to build efficient and dynamic web applications.

PDO
mysqli