Reading records with SELECT
Learn to run a SELECT query with PDO and read records.
Reading records with SELECT
To read records you run a SELECT with a prepared statement. prepare() and execute() work the same way as INSERT.
To fetch results, fetchAll(PDO::FETCH_ASSOC) returns each row as an associative array; column names become array keys.
If you want one row at a time, use fetch(); each call returns the next row, and false when no rows remain.
Filtering with WHERE
The WHERE clause selects specific rows. Instead of writing values directly into the SQL text, you use a ? placeholder in a prepared statement.
For example, SELECT name FROM users WHERE role = ? returns only rows with the requested role; the value is passed safely via execute().
Ordering is not guaranteed, so adding ORDER BY is a good habit for deterministic output.
Exercise List users with the 'admin' role
In the skeleton below, use prepare, execute and fetchAll(PDO::FETCH_ASSOC) to print the names of users with role 'admin' ordered by ID.
Show example solution
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, role TEXT)");
$pdo->exec("INSERT INTO users (name, role) VALUES ('Alex', 'admin')");
$pdo->exec("INSERT INTO users (name, role) VALUES ('Sam', 'editor')");
$pdo->exec("INSERT INTO users (name, role) VALUES ('Dana', 'admin')");
$stmt = $pdo->prepare("SELECT name FROM users WHERE role = ? ORDER BY id");
$stmt->execute(['admin']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
echo $row['name'] . "\n";
}Test yourself
-
Which fetch mode returns an associative array?
- PDO::FETCH_ASSOC
- PDO::FETCH_NUM
- PDO::FETCH_BOTH
- PDO::FETCH_OBJ
Show answer
PDO::FETCH_ASSOC — PDO::FETCH_ASSOC returns each row keyed by column name.
-
Fetch all rows
$rows = $stmt->____(PDO::FETCH_ASSOC);Show answer
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC); -
Match methods to their results
- fetch()
- Returns one row
- fetchAll()
- Returns all rows
- fetchColumn()
- Returns one column
- prepare()
- Prepares the statement