Connecting PHP to MySQL With PDO

Outside a framework, PHP has two APIs for talking to MySQL: the older mysqli extension, and PDO (PHP Data Objects). PDO is the better default for new code — it supports multiple database drivers behind one consistent API, and its prepared-statement handling is harder to misuse than mysqli's.

1. Opening a connection

$dsn = 'mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4';

try {
    $pdo = new PDO($dsn, 'db_user', 'db_password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION matters more than it looks — without it, PDO fails silently on a bad query by default, returning false instead of throwing, which makes real errors easy to miss during development.

2. Prepared statements — never string-concatenate user input

// Wrong — vulnerable to SQL injection
$email = $_POST['email'];
$result = $pdo->query("SELECT * FROM users WHERE email = '$email'");

// Correct — the value is bound, never interpolated into the SQL string
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $_POST['email']]);
$user = $stmt->fetch();

A prepared statement sends the query structure and the parameter values to MySQL as two separate things — the database never interprets the parameter as part of the SQL syntax, which is what makes SQL injection through that value impossible, regardless of what the value actually contains.

3. Fetching results

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

$users = $stmt->fetchAll(); // array of associative arrays, given the default fetch mode set above

foreach ($users as $user) {
    echo $user['name'];
}

4. Inserting and getting the new ID

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->execute(['name' => 'Jane Doe', 'email' => 'jane@example.com']);

$newId = $pdo->lastInsertId();

5. Transactions

For operations where multiple statements must all succeed or all fail together — transferring a balance between two accounts, for example — wrap them in a transaction so a failure partway through doesn't leave the database in a half-updated state:

try {
    $pdo->beginTransaction();

    $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :from')
        ->execute(['amount' => 100, 'from' => 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :to')
        ->execute(['amount' => 100, 'to' => 2]);

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