PHP Databases with PDO

Connect PHP to MySQL or SQLite with PDO, run queries, use prepared statements to prevent SQL injection and turn rows into HTML.

Lesson 17 of 86Advanced17 min read

Most dynamic websites keep their data in a database: products, users, orders, posts and comments. PHP's job is to fetch that data with SQL, turn it into HTML, and save new data when visitors submit forms. If you've worked through the SQL tutorial, you already know the queries; this lesson shows how to run them from PHP.

PHP's recommended way to talk to databases is PDO (PHP Data Objects). It works the same way with MySQL, PostgreSQL, SQLite and others, so your code barely changes if the database does. Most importantly, PDO supports prepared statements, which keep user input separate from SQL and prevent SQL injection, one of the most damaging security mistakes a website can make.

In this lesson you'll connect to a database, read rows, insert and update data with prepared statements, get the ID of a new row, wrap related changes in a transaction and display results safely in a page.

Connecting with PDO

PHP
$pdo = new PDO(
    'mysql:host=localhost;dbname=shop;charset=utf8mb4',
    'shop_user',
    'secret-password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);
  • The first argument is the DSN: the database type, host, database name and character set. For SQLite it's simply 'sqlite:' . __DIR__ . '/shop.db'.
  • ERRMODE_EXCEPTION makes PDO throw an exception when something goes wrong, so errors are never silently ignored.
  • FETCH_ASSOC returns each row as an associative array keyed by column name.
  • Keep the connection details in a config file outside the public web folder, and create the connection once per request.

Reading rows

For a query with no user input, query() is fine:

PHP
$rows = $pdo->query('SELECT name, price FROM products ORDER BY price')->fetchAll();
foreach ($rows as $row) {
    echo $row['name'];
}
  • fetchAll() returns every row as an array.
  • fetch() returns one row at a time, or false when there are no more, which suits very large results.
  • fetchColumn() returns the first column of the next row, handy for SELECT COUNT(*).

Prepared statements

As soon as any value comes from a visitor, use a prepared statement. Write the SQL with placeholders, then pass the values separately:

PHP
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = :cat AND price <= :max');
$stmt->execute(['cat' => $_GET['cat'] ?? '', 'max' => 20]);
$products = $stmt->fetchAll();

The database receives the SQL and the values separately, so a value can never change what the query does, no matter what characters it contains. Named placeholders like :cat are easiest to read; question marks (?) with a list of values also work. Placeholders can only stand in for values, not for table or column names.

Inserting, updating and transactions

The same pattern works for changing data:

PHP
$stmt = $pdo->prepare('INSERT INTO customers (name, city) VALUES (?, ?)');
$stmt->execute([$name, $city]);
$newId = $pdo->lastInsertId();

rowCount() tells you how many rows an UPDATE or DELETE changed. When several changes must succeed or fail together, such as saving an order and reducing stock, use a transaction: $pdo->beginTransaction(), then $pdo->commit(), or $pdo->rollBack() in a catch block if anything fails.

PHP PDO Syntax

PDO patterns

$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$rows = $pdo->query('SELECT ...')->fetchAll(PDO::FETCH_ASSOC);

$stmt = $pdo->prepare('SELECT * FROM t WHERE id = :id');
$stmt->execute(['id' => $id]);
$row = $stmt->fetch();

$stmt = $pdo->prepare('INSERT INTO t (a, b) VALUES (?, ?)');
$stmt->execute([$a, $b]);
$id = $pdo->lastInsertId();

PHP PDO Summary

  • PDO connects PHP to MySQL, SQLite, PostgreSQL and more with the same code.
  • Set ERRMODE_EXCEPTION and FETCH_ASSOC when connecting.
  • Use query() only for fixed SQL; use prepare() and execute() whenever values come from users.
  • fetchAll, fetch and fetchColumn read results; lastInsertId and rowCount report changes.
  • Use transactions for related changes, and escape database values when printing them.

PHP PDO Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Listing products from a SQLite database

The database holds the shop's six products from the SQL course.

PHP
<?php
$pdo = new PDO('sqlite:' . __DIR__ . '/shop.db', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$count = $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn();
$rows = $pdo->query('SELECT name, price FROM products WHERE stock > 0 ORDER BY price DESC LIMIT 3')->fetchAll();

echo "$count products, top 3 in stock:\n";
foreach ($rows as $r) {
    printf("%-12s £%.2f\n", htmlspecialchars($r['name']), $r['price']);
}
Output
6 products, top 3 in stock:
Backpack     £34.99
Desk lamp    £24.50
Water bottle £12.00
Line-by-line explanation
  1. Lines 2 to 5Connect to an SQLite file with exceptions and associative rows turned on.
  2. Line 7fetchColumn returns the single number from COUNT(*).
  3. Line 8A fixed query with no user input, so query() is fine.
  4. Line 12Escape names before printing them in HTML.
Example 2

A search with a prepared statement

The visitor searched for lamp with a maximum price of 30.

PHP
<?php
$search = trim($_GET['q'] ?? '');
$max = filter_var($_GET['max'] ?? 1000, FILTER_VALIDATE_FLOAT) ?: 1000;

$stmt = $pdo->prepare(
    'SELECT name, price FROM products WHERE name LIKE :q AND price <= :max ORDER BY price'
);
$stmt->execute(['q' => "%$search%", 'max' => $max]);

$results = $stmt->fetchAll();
foreach ($results as $p) {
    echo htmlspecialchars($p['name']) . " ({$p['price']})\n";
}
echo count($results) . " result(s)";
Output
Desk lamp (24.5)
1 result(s)
Line-by-line explanation
  1. Lines 5 to 7The SQL contains placeholders instead of the visitor's text.
  2. Line 8The values are passed separately. The % signs are added to the value, not to the SQL.
  3. Lines 10 to 13Results are escaped before output.
Example 3

Saving an order in a transaction

PHP
<?php
function placeOrder(PDO $pdo, int $customerId, int $productId, int $qty): int {
    $pdo->beginTransaction();
    try {
        $stmt = $pdo->prepare('UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?');
        $stmt->execute([$qty, $productId, $qty]);
        if ($stmt->rowCount() === 0) {
            throw new RuntimeException('Not enough stock');
        }
        $stmt = $pdo->prepare('INSERT INTO orders (customer_id, product_id, quantity, order_date) VALUES (?, ?, ?, ?)');
        $stmt->execute([$customerId, $productId, $qty, date('Y-m-d')]);
        $id = (int) $pdo->lastInsertId();
        $pdo->commit();
        return $id;
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

echo "Order " . placeOrder($pdo, 1, 4, 2) . " placed\n";
try {
    placeOrder($pdo, 2, 5, 1);
} catch (RuntimeException $e) {
    echo "Failed: " . $e->getMessage();
}
Output
Order 7 placed
Failed: Not enough stock
Line-by-line explanation
  1. Line 3Start a transaction so both changes succeed or fail together.
  2. Lines 5 to 9Only reduce stock if there's enough; rowCount tells us whether a row changed.
  3. Line 12The new order's id, assigned by the database.
  4. Lines 15 to 17Any error undoes everything, then passes the error on.
  5. Line 22The lunch box (product 5) has no stock, so the second order fails and nothing is saved.

Common PHP PDO Mistakes

Building SQL from user input

Don't
$sql = "SELECT * FROM users WHERE email = '{$_POST['email']}'";
$user = $pdo->query($sql)->fetch();
Do
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$_POST['email'] ?? '']);
$user = $stmt->fetch();

Silent errors

Don't
$pdo = new PDO($dsn, $user, $pass);   // older PHP: errors may be ignored silently
Do
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

A placeholder for a column name

Don't
$stmt = $pdo->prepare('SELECT * FROM products ORDER BY :column');
Do
$allowed = ['name', 'price'];
$column = in_array($_GET['sort'] ?? '', $allowed) ? $_GET['sort'] : 'name';
$stmt = $pdo->query("SELECT * FROM products ORDER BY $column");

Placeholders only work for values. Choose identifiers from an allow-list.

PHP PDO in Real Projects

PDO in real applications

WordPress, Laravel, Symfony and almost every PHP application talk to databases through PDO or a library built on it. Laravel's Eloquent and Doctrine's ORM add classes and nicer syntax on top, but they still send prepared statements underneath, which is why understanding PDO helps you debug any PHP project.

PHP PDO FAQ

Should I use PDO or mysqli?

PDO works with many databases through one API, supports named placeholders and is the more common choice for new code. mysqli only works with MySQL and MariaDB but exposes some MySQL-specific features. Both are safe with prepared statements; see the mysqli versus PDO lesson for a comparison.

How do I count the rows returned by a SELECT?

Run a SELECT COUNT(*) query and read it with fetchColumn(), or count the array from fetchAll() when you need the rows anyway. Don't rely on rowCount() after a SELECT: for many drivers it's only meaningful for INSERT, UPDATE and DELETE.

How do I use an array of values in an IN clause?

Build one placeholder per value and pass the array to execute: $marks = implode(',', array_fill(0, count($ids), '?')); then $pdo->prepare("SELECT * FROM products WHERE id IN ($marks)")->execute($ids);. Check that the array isn't empty first, because IN () is a syntax error.

Do I need to close a PDO connection?

No. PHP closes the connection automatically when the script ends. Set $pdo = null; only in long-running scripts, such as workers, that should release the connection early. Persistent connections exist but need care, as the connection practices lesson explains.

What are emulated prepared statements?

By default, the MySQL PDO driver may build the final query on the PHP side instead of sending a real prepared statement to the server. It's still safe from injection when used correctly, but it can change how types are returned. Many projects set PDO::ATTR_EMULATE_PREPARES => false to use the database's native prepared statements.

PHP PDO Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the code that runs a prepared statement.

$stmt = $pdo->('SELECT * FROM products WHERE id = ?');
$stmt->([$id]);
Multiple choiceeasy

What does PDO stand for?

Multiple choicemedium

Why do prepared statements prevent SQL injection?

Predict the outputmedium

After $stmt = $pdo->prepare('UPDATE products SET price = 9 WHERE category = ?'); $stmt->execute(['Kitchen']);, what does $stmt->rowCount() return for the example shop?

Multiple choicehard

Which option makes PDO throw exceptions on errors?

PHP PDO Quiz

5 questions to check what you learned.

  1. Which method returns all result rows at once?

    • fetchAll()
    • fetch()
    • rows()
    • getAll()

  2. When is $pdo->query() acceptable?

    • Always
    • For fixed SQL that contains no user input
    • For INSERT statements with form data
    • Never

  3. How do you get the id of a row you just inserted?

    • $stmt->id()
    • SELECT MAX(id)
    • $pdo->lastInsertId()
    • $stmt->rowCount()

  4. Can a placeholder replace a table or column name?

    • Yes
    • Only in MySQL
    • Only with named placeholders
    • No, placeholders only stand in for values

  5. What should a catch block do inside a PDO transaction?

    • rollBack(), then report or rethrow the error
    • commit()
    • Nothing
    • Close the connection only

Related PHP topics

11Form ValidationValidate required fields, emails, numbers and lengths on the server, collect error messages and protect forms with CSRF tokens.18Classes & OOPWrite object-oriented PHP: classes, properties and methods, constructor promotion, visibility, static members, inheritance and interfaces.20Exceptions & ErrorsHandle problems gracefully with try, catch and finally, throw your own exceptions and configure error reporting safely.
Esc
↑↓ to navigateEnter to open