Connecting to a database with PDO

Why PDO

Websites keep their data in a database: products, customers, orders, posts. PHP talks to databases through PDO (PHP Data Objects), a single interface that works with MySQL, MariaDB, SQLite, PostgreSQL, SQL Server and more. Learn it once and only the connection details change between databases.

You may see older code using the mysqli extension, or even the mysql_ functions that were removed in PHP 7. Use PDO for new code.

This lesson and the next use SQLite, a database that lives in a single file and needs no server, so every example runs anywhere. The MySQL course teaches the SQL itself; everything shown here works the same with MySQL once you change the connection line.

Connecting

A connection needs a DSN (data source name) that names the driver and the database, plus, for server databases, a user name and password. For MySQL:

<?php
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';
$pdo = new PDO($dsn, 'shop_user', getenv('DB_PASSWORD'), [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

Always include charset=utf8mb4 for MySQL, so names in any language and emoji are stored correctly, as explained in Unicode text and the mb_ functions.

For SQLite, the DSN is the path of the database file, and sqlite::memory: creates a temporary database in memory:

<?php
$pdo = new PDO('sqlite::memory:', options: [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

echo "Connected with the " . $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) . " driver\n";
echo "SQLite version: " . ($pdo->query('SELECT sqlite_version()')->fetchColumn() !== '' ? 'available' : 'unknown') . "\n";

Connected with the sqlite driver
SQLite version: available

The two options matter:

• ERRMODE_EXCEPTION makes a failed query throw a PDOException, which you cannot miss. It is the default since PHP 8.0; set it anyway so the intent is clear.
• FETCH_ASSOC returns each row as an associative array keyed by column name, instead of PDO's default, which repeats every value under both a name and a number.

If the connection fails (wrong password, server down), new PDO() throws a PDOException. Its message can contain the host and user name, so log it and show visitors a general error page rather than printing it.

Creating a table and some rows

exec() runs a statement that returns no rows and gives back the number of affected rows. Here is a small version of the MySQL course's sample shop:

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

<?php
$pdo = require __DIR__ . '/db.php';

$pdo->exec('DROP TABLE IF EXISTS products');
$pdo->exec('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
)');

$added = $pdo->exec("INSERT INTO products (name, category, price, stock) VALUES
    ('Mechanical Keyboard', 'Electronics', 89.50, 60),
    ('Noise-Cancelling Headphones', 'Electronics', 199.99, 25),
    ('Ceramic Mug Set', 'Home', 18.75, 120),
    ('Yoga Mat', 'Sports', 24.99, 80),
    ('Puzzle 1000 Pieces', 'Toys', 19.99, 0)");

echo "Added $added products\n";

Added 5 products

The connection lives in db.php, which returns the PDO object, so every page can get it with one line. In MySQL, the column types would be INT AUTO_INCREMENT, VARCHAR(100), DECIMAL(8,2) and so on, as in the data types lesson.

Reading rows

query() runs a SQL statement and returns a PDOStatement, from which you fetch the rows:

<?php
$pdo = require __DIR__ . '/db.php';

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

$count = $pdo->query('SELECT COUNT(*) FROM products WHERE stock > 0')->fetchColumn();
echo "$count products in stock\n";

$cheapest = $pdo->query('SELECT name, price FROM products ORDER BY price LIMIT 1')->fetch();
print_r($cheapest);

Noise-Cancelling Headphones   199.99   25
Mechanical Keyboard            89.50   60
Yoga Mat                       24.99   80
Puzzle 1000 Pieces             19.99    0
Ceramic Mug Set                18.75  120
4 products in stock
Array
(
    [name] => Ceramic Mug Set
    [price] => 18.75
)

<?php
$pdo = require __DIR__ . '/db.php';

print_r($pdo->query('SELECT name FROM products WHERE category = \'Electronics\'')->fetchAll(PDO::FETCH_COLUMN));
print_r($pdo->query('SELECT category, COUNT(*) FROM products GROUP BY category')->fetchAll(PDO::FETCH_KEY_PAIR));

Array
(
    [0] => Mechanical Keyboard
    [1] => Noise-Cancelling Headphones
)
Array
(
    [Electronics] => 2
    [Home] => 1
    [Sports] => 1
    [Toys] => 1
)

For large results, loop over the statement with foreach ($pdo->query($sql) as $row) instead of fetchAll(), so rows are fetched one at a time.

Never put input into query()

Everything in this lesson used fixed SQL, written entirely by you. The moment a value comes from a visitor (a search term, an ID in the URL, a form field), it must not be glued into the SQL string. That is how SQL injection happens. The next lesson shows the only safe way: prepared statements.

Keep credentials out of code

The database password is a secret. Do not write it in a file that is inside the web root, or in a repository others can see. Common approaches:

• An environment variable, read with getenv('DB_PASSWORD'), set in the server's configuration.
• A .env file outside the web root, loaded by a library such as vlucas/phpdotenv from Namespaces, autoloading and Composer. Add .env to .gitignore.
• A config file that returns an array, stored outside the web root, as in include, require and splitting code into files.

Also give the application its own database user with only the privileges it needs (usually SELECT, INSERT, UPDATE and DELETE on one database), never root. The MySQL lesson on users and privileges shows how.

Common mistakes

• Leaving out charset=utf8mb4 in a MySQL DSN, which corrupts non-English text.
• Printing the PDOException message to visitors, which can reveal connection details.
• Using the root user for a web application.
• Building SQL with user input in query() or exec().

Exercise

Using the products table above, print the total stock value (price times stock) for each category, highest first, with two decimals. Do it in SQL with GROUP BY and fetch the result with FETCH_KEY_PAIR.

<?php
$pdo = require __DIR__ . '/db.php';

$values = $pdo->query('SELECT category, SUM(price * stock) AS value FROM products GROUP BY category ORDER BY value DESC')
    ->fetchAll(PDO::FETCH_KEY_PAIR);

foreach ($values as $category => $value) {
    printf("%-12s %10.2f\n", $category, $value);
}

Electronics    10369.75
Home            2250.00
Sports          1999.20
Toys               0.00