How to update a row in a database
Short answer
Run UPDATE as a prepared statement:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)');
$pdo->exec("INSERT INTO products (title, price) VALUES ('Tea', 250), ('Coffee', 400), ('Juice', 180), ('Green tea', 300)");
$statement = $pdo->prepare('UPDATE products SET price = ? WHERE id = ?');
$statement->execute([270, 1]);
echo $pdo->query('SELECT price FROM products WHERE id = 1')->fetchColumn();Output:
270How many rows changed
rowCount() returns the number of affected rows. Zero means no record matches the condition:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)');
$pdo->exec("INSERT INTO products (title, price) VALUES ('Tea', 250), ('Coffee', 400), ('Juice', 180), ('Green tea', 300)");
$statement = $pdo->prepare('UPDATE products SET price = price + 10 WHERE price < ?');
$statement->execute([300]);
echo $statement->rowCount();Output:
2Do not forget WHERE
An UPDATE products SET price = 0 query without a condition changes every row of the table.
Functions used in this recipe
- PDO — A database connection.
- PDOStatement — A prepared statement and its result.
- intval — Converts a value to an integer.