Inserting, Updating and Deleting
Run INSERT, UPDATE and DELETE through a prepared statement, then read rowCount and lastInsertId to find out what happened.
$stmt = $pdo->prepare('UPDATE users SET name = :name WHERE id = :id'); $stmt->execute(['name' => $name, 'id' => $id]); $changed = $stmt->rowCount();A write goes out the same way a read does: prepare() the SQL with placeholders, then execute() with the values. There is no separate write API and no reason to build the SQL by hand. INSERT adds a row, UPDATE changes rows that match a condition, DELETE removes them.
Finding out what happened
execute() returns true on success, but with exceptions enabled a failure throws instead, so the return value is rarely worth checking. Two methods tell you more. rowCount() returns how many rows the statement affected. lastInsertId() returns the id the database generated for the row you just inserted, as a string, which you need the moment one record points at another.
Shorthand and longhand
$pdo->exec($sql) runs a statement straight away and returns the affected row count. It is the short form of prepare() followed by execute() with no values, and it is only safe for SQL that contains no values at all, such as a DELETE whose condition is a database function. Anything with a value in it goes through prepare().
The WHERE clause
An UPDATE or DELETE without a WHERE clause is valid SQL and applies to every row in the table. There is no confirmation and no undo outside a transaction. Write the WHERE first and the SET second, and test a new write statement as a SELECT before you run it.
Example
When to use it
- Save a submitted registration form as a new row.
- Create an order and its line items, linking them with the generated id.
- Delete a user's expired sessions in a nightly cleanup script.
- Report 'nothing changed' to someone who saved a form without editing it.
More examples
Insert, then use the new id
This is the commonest write in any application: create a parent record, then rows that point at it. lastInsertId() must be read before you run anything else on the connection, because it reflects the most recent insert. On PostgreSQL, pass the sequence name, as in $pdo->lastInsertId('orders_id_seq'). These two inserts belong in a transaction, which the next lesson covers.
<?php
declare(strict_types=1);
$stmt = $pdo->prepare(
'INSERT INTO orders (customer_id, total_cents) VALUES (:customer_id, :total_cents)'
);
$stmt->execute([
'customer_id' => $customerId,
'total_cents' => 4999,
]);
// A string, because the id may be larger than a PHP int on some platforms.
$orderId = (int) $pdo->lastInsertId();
$line = $pdo->prepare(
'INSERT INTO order_lines (order_id, sku, quantity) VALUES (:order_id, :sku, :quantity)'
);
$line->execute([
'order_id' => $orderId,
'sku' => 'A-1',
'quantity' => 2,
]);
rowCount, and when it answers your question
rowCount() answers 'how many rows did this change', and that is all it answers. When the question is really 'does this record exist', ask with a SELECT, because an UPDATE that sets a column to the value it already holds reports zero changed rows on MySQL. This is the shape to use in any controller that edits a record it did not just load.
<?php
declare(strict_types=1);
$stmt = $pdo->prepare('DELETE FROM sessions WHERE user_id = :user_id');
$stmt->execute(['user_id' => $userId]);
// For DELETE, this is the number of rows removed. A reliable answer.
echo $stmt->rowCount(), " sessions ended\n";
// For UPDATE on MySQL it counts changed rows, so check existence separately.
$exists = $pdo->prepare('SELECT 1 FROM users WHERE id = :id');
$exists->execute(['id' => $userId]);
if ($exists->fetchColumn() === false) {
http_response_code(404);
exit('No such user.');
}
$update = $pdo->prepare('UPDATE users SET name = :name WHERE id = :id');
$update->execute(['name' => $name, 'id' => $userId]);
exec() next to prepare()
exec() and prepare()/execute() are the same operation written short and long, and the only legitimate use of the short form is SQL with no values anywhere in it: cleanup statements, and schema changes in a migration script. The moment a variable appears, the line has to become a prepared statement. The commented-out line is the bug this whole stage exists to prevent.
<?php
declare(strict_types=1);
// exec() is for statements with no values in them. It returns the row count.
$removed = $pdo->exec('DELETE FROM sessions WHERE expires_at < NOW()');
// The same statement the long way. Identical result.
$stmt = $pdo->prepare('DELETE FROM sessions WHERE expires_at < NOW()');
$stmt->execute();
$removed = $stmt->rowCount();
// Never this. $days came from somewhere, so it needs a placeholder.
// $pdo->exec("DELETE FROM sessions WHERE age_days > {$days}");
$stmt = $pdo->prepare('DELETE FROM sessions WHERE age_days > :days');
$stmt->execute(['days' => $days]);
Discussion