Updating records with UPDATE
Learn to update an existing record with PDO and a prepared statement.
Updating a record with UPDATE
To update an existing record you run an UPDATE with a prepared statement. The SET clause names the columns to change; WHERE selects which rows are affected.
For example, UPDATE users SET role = ? WHERE name = ? changes the role of a specific user; values are passed via execute().
Without WHERE, UPDATE changes every row in the table; forgetting the clause is dangerous.
Verifying after update
To check whether the update took effect, you fetch the same record with SELECT and print the new value.
PDO fetch(PDO::FETCH_ASSOC) returns a single row as an associative array; if no record matches, it returns false.
This pattern is also useful to compare state before and after the update.
Exercise Update the role and verify
In the skeleton below, use prepare and execute to change the role of 'Alex' to 'admin'. Then fetch the role with SELECT and print it.
Show example solution
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, role TEXT)");
$pdo->exec("INSERT INTO users (name, role) VALUES ('Alex', 'editor')");
$stmt = $pdo->prepare("UPDATE users SET role = ? WHERE name = ?");
$stmt->execute(['admin', 'Alex']);
$stmt = $pdo->prepare("SELECT role FROM users WHERE name = ? ORDER BY id");
$stmt->execute(['Alex']);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['role'] . "\n";Test yourself
-
Match UPDATE parts
- UPDATE
- Table name
- SET
- New values
- WHERE
- Which rows
- execute()
- Runs the statement
-
What happens if UPDATE has no WHERE?
- The first row is updated
- The query throws an error
- All rows are updated
- No record changes
Show answer
All rows are updated — Without WHERE, UPDATE changes every row in the table.
-
Complete the UPDATE clause
$stmt = $pdo->prepare("UPDATE users SET role = ? ____ name = ?");Show answer
$stmt = $pdo->prepare("UPDATE users SET role = ? WHERE name = ?");