Fetching results (fetch, fetchAll) in PHP - Mini Project: Build & Apply

Fetching results using fetch and fetchAll in PHP

📖 Scenario: You are building a simple PHP script to get user data from a database. You want to learn how to fetch one row at a time and how to fetch all rows at once.

🎯 Goal: Learn how to use fetch() and fetchAll() methods to get data from a database query result.

📋 What You'll Learn

Create a PDO connection to an SQLite in-memory database

Create a table called users with columns id, name, and email

Insert three users with exact data

Use fetch() to get one user row

Use fetchAll() to get all user rows

Print the fetched results

💡 Why This Matters

🌍 Real World

Fetching data from databases is a common task in web development. Knowing how to get one row or all rows helps you display data efficiently.

💼 Career

Many jobs require working with databases and PHP. Understanding fetch methods is essential for backend development and data handling.

1

Create the database and insert users

Create a PDO connection called $pdo to an SQLite in-memory database. Then create a table called users with columns id (integer primary key), name (text), and email (text). Insert these three users exactly: (1, 'Alice', '[e-mail]'), (2, 'Bob', '[e-mail]'), and (3, 'Charlie', '[e-mail]').

PHP

<?php
// Create PDO connection and insert users
// Your code here

Need a hint?

Use new PDO('sqlite::memory:') to create the database. Use exec() to run SQL commands.

2

Prepare a SELECT query

Create a variable called $stmt that prepares the SQL query SELECT * FROM users using the $pdo connection.

PHP

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)");
$pdo->exec("INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[e-mail]'), (2, 'Bob', '[e-mail]'), (3, 'Charlie', '[e-mail]')");
// Prepare the SELECT query
// Your code here

Need a hint?

Use $pdo->prepare() with the exact SQL string.

3

Fetch one user row using fetch()

Execute the prepared statement $stmt. Then create a variable called $oneUser that fetches one row using fetch() with PDO::FETCH_ASSOC to get an associative array.

PHP

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)");
$pdo->exec("INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[e-mail]'), (2, 'Bob', '[e-mail]'), (3, 'Charlie', '[e-mail]')");
$stmt = $pdo->prepare('SELECT * FROM users');
// Execute and fetch one row
// Your code here

Need a hint?

Call execute() before fetching. Use fetch(PDO::FETCH_ASSOC) to get one row as an associative array.

4

Fetch all user rows using fetchAll() and print results

Create a variable called $allUsers that fetches all rows using fetchAll() with PDO::FETCH_ASSOC. Then print $oneUser and $allUsers using print_r().

PHP

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)");
$pdo->exec("INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[e-mail]'), (2, 'Bob', '[e-mail]'), (3, 'Charlie', '[e-mail]')");
$stmt = $pdo->prepare('SELECT * FROM users');
$stmt->execute();
$oneUser = $stmt->fetch(PDO::FETCH_ASSOC);
// Fetch all rows and print results
// Your code here

Need a hint?

Use fetchAll(PDO::FETCH_ASSOC) to get all rows as an array of associative arrays. Use print_r() to display arrays.