Select Data

Querying & Fetching Data (SELECT, fetch(), fetchAll())

Executing SELECT queries retrieves matching rows from MySQL database tables. PDO provides flexible fetch modes like PDO::FETCH_ASSOC, PDO::FETCH_OBJ, and PDO::FETCH_COLUMN.

Fetch Modes Comparison

• PDO::FETCH_ASSOC: Returns row as associative array (['id' => 1, 'name' => 'Alice']).
• PDO::FETCH_OBJ: Returns row as stdClass object ($row->name).
• PDO::FETCH_COLUMN: Returns a single column array from all matching rows.

flowchart TD
    A["$stmt->execute()"] --> B{"Choose Fetch Method"}
    B -- Single Row --> C["$row = $stmt->fetch(PDO::FETCH_ASSOC)"]
    B -- All Matching Rows --> D["$rows = $stmt->fetchAll(PDO::FETCH_ASSOC)"]
    B -- Iterate One By One (Low Memory) --> E["while ($row = $stmt->fetch()) { ... }"]

Practical Code Example

<?php
declare(strict_types=1);

try {
    $pdo = new PDO("mysql:host=127.0.0.1;dbname=ecommerce_db;charset=utf8mb4", 'root', 'secret_password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);

    // 1. Fetching Multiple Rows with fetchAll()
    $sql = "SELECT id, username, email FROM users WHERE status = :status ORDER BY id DESC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['status' => 'active']);
    $users = $stmt->fetchAll();

    echo "--- Active Users List (" . count($users) . ") ---
";
    foreach ($users as $user) {
        echo "ID: {$user['id']} | User: {$user['username']} | Email: {$user['email']}
";
    }

    // 2. Fetching Single Row with fetch()
    $singleSql = "SELECT id, username, email FROM users WHERE id = :id";
    $singleStmt = $pdo->prepare($singleSql);
    $singleStmt->execute(['id' => 1]);
    $singleUser = $singleStmt->fetch();

    if ($singleUser) {
        echo "
Found Single User: " . $singleUser['username'] . "
";
    }

} catch (PDOException $e) {
    die("Query Execution Error: " . $e->getMessage());
}

Best Practices

• Specify Column Names explicitly: Write SELECT id, name instead of SELECT * to reduce network and memory overhead.
• Use fetch() in while Loops for Large Result Sets: Avoid memory spikes by fetching rows one at a time when processing thousands of records.
• Check for false on fetch(): fetch() returns false when no matching record row is found.

Self-Check Challenge

What method on a PDOStatement object fetches ALL result rows at once into an array? ($stmt->fetchAll())