Recipe 10.4. Querying an SQL Database


10.4.1. Problem

You want to retrieve some data from your database.

10.4.2. Solution

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.

Sending a query to the database

<?php $st = $db->query('SELECT symbol,planet FROM zodiac'); foreach ($st->fetchAll() as $row) {     print "{$row['symbol']} goes with {$row['planet']} <br/>\n"; } ?>

10.4.3. Discussion

The query( ) method returns a PDOStatement object. Its fetchAll( ) provides a concise way to do something with each row returned from a query.

The fetch( ) method returns a row at a time, as shown in Example 10-10.

Fetching individual rows

<?php $rows = $db->query('SELECT symbol,planet FROM zodiac'); $firstRow = $rows->fetch(); print "The first results are that {$row['symbol']} goes with {$row['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 Ram, $firstRow[1] is Mars, $firstRow['symbol'] is Ram, and $firstRow['planet'] is Mars.

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 16-2.

Table 10-1. PDO::FETCH_* constants

Constant

Row format

PDO::FETCH_BOTH

Array with both numeric and string (column names) keys. The default format.

PDO::FETCH_NUM

Array with numeric keys.

PDO::FETCH_ASSOC

Array with string (column names) keys.

PDO::FETCH_OBJ

Object of class stdClass with column names as property names.

PDO::FETCH_LAZY

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 16-2, there are additional 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 7-34 shows how this works.

Binding result columns

<?php $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 7-34, 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 PDOStatement with a method that reports the average length of all the column values and then sets up a query to use it.

Extending PDOStatement

<?php 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.

10.4.4. See Also

Recipe 10.5 for other ways to retrieve data; Recipe 10.6 for modifying an SQL database; Recipe 10.7 for repeating queries efficiently; documentation on PDO at http://www.php.net/PDO .




PHP Cookbook, 2nd Edition
PHP Cookbook: Solutions and Examples for PHP Programmers
ISBN: 0596101015
EAN: 2147483647
Year: 2006
Pages: 445

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net