Summary - CRUD operations

PHP course · Advanced · Lesson 21/92

Build a user management app with INSERT, SELECT, UPDATE and DELETE.

Summary - CRUD operations

CRUD stands for the four core data operations: Create (INSERT), Read (SELECT), Update (UPDATE) and Delete (DELETE). With PDO you run each one safely using a prepared statement.

Wrapping the operations in functions reduces repetition: createUser, findUser, updateUser and deleteUser reuse the same connection.

After insert lastInsertId() and after update or delete rowCount() verify the result; transactions keep related operations atomic.

Exercise User management mini app

In the skeleton below, write four functions: createUser($pdo, $name, $email) runs INSERT and returns lastInsertId(); findUser($pdo, $id) runs SELECT and returns fetch(PDO::FETCH_ASSOC); updateUser($pdo, $id, $name) runs UPDATE and returns rowCount(); deleteUser($pdo, $id) runs DELETE and returns rowCount(). Keep the test code below the skeleton untouched.

Show example solution

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)");

function createUser($pdo, $name, $email) {
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->execute([$name, $email]);
    return $pdo->lastInsertId();
}

function findUser($pdo, $id) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function updateUser($pdo, $id, $name) {
    $stmt = $pdo->prepare("UPDATE users SET name = ? WHERE id = ?");
    $stmt->execute([$name, $id]);
    return $stmt->rowCount();
}

function deleteUser($pdo, $id) {
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->rowCount();
}

$id = createUser($pdo, 'Alex', 'alex@example.com');
updateUser($pdo, $id, 'Alex Y.');
$user = findUser($pdo, $id);
echo $user['name'] . "\n";
deleteUser($pdo, $id);
$stmt = $pdo->query("SELECT COUNT(*) FROM users");
echo $stmt->fetchColumn() . "\n";

Test yourself

Match the CRUD letter to the SQL command

Create

INSERT
Read

SELECT
Update

UPDATE
Delete

DELETE

Complete the findUser function

function findUser($pdo, $id) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->____(PDO::FETCH_ASSOC);
}

Show answer

function findUser($pdo, $id) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

Summary - CRUD operations