Building CRUD Operations with PHP and PDO
CRUD stands for Create, Read, Update, and Delete. These four operations form the foundation of many database-backed applications.
Connect with PDO
Create a PDO connection using credentials loaded from protected configuration. Enable exception-based error handling and use utf8mb4.
$pdo = new PDO(
"mysql:host=localhost;dbname=app_db;charset=utf8mb4",
$dbUser,
$dbPassword,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
Create a Record
Use a prepared statement and pass values separately.
$stmt = $pdo->prepare(
"INSERT INTO notes (title, body) VALUES (:title, :body)"
);
$stmt->execute([
"title" => $title,
"body" => $body,
]);
$newId = (int) $pdo->lastInsertId();
Validate values before inserting them. Database constraints should also enforce important rules.
Read Records
Fetch one record with fetch() or a collection with fetchAll().
$stmt = $pdo->prepare(
"SELECT id, title FROM notes WHERE id = :id LIMIT 1"
);
$stmt->execute(["id" => $noteId]);
$note = $stmt->fetch();
For lists, use pagination rather than loading an unbounded number of rows.
Update a Record
Use an UPDATE statement with a WHERE clause and bound values.
$stmt = $pdo->prepare(
"UPDATE notes SET title = :title WHERE id = :id"
);
$stmt->execute([
"title" => $title,
"id" => $noteId,
]);
Always verify that the current user is authorized to update the selected record.
Delete a Record
Delete only the intended row and consider whether a soft-delete or confirmation workflow is more appropriate.
$stmt = $pdo->prepare("DELETE FROM notes WHERE id = :id");
$stmt->execute(["id" => $noteId]);
Security and Reliability
Prepared statements protect parameter values from SQL injection, but they do not replace validation or authorization. Escape output for its destination context, use transactions for multi-step changes, and log failures without exposing database details to visitors.
Conclusion
PDO provides a consistent way to implement CRUD operations. Prepared statements, validation, authorization, and sensible pagination make these operations safer and more maintainable.