PHP PDO Select, Insert, Update, Delete
Once connected with PDO, you can run SQL queries to select, insert, update, and delete data. The query() method is useful for simple SELECT statements, while prepare() and execute() are preferred for queries involving user input.
Fetching results is done with methods like fetch() for a single row or fetchAll() for every matching row, typically returned as associative arrays for easy access by column name.
$pdo->query("SELECT ...");
$pdo->exec("INSERT ...");Selecting data
$pdo->query("SELECT * FROM users")->fetchAll(PDO::FETCH_ASSOC) runs a query and returns all matching rows as an array of associative arrays.
Insert, update and delete
INSERT, UPDATE and DELETE statements are executed with $pdo->exec($sql) or through prepared statements, and typically return the number of affected rows.
<?php
$stmt = $pdo->query("SELECT name FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
echo $user["name"] . " ";
}
?>Amy Ben fetchAll() with FETCH_ASSOC returns each row as an associative array keyed by column name.
<?php
$rows = $pdo->exec("UPDATE users SET active = 1 WHERE id = 1");
echo "$rows row(s) updated";
?>1 row(s) updatedexec() runs an UPDATE statement and returns the number of rows that were changed.
Key points
- query() is used for simple SELECT statements without user input.
- exec() runs INSERT, UPDATE or DELETE statements and returns affected row count.
- fetch() retrieves a single row; fetchAll() retrieves all matching rows.
- PDO::FETCH_ASSOC returns rows as associative arrays keyed by column name.