CRUD Operations

CRUD stands for Create, Read, Update, and Delete: the four basic operations for working with persistent storage. This page covers each operation with practical examples.

For connection setup and PDO fundamentals, see PDO Fundamentals.

---

CREATE (INSERT)

After inserting a record, you can retrieve its auto-generated ID with lastInsertId().

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

$newUserId = $pdo->lastInsertId();
echo "User created with ID: " . $newUserId;

---

READ (SELECT)

Reading data is the most common database operation. Here are the typical patterns.

Fetch Single Record

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([':id' => $userId]);
$user = $stmt->fetch();

if ($user) {
    echo "User: " . $user['name'] . " (" . $user['email'] . ")";
} else {
    echo "User not found";
}

Fetch Multiple Records

$stmt = $pdo->prepare("SELECT * FROM products WHERE category = :category ORDER BY name");
$stmt->execute([':category' => 'electronics']);
$products = $stmt->fetchAll();

foreach ($products as $product) {
    echo $product['name'] . " - $" . $product['price'] . "\n";
}

Count Records

$stmt = $pdo->prepare("SELECT COUNT(*) FROM orders WHERE status = :status");
$stmt->execute([':status' => 'pending']);
$count = $stmt->fetchColumn();
echo "Pending orders: " . $count;

Check If Record Exists

$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email = :email");
$stmt->execute([':email' => $email]);
$exists = $stmt->fetchColumn() > 0;

---

UPDATE

The rowCount() method tells you how many records were modified. Remember to include a WHERE clause to avoid updating the entire table.

$stmt = $pdo->prepare("UPDATE users SET email = :email, age = :age WHERE id = :id");
$stmt->execute([
    ':email' => $newEmail,
    ':age' => $newAge,
    ':id' => $userId
]);

if ($stmt->rowCount() > 0) {
    echo "User updated successfully";
} else {
    echo "No user found or no changes made";
}

Update Multiple Records

$stmt = $pdo->prepare("UPDATE users SET status = :status WHERE last_login < :cutoff");
$stmt->execute([
    ':status' => 'inactive',
    ':cutoff' => '2024-01-01'
]);
echo "Updated " . $stmt->rowCount() . " users";

---

DELETE

Be careful to include a WHERE clause. Without one, you’ll delete every row in the table.

$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute([':id' => $userId]);

if ($stmt->rowCount() > 0) {
    echo "User deleted successfully";
} else {
    echo "User not found";
}

Delete with Multiple Conditions

$stmt = $pdo->prepare("DELETE FROM sessions WHERE user_id = :user_id AND expires < :now");
$stmt->execute([
    ':user_id' => $userId,
    ':now' => time()
]);
echo "Deleted " . $stmt->rowCount() . " expired sessions";

---

Transactions

When multiple operations need to succeed or fail together (like transferring money between accounts), wrap them in a transaction.

try {
    $pdo->beginTransaction();

    // Debit from one account
    $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - :amount WHERE id = :from");
    $stmt->execute([':amount' => $amount, ':from' => $fromAccount]);

    // Credit to another account
    $stmt = $pdo->prepare("UPDATE accounts SET balance = balance + :amount WHERE id = :to");
    $stmt->execute([':amount' => $amount, ':to' => $toAccount]);

    $pdo->commit();
    echo "Transfer completed";

} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transfer failed";
}

---

Quick Reference

Query Patterns

Operation	Pattern
SELECT	prepare() → execute() → fetch()/fetchAll()
INSERT	prepare() → execute() → lastInsertId()
UPDATE/DELETE	prepare() → execute() → rowCount()
Single value	prepare() → execute() → fetchColumn()
Transaction	beginTransaction() → operations → commit()/rollBack()