PHP Database with PDO - PHP Tutorial

PDO is PHP's database layer. It works with MySQL, PostgreSQL, SQLite and others through one API.

Connecting

PHP

<?php
$dsn = 'mysql:host=localhost;dbname=scolar;charset=utf8mb4';

try {
    $pdo = new PDO($dsn, 'username', 'password', [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
} catch (PDOException $e) {
    error_log($e->getMessage());
    exit('Database unavailable');
}

Those three options are not optional in practice:

ERRMODE_EXCEPTION — throw on error instead of failing silently.
FETCH_ASSOC — get clean associative arrays.
EMULATE_PREPARES => false — use real prepared statements.

And use utf8mb4, not utf8 — MySQL's utf8 cannot store emoji or some Devanagari sequences.

Selecting rows

PHP

<?php
$stmt = $pdo->query('SELECT id, title, price FROM books ORDER BY title');

foreach ($stmt as $row) {
    echo $row['title'] . ' — NPR ' . $row['price'] . '<br>';
}

// or all at once
$books = $pdo->query('SELECT * FROM books')->fetchAll();

Fetching a single row

PHP

<?php
$stmt = $pdo->prepare('SELECT * FROM books WHERE id = ?');
$stmt->execute([$id]);
$book = $stmt->fetch();

if (!$book) {
    http_response_code(404);
    exit('Book not found');
}

Inserting

PHP

<?php
$stmt = $pdo->prepare(
    'INSERT INTO books (title, author, price) VALUES (?, ?, ?)'
);
$stmt->execute([$title, $author, $price]);

$newId = $pdo->lastInsertId();

Updating and deleting

PHP

<?php
$stmt = $pdo->prepare('UPDATE books SET price = ? WHERE id = ?');
$stmt->execute([$price, $id]);

echo $stmt->rowCount() . ' row(s) updated';

$pdo->prepare('DELETE FROM books WHERE id = ?')->execute([$id]);

Transactions

When several statements must all succeed or all fail:

PHP

<?php
try {
    $pdo->beginTransaction();

    $pdo->prepare('UPDATE stock SET qty = qty - ? WHERE id = ?')
        ->execute([$qty, $productId]);

    $pdo->prepare('INSERT INTO orders (product_id, qty) VALUES (?, ?)')
        ->execute([$productId, $qty]);

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}