PHP PDO & CRUD Operations

Intermediate~30 min read

PDO (PHP Data Objects) is the modern, recommended way to work with databases. It supports multiple databases and provides a consistent interface for CRUD operations!

<?php
// PDO & CRUD Operations

// PDO Connection
$host = 'localhost';
$db = 'myapp';
$user = 'root';
$pass = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected with PDO\n";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// CREATE - Insert data
$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['name' => 'John Doe', 'email' => 'john@example.com']);
echo "User created\n";

// READ - Select data
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
    echo $user['name'] . "\n";
}

// UPDATE - Modify data
$sql = "UPDATE users SET email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['email' => 'newemail@example.com', 'id' => 1]);
echo "User updated\n";

// DELETE - Remove data
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 1]);
echo "User deleted\n";

Output

Click Run to execute your code

PDO Connection

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=myapp", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

CRUD Operations

Operation	SQL
Create	`INSERT INTO`
Read	`SELECT`
Update	`UPDATE`
Delete	`DELETE`

Create (Insert)

<?php
$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['name' => 'John', 'email' => '[email protected]']);
?>

Read (Select)

<?php
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
    echo $user['name'];
}
?>

Update

<?php
$sql = "UPDATE users SET email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['email' => '[email protected]', 'id' => 1]);
?>

Delete

<?php
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 1]);
?>

Summary

PDO: Modern database interface
CREATE: INSERT INTO
READ: SELECT
UPDATE: UPDATE SET
DELETE: DELETE FROM

What's Next?

Finally, learn about Prepared Statements & Security - protecting against SQL injection!

Previous MySQL & MySQLi

Next Database Security