PHP and MySQL with PDO

PHP and MySQL with PDO

PDO is PHP's database access layer. It supports multiple database engines and, crucially, prepared statements that stop SQL injection.

Connecting

<?php
$pdo = new PDO(
  'mysql:host=localhost;dbname=shop',
  'root',
  ''
);

The DSN names the engine, host, and database. You can also set an error mode so failures throw exceptions.

Prepared statements

Question marks stand for values, and execute binds them safely:

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();

Because values never touch the SQL string, an attacker's input becomes data instead of code.

Inserting with named placeholders

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->execute([':name' => $name, ':email' => $email]);

Named placeholders make statements easier to read. lastInsertId returns the new record id.

Fetching results

$rows = $pdo->query('SELECT * FROM products')->fetchAll();
foreach ($rows as $row) {
  echo $row['name'];
}

fetch returns one row as an associative array, and fetchAll returns every row at once.

Errors and transactions

Set ERRMODE_EXCEPTION so problems surface as catchable exceptions. Wrap multi-step writes in beginTransaction and commit so either all changes apply or none do.

Key Points

• PDO connects PHP to several databases.
• Prepared statements prevent SQL injection.
• Named placeholders improve readability.
• fetch and fetchAll turn results into arrays.
• Transactions keep multi-step writes atomic.