July 27, 2011, 4:24 p.m.
posted by stackme
Querying an SQL 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 Figure.
Sending a query to the database
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
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 is Ram, $firstRow 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.
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
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.
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.
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 .