10.4.1. ProblemYou want to retrieve some data from your database. 10.4.2. SolutionUse 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
10.4.3. DiscussionThe 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
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.
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
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
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 AlsoRecipe 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 . |