You want to retrieve some data from your database.
Use PDO::query() to send the SQL query to the database, and then a foreach loop to retrieve each row of the result, as shown in Example 10-9.
Example 10-9. Sending a query to the database
|
1 2 3 4 |
$st = $db->query('SELECT symbol,planet FROM zodiac'); foreach ($st->fetchAll() as $row) { print "{$row['symbol']} goes with {$row['planet']} <br/>\n"; } |
The query() method returns a PDOStatement object. Its fetchAll() method provides a concise way to operate on each row returned from a query.
The fetch() method returns a row at a time, as shown in Example 10-10.
Example 10-10. Fetching individual rows
|
1 2 3 4 |
$rows = $db->query('SELECT symbol,planet FROM zodiac ORDER BY planet'); $firstRow = $rows->fetch(); print "The first results are that {$firstRow['symbol']} goes with ↵ {$firstRow['planet']}"; |
Each call to fetch() returns the next row in the result set. When there are no more rows available, fetch() returns false.
By default, fetch() returns an array containing each column in the result set row twice —once with an index corresponding to the column name and once with a numerical index.
That means that the $firstRow variable in Example 10-10 has four elements: $firstRow[0] is Archer, $firstRow[1] is Jupiter, $firstRow[‘symbol’] is Archer, and $firstRow[‘planet’] is Jupiter.
To have fetch() return rows in a different format, pass a PDO::FETCH_* constant to query() as a second argument.
You can also pass one of the constants as the first argument to fetch(). The allowable constants and what they make fetch() return are listed in Table 10-1.
Table 10-1. PDO::FETCH_* constants
| Constant | Row format |
| PDO::FETCH_BOTH
PDO::FETCH_NUM PDO::FETCH_ASSOC PDO::FETCH_OBJ PDO::FETCH_LAZY
|
Array with both numeric and string (column names) keys. The default format.
Array with numeric keys. Array with string (column names) keys. Object of class stdClass with column names as property names. Object of class PDORow with column names as property names. The properties aren’t populated until accessed, so this is a good choice if your result row has a lot of columns. Note that if you store the returned object and fetch another row, the stored object is updated with values from the new row. |
In addition to the choices in Table 10-1, there are other ways a row can be structured.
These other ways require more than just passing a constant to query() or fetch(), however.
In combination with bindColumn(), the PDO::FETCH_BOUND fetch mode lets you set up variables whose values get refreshed each time fetch() is called. Example 10-11 shows how this works.
Example 10-11. Binding result columns
|
1 2 3 4 5 6 7 8 |
$row = $db->query('SELECT symbol,planet FROM zodiac',PDO::FETCH_BOUND); // Put the value of the 'symbol' column in $symbol $row->bindColumn('symbol', $symbol); // Put the value of the second column ('planet') in $planet $row->bindColumn(2, $planet); while ($row->fetch()) { print "$symbol goes with $planet. <br/>\n"; } |
In Example 10-11, each time fetch() is called, $symbol and $planet are assigned new values. Note that you can use either a column name or number with bindColumn(). Column numbers start at 1.
When used with query(), the PDO::FETCH_INTO and PDO::FETCH_CLASS constants put result rows into specialized objects of particular classes.
To use these modes, first create a class that extends the built-in PDOStatement class. Example 10-12 extends PDOState ment with a method that reports the average length of all the column values and then sets up a query to use it.
Example 10-12. Extending PDOStatement
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
class AvgStatement extends PDOStatement { public function avg() { $sum = 0; $vars = get_object_vars($this); // Remove PDOStatement's built-in 'queryString' variable unset($vars['queryString']); foreach ($vars as $var => $value) { $sum += strlen($value); } return $sum / count($vars); } } $row = new AvgStatement; $results = $db->query('SELECT symbol,planet FROM zodiac',PDO::FETCH_INTO, $row); // Each time fetch() is called, $row is repopulated while ($results->fetch()) { print "$row->symbol belongs to $row->planet (Average: {$row->avg()}) <br/> \n"; } |
In Example 10-12, the second and third arguments to query() tell PDO “each time you fetch a new row, stuff the values into properties of the $row variable.”
Then, inside the while() loop, the properties of $row are available, as well as the newly defined avg()
method.
PDO::FETCH_INTO is useful when you want to keep data around in the same object, such as whether you’re displaying an odd- or even-numbered row, throughout all the calls to fetch().
But when you want a new object for each row, use PDO::FETCH_CLASS. Pass it to query() like PDO::FETCH_INTO, but make the third argument to query() a class name, not an object instance.
The class name you provide with PDO::FETCH_CLASS must extend PDOStatement.
