exec() and query() are PDO object methods: You use them with a database handle and they execute a
statement immediately and return its result. It is also possible to prepare a statement for execution without
executing it immediately. The prepare() method takes an SQL statement as its argument and returns a
PDOStatement statement-handle object. The statement handle has an execute() method that executes
the statement:
$sth = $dbh->prepare ($stmt);
$sth->execute ();
Writing MySQL Scripts with PHP and PDO - 6 -
Following the execute() call, other statement-handle methods provide information about the statement
result:
• For a statement that modifies rows, invoke rowCount() to get the rows-affected count:
$sth = $dbh->prepare ("DELETE FROM animal WHERE category = ’mammal’");
$sth->execute ();
printf ("Number of rows affected: %d\n", $sth->rowCount ());
• For a statement that produces a result set, the fetch() method retrieves them and the column-
Count() method indicates how many columns there are. To determine how many rows there are,
count them as you fetch them. (As mentioned previously, rowCount() returns a row count, but
should be used only for statements that modify rows.)
$sth = $dbh->prepare ("SELECT name, category FROM animal");
$sth->execute ();
printf ("Number of columns in result set: %d\n", $sth->columnCount ());
$count = 0;
while ($row = $sth->fetch ())
{
printf ("Name: %s, Category: %s\n", $row[0], $row[1]);
$count++;
}
printf ("Number of rows in result set: %d\n", $count);
If you are not sure whether a given SQL statement modifies or returns nows, the statement handle itself
enables you to determine the proper mode of processing. See ‘‘Determining the Type of a Statement.’’
As just shown, prepared statements appear to offer no advantage over exec() and query() because
using them introduces an extra step into statement processing. But there are indeed some benefits to them:
• Prepared statements can be parameterized with placeholders that indicate where data values should
appear. You can bind specific values to these placeholders and PDO takes care of any quoting or escaping
issues for values that contain special characters. ‘‘Placeholders and Quoting’’ discusses these topics
further.
• Separating statement preparation from execution can be more efficient for statements to be executed
multiple times because the preparation phase need be done only once. For example, if you need to
insert a bunch of rows, you can prepare an INSERT statement once and then execute it repeatedly, binding
successive row values to it for each execution.
Placeholders and Quoting
A prepared statement can contain placeholders to indicate where data values should appear. After you prepare
the statement, bind specific values to the placeholders (either before or at statement-execution time),
and PDO substitutes the values into the statement before sending it to the database server.
PDO supports named and positional placeholders:
• Anamed placeholder consists of a name preceded by a colon. After you prepare the statement, use
bindValue() to provide a value for each placeholder, and then execute the statement. To insert
another row, bind new values to the placeholders and invoke execute() again:
$sth = $dbh->prepare ("INSERT INTO animal (name, category)
VALUES (:name, :cat)");
$sth->bindValue (":name", "ant");
$sth->bindValue (":cat", "insect");
$sth->execute ();
$sth->bindValue (":name", "snail");
$sth->bindValue (":cat", "gastropod");
$sth->execute ();
As an alternative to binding the data values before calling execute(), you can pass the values
directly to execute() using an array that associates placeholder names with the values:
$sth->execute (array (":name" => "black widow", ":cat" => "spider"));
• Positional placeholders are characters within the statement string. You can bind the values prior to calling
execute(), similar to the previous example, or pass an array of values directly to execute():
$sth = $dbh->prepare ("INSERT INTO animal (name, category)
VALUES (?, ?)");
# use bindValue() to bind data values
$sth->bindValue (1, "ant");
$sth->bindValue (2, "insect");
$sth->execute ();
# pass values directly to execute() as an array
$sth->execute (array ("snail", "gastropod"));
Positional placeholder numbers begin with 1.
An alternative to bindValue() is bindParam(), which adds a level of indirection to value-binding.
Instead of passing a data value as the second argument to bindParam(), pass a variable to associate the
variable with the placeholder. To supply a value for the placeholder, assign a value to the variable:
$sth = $dbh->prepare ("INSERT INTO animal (name, category)
VALUES (?, ?)");
$sth->bindParam (1, $name);
$sth->bindParam (2, $category);
$name = "ant";
$category = "insect";
$sth->execute ();
$name = "snail";
$category = "gastropod";
$sth->execute ();
The preceding examples use INSERT statements, but placeholder techniques are applicable to any type of
statement, such as UPDATE or SELECT.
One of the benefits of using placeholders is that PDO handles any quoting or escaping of special characters
or NULL values. For example, if you bind the string "a’b’c" to a placeholder, PDO inserts
"’a\’b\’c’" into the statement. To bind the SQL NULL value to a placeholder, bind the PHP NULL
value. In this case, PDO inserts the word ‘‘NULL’’ into the statement without surrounding quotes. (Were
quotes to be added, the value inserted into the statement would be the string "’NULL’", which is incorrect.)
PDO also provides a database handle quote() method to which you can pass a string and receive back a
quoted string with special characters escaped. However, I find this method deficient. For example, if you
pass it NULL, it returns an empty string, which if inserted into a statement string does not correspond to the
SQL NULL value. Use quote() with care if you use it.