Page 430 - Beginning PHP 5.3

Part III: Using PHP in Practice
The next two static methods, getMembers() and getMember() , form the core of the class.
getMembers() expects three arguments: $startRow , $numRows , and $order . It returns a list of
$numRows records from the members table, ordered by $order and starting from $startRow . The
records are returned as an array of Member objects.

After calling the DataObject class ’ s connect() method to create a database connection, the method
sets up the SQL statement to retrieve the rows:
$sql = “SELECT SQL_CALC_FOUND_ROWS * FROM “ . TBL_MEMBERS . “ ORDER BY

$order LIMIT :startRow, :numRows”;
Much of this statement will be familiar to you. It ’ s selecting all columns ( * ) from the members table,
ordered by the $order variable, and limited to the range specified by the $startRow and $numRows
variables. However, there are a couple of concepts here that you haven ’ t seen before.
SQL_CALC_FOUND_ROWS is a special MySQL keyword that computes the total number of rows that
would be returned by the query, assuming the LIMIT clause wasn ’ t applied. So if the query would return
20 records, but the LIMIT clause limits the returned rows to five, SQL_CALC_FOUND_ROWS returns a value
of 20. This is useful because it enables you to display the records over several pages, as you see in a
moment.

:startRow and :numRows are called placeholders or parameter markers . They serve two purposes. First of
all, they let you prepare — that is, get MySQL to parse — a query once, then run it multiple times with

different values. If you need to run the same query many times using different input values — when
inserting many rows of data, for instance — prepared statements can really speed up execution.
Secondly, they reduce the risk of so - called SQL injection attacks. For example, an alternative to using
placeholders might be to write:
$sql = “SELECT SQL_CALC_FOUND_ROWS * FROM “ . TBL_MEMBERS . “ ORDER BY

$order LIMIT $startRow, $numRows”;
However, imagine that, due to insufficient checking of user input, a malicious user managed to set
$numRows to “ 1; DELETE FROM members . This would run the query as intended, but it would also
”
run the second statement, which would delete all records from your members table!

When you use placeholders, you pass data to the query via PDO (as you see shortly), not directly into
your query string. This allows PDO to check the passed data to ensure that it only contains what it ’ s
supposed to contain (integers in this case).

The next block of code is inside a try ... catch construct. This ensures that any PDO exceptions that
occur during the query are caught by the method. First, the method calls the prepare() method of the
PDO object, passing in the SQL string just created:

$st = $conn- > prepare( $sql );
This sets up the query in the MySQL engine, and returns a PDOStatement object to work with (stored in
the $st variable). Next, the two :startRow and :numRow placeholders you created earlier are populated
with the actual data from the $startRow and $numRow variables:

$st- > bindValue( “:startRow”, $startRow, PDO::PARAM_INT );

$st- > bindValue( “:numRows”, $numRows, PDO::PARAM_INT );
392

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