CRUD Operations with MySQL

CRUD — Create, Read, Update, Delete — covers the four basic operations every data-driven application needs, all achievable safely through PDO's prepared statements.

Inserting and Reading Data

Prepared statements separate the SQL structure from the data being inserted, which PDO binds safely — this is the standard defence against SQL injection.

// Create
$stmt = $pdo->prepare("INSERT INTO students (name, email) VALUES (:name, :email)");
$stmt->execute(['name' => 'Ayanda', 'email' => 'ayanda@example.com']);

// Read
$stmt = $pdo->prepare("SELECT * FROM students WHERE id = :id");
$stmt->execute(['id' => 1]);
$student = $stmt->fetch(PDO::FETCH_ASSOC);

Updating and Deleting

The same prepared-statement pattern applies to updates and deletes, always binding user-provided values rather than concatenating them into the query string.

// Update
$stmt = $pdo->prepare("UPDATE students SET email = :email WHERE id = :id");
$stmt->execute(['email' => 'new@example.com', 'id' => 1]);

// Delete
$stmt = $pdo->prepare("DELETE FROM students WHERE id = :id");
$stmt->execute(['id' => 1]);

Common Mistakes

• Concatenating user input directly into an SQL string instead of using prepared statement placeholders, opening the door to SQL injection.
• Forgetting to call execute() with the bound values after preparing a statement.
• Not checking the number of affected rows after an UPDATE or DELETE to confirm the operation actually matched a row.
• Reusing the same variable names for bound parameters as existing PHP variables, causing confusion about which value is which.