Databases with PDO

Almost every PHP application stores its data in a database. PDO (PHP Data Objects) is PHP's standard database interface: the same code works with MySQL/MariaDB, PostgreSQL, SQLite, SQL Server and others, with only the connection string changing. This lesson connects to a database, creates a table, and runs queries.

Connecting

Create a PDO object with a DSN (data source name) describing the database, plus a username and password for server databases. The examples here use SQLite, which stores the whole database in a single file and needs no server — perfect for learning:

PHP 
<?php

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

echo "Connected using the ", $pdo->getAttribute(PDO::ATTR_DRIVER_NAME), " driver\n";
Output
Connected using the sqlite driver

For MySQL or PostgreSQL only the DSN and credentials change:

PHP 
<?php

$mysql = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'shop_user', 'secret', $options);
$pgsql = new PDO('pgsql:host=localhost;dbname=shop', 'shop_user', 'secret', $options);

Two options are worth setting on every connection: ERRMODE_EXCEPTION makes failed queries throw a PDOException (the default since PHP 8, but be explicit), and FETCH_ASSOC returns rows as associative arrays. Keep credentials out of your code — read them from environment variables or a config file that isn't committed to version control.

Creating a table and inserting rows

exec() runs a statement that doesn't return rows and gives back the number of affected rows. (Inserting values that come from users needs prepared statements, covered in the next lesson — the values here are fixed.)

PHP 
<?php

$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);

$pdo->exec(<<<SQL
    CREATE TABLE products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        category TEXT NOT NULL,
        price REAL NOT NULL,
        stock INTEGER NOT NULL DEFAULT 0
    )
    SQL);

$count = $pdo->exec(<<<SQL
    INSERT INTO products (name, category, price, stock) VALUES
        ('Desk lamp', 'Lighting', 34.50, 12),
        ('Standing desk', 'Furniture', 399.00, 3),
        ('Office chair', 'Furniture', 189.00, 0),
        ('LED strip', 'Lighting', 19.99, 40),
        ('Bookshelf', 'Furniture', 129.00, 7)
    SQL);

echo "Inserted $count rows; last id ", $pdo->lastInsertId(), "\n";
Output
Inserted 5 rows; last id 5

sqlite::memory: creates a temporary database that lives only as long as the script — ideal for examples and tests.

Querying

query() runs a SELECT and returns a PDOStatement. Loop over it directly, or fetch rows with fetch(), fetchAll() and fetchColumn():

PHP setup.php
<?php

$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL, stock INTEGER)");
$pdo->exec("INSERT INTO products (name, category, price, stock) VALUES
    ('Desk lamp', 'Lighting', 34.50, 12), ('Standing desk', 'Furniture', 399.00, 3),
    ('Office chair', 'Furniture', 189.00, 0), ('LED strip', 'Lighting', 19.99, 40),
    ('Bookshelf', 'Furniture', 129.00, 7)");

return $pdo;
PHP main.php
<?php

$pdo = require __DIR__ . '/setup.php';

foreach ($pdo->query('SELECT name, price FROM products ORDER BY price DESC') as $row) {
    printf("%-14s %7.2f\n", $row['name'], $row['price']);
}

$first = $pdo->query('SELECT * FROM products WHERE stock = 0')->fetch();
echo "Out of stock: {$first['name']}\n";

echo "Products: ", $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn(), "\n";

$names = $pdo->query("SELECT name FROM products WHERE category = 'Lighting'")->fetchAll(PDO::FETCH_COLUMN);
echo "Lighting: ", implode(', ', $names), "\n";
Output
Standing desk   399.00
Office chair    189.00
Bookshelf       129.00
Desk lamp        34.50
LED strip        19.99
Out of stock: Office chair
Products: 5
Lighting: Desk lamp, LED strip

setup.php builds the sample database and returns the connection — an included file can return a value, which require passes back.

Fetch modes

PHP main.php
<?php

$pdo = require __DIR__ . '/setup.php';

$byCategory = $pdo->query('SELECT category, name FROM products ORDER BY name')
    ->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);
print_r($byCategory);

$priceById = $pdo->query('SELECT id, price FROM products')->fetchAll(PDO::FETCH_KEY_PAIR);
echo "Product 2 costs {$priceById[2]}\n";

final class Product
{
    public int $id;
    public string $name;
    public float $price;

    public function label(): string { return sprintf('%s (%.2f)', $this->name, $this->price); }
}

$products = $pdo->query('SELECT id, name, price FROM products LIMIT 2')->fetchAll(PDO::FETCH_CLASS, Product::class);
echo $products[1]->label(), "\n";
Output
Array
(
    [Furniture] => Array
        (
            [0] => Bookshelf
            [1] => Office chair
            [2] => Standing desk
        )

    [Lighting] => Array
        (
            [0] => Desk lamp
            [1] => LED strip
        )

)
Product 2 costs 399
Standing desk (399.00)

FETCH_GROUP groups rows by the first column, FETCH_KEY_PAIR builds a key–value array from two columns, and FETCH_CLASS creates objects. One caution: with LIMIT but no ORDER BY, which rows come back is up to the database — add ORDER BY whenever order matters.

Errors

With exceptions enabled, a bad query throws a PDOException describing the problem:

PHP main.php
<?php

$pdo = require __DIR__ . '/setup.php';

try {
    $pdo->query('SELECT nmae FROM products');
} catch (PDOException $e) {
    echo "Query failed: ", $e->getMessage(), "\n";
}
Output
Query failed: SQLSTATE[HY000]: General error: 1 no such column: nmae

Log the full message, but don't show raw database errors to visitors: they reveal table and column names that help attackers.

Updating and deleting

PHP main.php
<?php

$pdo = require __DIR__ . '/setup.php';

$changed = $pdo->exec("UPDATE products SET price = price * 0.9 WHERE category = 'Furniture'");
echo "$changed products discounted\n";

$deleted = $pdo->exec("DELETE FROM products WHERE stock = 0");
echo "$deleted product removed\n";

echo "Furniture total: ", round($pdo->query("SELECT SUM(price) FROM products WHERE category = 'Furniture'")->fetchColumn(), 2), "\n";
Output
3 products discounted
1 product removed
Furniture total: 475.2

Which database?

  • SQLite — a file, no server. Great for learning, tests, small sites, desktop and command-line tools.
  • MySQL / MariaDB — the classic PHP pairing, available on almost every host.
  • PostgreSQL — powerful and standards-compliant; popular for larger applications.

Our SQL course teaches the query language itself in depth; this course focuses on using it from PHP.

Try it yourself

Create a books table in an in-memory database, add a few books, and list them grouped by author.

Try it yourself · main.php
Edit the code, copy it into main.php, then run it with php main.php (PHP 8.5). How to install PHP
Output
Frank Herbert: 2 books, first in 1965
Jane Austen: 2 books, first in 1815
PreviousDates and Times