PHP and Databases

Building CRUD Operations with PHP and PDO

Learn the basic create, read, update, and delete workflow using PDO prepared statements and MySQL.

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.