Deleting records with DELETE
Learn to delete a record with PDO and a prepared statement.
Deleting a record with DELETE
To delete a record you run a DELETE FROM with a prepared statement. The WHERE clause selects which rows are removed.
For example, DELETE FROM users WHERE id = ? removes only the row with the given ID; the value is passed safely via execute().
Without WHERE, DELETE removes every row in the table; always include the clause.
Counting after delete
To see the effect of a delete, fetch the remaining row count with SELECT COUNT(*) FROM users.
PDO fetchColumn() returns a single-column result directly; this is handy for single-value queries like COUNT.
You can run the query directly with query(); since it takes no parameters, a prepared statement is not needed.
Exercise Delete a user and print the remaining count
In the skeleton below, use prepare and execute to delete user 'Sam'. Then fetch the remaining row count with SELECT COUNT(*) FROM users and fetchColumn(), and print it.
Show example solution
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$pdo->exec("INSERT INTO users (name) VALUES ('Sam')");
$pdo->exec("INSERT INTO users (name) VALUES ('Dana')");
$stmt = $pdo->prepare("DELETE FROM users WHERE name = ?");
$stmt->execute(['Sam']);
$stmt = $pdo->query("SELECT COUNT(*) FROM users");
echo $stmt->fetchColumn() . "\n";Test yourself
-
Complete the DELETE clause
$stmt = $pdo->prepare("DELETE ____ users WHERE id = ?");Show answer
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?"); -
Match DELETE parts
- DELETE
- Removes rows
- FROM
- Table source
- WHERE
- Which rows
- execute()
- Runs the statement
-
Which is the safest way to delete by ID?
- DELETE FROM users
- DELETE users WHERE id = ?
- DELETE id FROM users
- DELETE FROM users WHERE id = ?
Show answer
DELETE FROM users WHERE id = ? — DELETE FROM users WHERE id = ? is the correct and safe prepared statement.