PHP MySQL With PDO

PHP MySQL access with PDO (PHP Data Objects) is the recommended way to work with databases in plain PHP. PDO supports MySQL, PostgreSQL, SQLite and more with one API, and its prepared statements protect you from SQL injection.

Connecting to MySQL With PDO

Create a PDO object with a DSN string, username and password. Set the error mode to exceptions and default fetch mode to associative arrays.

<?php
$dsn = 'mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4';
$pdo = new PDO($dsn, 'root', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
echo "Connected";

Connected

Prepared Statements Prevent SQL Injection

Never put user input directly into SQL. Use placeholders and pass values separately; the database treats them as data, never as SQL code.

<?php
$email = $_GET['email'] ?? 'sara@example.com';

$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();

echo $user ? "Found {$user['name']}" : "No user";

Found Sara

Insert, Update and Delete

<?php
$stmt = $pdo->prepare('INSERT INTO products (name, price) VALUES (?, ?)');
$stmt->execute(['Green Tea', 4.5]);
echo "New ID: " . $pdo->lastInsertId() . "\n";

$stmt = $pdo->prepare('UPDATE products SET price = ? WHERE id = ?');
$stmt->execute([4.99, 12]);
echo "Updated rows: " . $stmt->rowCount() . "\n";

$pdo->prepare('DELETE FROM products WHERE id = ?')->execute([12]);
echo "Deleted";

New ID: 12
Updated rows: 1
Deleted

Fetching Many Rows

<?php
$stmt = $pdo->query('SELECT name, price FROM products ORDER BY price LIMIT 3');
foreach ($stmt->fetchAll() as $row) {
    printf("%-10s %6.2f\n", $row['name'], $row['price']);
}

Salt         0.99
Rice         3.20
Green Tea    4.50

Transactions

A transaction makes several queries succeed or fail together, for example moving money between accounts.

<?php
try {
    $pdo->beginTransaction();
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([100, 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([100, 2]);
    $pdo->commit();
    echo "Transfer complete";
} catch (Throwable $e) {
    $pdo->rollBack();
    echo "Transfer failed";
}

Transfer complete

Frequently asked questions

Should I use PDO or MySQLi?

PDO is usually the better choice: it supports many databases, has named placeholders and a cleaner API. MySQLi works only with MySQL.

How do prepared statements prevent SQL injection?

The SQL and the values are sent separately, so the database never interprets user input as SQL commands.

Where should I store database passwords?

In environment variables or a config file outside the public web root, never hard-coded in files committed to Git.