Learn › PHP › Databases & PDO

Databases & PDO

⏱ 22 min read read

What is PDO?

PDO (PHP Data Objects) is a consistent interface for working with
databases. It supports MySQL, PostgreSQL, SQLite, and more. Always use
PDO over old mysql\_ functions.

Connecting to MySQL:

$dsn = 'mysql:host=localhost;dbname=mydb;charset=utf8mb4';

$pdo = new PDO($dsn, 'username', 'password', [

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

PDO::ATTR_EMULATE_PREPARES => false,

]);

NEVER put user input directly in SQL strings --- SQL injection risk!

BAD: "SELECT * FROM users WHERE name = '$name'"

GOOD: Use prepared statements with placeholders.

ALWAYS set PDO::ERRMODE_EXCEPTION to catch database errors.

Use PDO::FETCH_ASSOC to get rows as associative arrays.

Prepared Statements --- The Safe Way:

// Named placeholders (:name)

$stmt = $pdo->prepare('SELECT * FROM users WHERE email =
:email');

$stmt->execute(['email' => $email]);

$user = $stmt->fetch();

// Positional placeholders (?)

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?,
?)');

$stmt->execute([$name, $email]);

$newId = $pdo->lastInsertId();

Fetching Data:

// Fetch one row

$row = $stmt->fetch(); // one row as assoc array

// Fetch all rows

$rows = $stmt->fetchAll(); // all rows as array of arrays

// Fetch single value

$count = $stmt->fetchColumn(); // first column of first row

Transactions:

$pdo->beginTransaction();

try {

$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();

} catch (Exception $e) {

$pdo->rollBack();

throw $e;

}

Code Example

<?php

// Database connection with error handling

function getConnection(): PDO {

$dsn = 'mysql:host=localhost;dbname=school;charset=utf8mb4';

return new PDO($dsn, 'root', '', [

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

PDO::ATTR_EMULATE_PREPARES => false,

]);

}

try {

$pdo = getConnection();

// CREATE table

$pdo->exec('CREATE TABLE IF NOT EXISTS students (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

email VARCHAR(150) UNIQUE NOT NULL,

grade DECIMAL(5,2),

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

)');

// INSERT --- prepared statement prevents SQL injection

$stmt = $pdo->prepare('INSERT INTO students (name, email, grade)
VALUES (:name, :email, :grade)');

$students = [

['name' => 'Alice', 'email' => 'alice@email.com',
'grade' => 92.5],

['name' => 'Bob', 'email' => 'bob@email.com', 'grade'
=> 78.0],

['name' => 'Carol', 'email' => 'carol@email.com',
'grade' => 85.5],

];

foreach ($students as $s) {

$stmt->execute($s);

}

// SELECT all

$all = $pdo->query('SELECT * FROM students ORDER BY grade
DESC')->fetchAll();

foreach ($all as $row) {

echo "{$row['name']}: {$row['grade']}\n";

}

// SELECT with condition

$stmt = $pdo->prepare('SELECT * FROM students WHERE grade >=
:min');

$stmt->execute(['min' => 80]);

$passing = $stmt->fetchAll();

echo count($passing) . " passing students\n";

// UPDATE

$upd = $pdo->prepare('UPDATE students SET grade = :grade WHERE
name = :name');

$upd->execute(['grade' => 95.0, 'name' => 'Alice']);

echo "Updated " . $upd->rowCount() . " row(s)\n";

// DELETE

$del = $pdo->prepare('DELETE FROM students WHERE name = :name');

$del->execute(['name' => 'Bob']);

// COUNT

$count = $pdo->query('SELECT COUNT(*) FROM
students')->fetchColumn();

echo "Remaining students: $count\n";

} catch (PDOException $e) {

echo "Database error: " . $e->getMessage() . "\n";

}

?>

← Working with Forms & HTTP Traits & Advanced OOP →