exec() runs a statement and returns the number of affected rows; use it for UPDATE, DELETE or DDL that contains no outside values. query() runs a fixed SELECT and returns a PDOStatement you can foreach over. prepare() plus execute() is for every statement that contains a value from outside: a form field, a URL parameter, a cookie, even a value read from your own database (Subsection 4.17.4).
<?php
$pdo = require 'db.php';
echo 'exec: ', $pdo->exec('UPDATE products SET stock = stock WHERE id < 4'), " rows changed\n";
foreach ($pdo->query('SELECT sku FROM products WHERE category_id = 5') as $row) {
echo 'query: ', $row['sku'], "\n";
}
$st = $pdo->prepare('SELECT title FROM products WHERE sku = ?');
$st->execute([$_GET['sku'] ?? 'BK-LNX-01']); // outside input goes through prepare()
echo 'prepare: ', $st->fetchColumn(), "\n";Output
exec: 0 rows changed query: AC-MUG-01 query: AC-STK-01 prepare: The Linux Server Handbook
The UPDATE matched three rows but changed none, and MySQL 524 reports changed rows, which is why exec() said 0; Pdo\Mysql::ATTR_FOUND_ROWS switches the count to matched rows. Do not rely on rowCount() after a SELECT: it works on MySQL only because results are buffered. $pdo->quote() escapes a string for the rare spot where no placeholder fits.