Summary - PDO basics

PHP course · Advanced · Lesson 7/92

Build a user list app with connection, query, fetch and fetchAll.

Summary - PDO basics

In this group we covered PDO basics: new PDO($dsn) for the connection, PDO::ERRMODE_EXCEPTION for error handling, query() to run SQL and fetch()/fetchAll() to read rows.

In this lesson we will write a small user list app. Set up the connection, create the table, insert data and list it with fetchAll.

The first task sets up the connection and table; the second task pulls the data and prints it.

User list application

Our app has two parts: first create the table and insert data, then pull rows with fetchAll(PDO::FETCH_ASSOC) and print them.

FETCH_ASSOC returns an associative array keyed by column name; you process each row with foreach. This is the mode we will use most.

In later lessons we will learn to write safe queries with prepare(); for now we use fixed SQL with query().

Exercise Set up the connection and table

Connect to in-memory SQLite with new PDO('sqlite::memory:'). Set the error mode with setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION). Create the users (id INTEGER PRIMARY KEY, name TEXT, role TEXT) table and insert three users: Alex/admin, Sam/editor, Dana/admin. Finally print 'OK'.

Show example solution

:ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);. Write $pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, role TEXT)");. Add three INSERTs: Alex/admin, Sam/editor, Dana/admin. Finally write echo 'OK' . "\n";.
Example solution:
<?php
// Connection, error mode and table setup
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, 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')");
echo 'OK' . "\n";

Exercise Print the user list with fetchAll

This task builds on the previous solution. After setting up the connection, error mode and table, insert three users. Then run query("SELECT name, role FROM users ORDER BY id"); get all rows with fetchAll(PDO::FETCH_ASSOC) and print each row with foreach in the name - role form.

Show example solution

:FETCH_ASSOC);. Print with foreach ($rows as $row) { echo $row['name'] . ' - ' . $row['role'] . "\n"; }.
Example solution:
<?php
// Connection, error mode and table setup
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, 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->query("SELECT name, role FROM users ORDER BY id");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    echo $row['name'] . ' - ' . $row['role'] . "\n";
}

Test yourself

How is a PDO connection closed?
- $pdo = null
- $pdo->close()
- $pdo->disconnect()
- PDO::close($pdo)
Show answer

$pdo = null — Correct. $pdo = null closes the connection; it also closes automatically at the end of the script.

All lessons in Advanced

Summary - PDO basics