PHP
Summary - CRUD operations
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);
}
Next lesson: Project: Blog with PDO
