Reading Data (SELECT / FETCH)

To retrieve data in PDO, we use the query() method (for simple queries) or prepared statements. We then process the results using the fetchAll() method, which returns all rows at once in the form of an array.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "pdo_database";

try {
    // STEP 1: Establish a secure database connection
    $connection = new PDO("mysql:host=$server;dbname=$database;charset=utf8mb4", $username, $password);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // STEP 2: Execute the SQL query
    // The query() method sends the SQL statement to the database and returns a data object.
    $stmt = $connection->query("SELECT id, first_name, last_name, email FROM users");

    // STEP 3: Fetching data
    // PDO::FETCH_ASSOC ensures we get an associative array (the key is the column name).
    // This is essential for code readability, as we don't have to guess column numbers.
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Prepare HTML table for display
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
    echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th><th>Email</th></tr>";

    // STEP 4: Iterate through results
    // We use a foreach loop to walk through every row returned by the database.
    foreach ($results as $row) {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['first_name'] . "</td>";
        echo "<td>" . $row['last_name'] . "</td>";
        echo "<td>" . $row['email'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";

} catch (PDOException $e) {
    // In case of an error (e.g., wrong password or table name), display a message.
    echo "Error: " . $e->getMessage();
}

// STEP 5: Closing the connection
// Always clean up after yourself and free server resources.
$connection = null;
?>

RESULT

The result will be displayed here...

By using the PDO::FETCH_ASSOC mode, we ensure that the results are returned as an associative array, where the keys match the column names in the database (e.g., $row["name"]). In the example below, we will output all users from our table.

The query() method is suitable for queries without external parameters.
fetchAll() is ideal for small to medium-sized data sets.
We use a foreach loop for an elegant iteration through all retrieved rows.