Prepared Queries
A prepared statement is a database statement with placeholders where values will go. The statement is built once, and its values are supplied separately when it runs.
Insert with a prepared statement
The prepare method builds an INSERT statement with two question mark placeholders. The execute method runs that statement twice, and the COUNT query then counts the rows while fetchColumn reads that single count value.
<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE books (title TEXT, price REAL)");
$stmt = $db->prepare("INSERT INTO books (title, price) VALUES (?, ?)");
$stmt->execute(["Dune", 12]);
$stmt->execute(["Emma", 7]);
$count = $db->query("SELECT COUNT(*) FROM books")->fetchColumn();
echo $count . "\n";A prepared SELECT by price
The prepared SELECT here holds one placeholder for a price ceiling. The execute method supplies the ceiling 10, and fetch reads the matching rows one at a time.
<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE books (title TEXT, price REAL)");
$db->exec("INSERT INTO books (title, price) VALUES ('Dune', 12)");
$db->exec("INSERT INTO books (title, price) VALUES ('Emma', 7)");
$db->exec("INSERT INTO books (title, price) VALUES ('Hamlet', 5)");
$stmt = $db->prepare("SELECT title FROM books WHERE price <= ?");
$stmt->execute([10]);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row["title"] . "\n";
}A title with a quote reads back whole
The title Reader's Choice holds an apostrophe. The INSERT and the SELECT both send the title as a parameter, so the title comes back whole.
<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE books (title TEXT, price REAL)");
$insert = $db->prepare("INSERT INTO books (title, price) VALUES (?, ?)");
$insert->execute(["Reader's Choice", 9]);
$select = $db->prepare("SELECT title FROM books WHERE title = ?");
$select->execute(["Reader's Choice"]);
$row = $select->fetch(PDO::FETCH_ASSOC);
echo $row["title"] . "\n";Example
<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE books (title TEXT, author TEXT)");
$insert = $db->prepare("INSERT INTO books (title, author) VALUES (?, ?)");
$insert->execute(["Emma", "Jane Austen"]);
$insert->execute(["Persuasion", "Jane Austen"]);
$insert->execute(["Dune", "Frank Herbert"]);
$select = $db->prepare("SELECT title FROM books WHERE author = ?");
$select->execute(["Jane Austen"]);
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
echo $row["title"] . "\n";
}Key points
- A placeholder is a mark in a statement that stands for a value supplied later.
- The prepare method builds a prepared statement and hands it back.
- The execute method runs a prepared statement with its values in an array.
- A question mark placeholder takes the value at its position in the execute array.
- A value built into a query string can break the statement when the value holds a quote.
- A value sent as a parameter is treated as data alone, so a quote inside it stays safe.
In this example, a prepared INSERT adds three books, and the same statement runs three times with new values each time. A prepared SELECT then takes the author name as its one parameter. The two matching titles print in the order the rows were added.
Note
One prepared statement can run many times with different values.
Try it Yourself
Edit the code, then press Run.
Running is simulated
Exercise
A book title holds an apostrophe. What happens when the title is sent as a prepared statement parameter?