UPDATE Queries

UPDATE changes existing rows.

1
2
3
4
5
6
7

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->prepare("UPDATE products SET stock = :stock WHERE id = :id");
$statement->execute(["stock" => 40, "id" => 3]);

echo $statement->rowCount() . " row updated";

1

1 row updated

rowCount() tells you how many rows the statement changed.

Never forget WHERE

Without WHERE, every row is updated.

1
2

UPDATE products SET stock = 0;            -- sets ALL products to 0
UPDATE products SET stock = 0 WHERE id = 3;   -- only product 3

Always double-check the WHERE clause before you run an update. In important code, add a LIMIT 1 when you expect one row (MySQL supports UPDATE ... LIMIT).

Update several columns

1
2
3
4
5
6
7
8
9

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->prepare("UPDATE users SET name = :name, role = :role WHERE id = :id");
$statement->execute(["name" => "Benjamin", "role" => "editor", "id" => 2]);

$row = $pdo->query("SELECT name, role FROM users WHERE id = 2")->fetch();

echo $row["name"] . " is now " . $row["role"];

1

Benjamin is now editor

Calculate from the current value

Do the maths inside the database. It is safe even if two requests run at the same time.

1
2
3
4
5
6
7

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->prepare("UPDATE products SET stock = stock - :qty WHERE id = :id AND stock >= :needed");
$statement->execute(["qty" => 3, "id" => 5, "needed" => 3]);

echo $statement->rowCount() === 1 ? "Order accepted" : "Not enough stock";

1

Order accepted

The extra condition stock >= :needed means the update only happens if enough is in stock, and rowCount() tells you whether it did. Reading the stock into PHP, subtracting and writing it back would be unsafe, because another visitor could buy the same item in between.

rowCount() can be 0 even when the id exists

In MySQL, rowCount() returns the number of rows that actually changed. If you “update” a row to the values it already has, the count is 0. Do not treat 0 as proof that the row is missing. Check with a SELECT if the difference matters.

The update form flow

1. The page loads the row with SELECT and shows a form filled with the values.
2. The visitor edits and submits (POST).
3. PHP validates and runs UPDATE ... WHERE id = :id.
4. PHP redirects (Post/Redirect/Get) with a success message.

1
2
3
4
5
6
7
8
9

<?php
$id = filter_var($_POST["id"] ?? null, FILTER_VALIDATE_INT);

if ($id === false) {
    exit("Invalid id");
}

$statement = $pdo->prepare("UPDATE products SET name = :name, price = :price WHERE id = :id");
$statement->execute(["name" => $name, "price" => $price, "id" => $id]);

Check permissions

An id from a form can be changed by the visitor. Make sure the logged-in user is allowed to change that row (for example WHERE id = :id AND user_id = :me). Otherwise one user can edit another user’s data by changing the number. This weakness is called IDOR (insecure direct object reference).

1
2
3

<?php
$statement = $pdo->prepare("UPDATE posts SET title = :title WHERE id = :id AND user_id = :user");
$statement->execute(["title" => $title, "id" => $id, "user" => $_SESSION["user_id"]]);