PHP Database Access Querying an SQL Database

PHP Database Access

Querying an SQL Database

Problem

You want to retrieve some data from your database.

Solution

Example  Sending a query to the database

         $st = $db->query('SELECT symbol,planet FROM zodiac');

         foreach ($st->fetchAll() as $row) {

                  print "{$row['symbol']} goes with {$row['planet']} <br/>\n";

         }

Discussion

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

Example  Fetching individual rows

         $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 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.

Table  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.

_______________________________________________________________

Constant                        Row format                                                                                          

PDO::FETCH_LAZY      Objectof 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, 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. Shows how this works.

Example  Binding result columns

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

Example  Extending PDOStatement

         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 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_CLASSmust extend PDOStatement.