Querying an SQL Database






Querying an SQL Database

Problem

You want to retrieve some data from your database.

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

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";
}
?>

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

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

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 Figure, 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. Figure 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 Figure, 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. Figure 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 Figure, 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.

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 .



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows