Page 431 - Beginning PHP 5.3

Chapter 13: Retrieving Data from MySQL with PHP
The PDOStatement::bindValue() method takes three arguments: the name of the placeholder to
bind, the value to use instead of the placeholder, and the data type of the value ( PDO::PARAM_INT , or
integer, in this case). By specifying the data type, PDO can ensure that the correct type of data is passed
to MySQL. In addition, PDO automatically escapes any quote marks and other special characters in the
data. (Failing to escape special characters is another common cause of SQL injection vulnerabilities.)

Some other common data types that you can use include:

❑ PDO::PARAM_BOOL — A Boolean data type

❑ PDO::PARAM_NULL — The NULL data type
❑ PDO::PARAM_STR — A string data type. (This is the default if you don ’ t specify a type.)

❑ PDO::PARAM_LOB — A LOB data type, such as BLOB or LONGBLOB
Now that the statement has been prepared and the placeholders filled with actual values, it ’ s time to run
the query:
$st- > execute();

The next block of code loops through the record set returned by the query. For each row returned, it
creates a corresponding Member object to hold the row ’ s data, and stores the object in an array:
$members = array();
foreach ( $st- > fetchAll() as $row ) {
$members[] = new Member( $row );
}
PDOStatement::fetchAll() is one of many ways that you can retrieve the result set returned from a
query. fetchAll() grabs the whole result set in one go, and returns it as an array of associative arrays,
where each associative array holds a row of data. Though this is fine for relatively small result sets —
say, less than 100 records — be careful of using fetchAll() with large result sets, because the entire
result set is loaded into your script ’ s memory in one go.

However, in this case fetchAll() is ideal. The script loops through the returned array of rows, passing
each $row associative array into the constructor for the Member class. Remember that the constructor is
actually in the DataObject class, and it expects an associative array of field names and values, which
is exactly what each element of the array returned by fetchAll() contains. The constructor then uses
this associative array to populate the Member object with the data.

Once the array of Member objects has been created, the method runs another query. Remember the SQL_
CALC_FOUND_ROWS keyword in the original query? To extract the calculated total number of rows, you
need to run a second query immediately after the original query:
$st = $conn- > query( “SELECT found_rows() AS totalRows” );
$row = $st- > fetch();
The query calls the MySQL found_rows() function to get the calculated total, and returns the result as
an alias, totalRows . Notice that this is a regular query that uses PDO::query() , rather than a prepared
statement as used by the first query. You don ’ t need to use placeholders because the query doesn ’ t need
to contain any passed - in values; hence there is no need to go to the trouble of creating a prepared
statement.
393

9/21/09 9:12:04 AM
c13.indd 393 9/21/09 9:12:04 AM
c13.indd 393