The query( ) function can also be used to retrieve information from the database. The syntax of query( ) is the same, but what you do with the object that query( ) returns is new. When it successfully completes a SELECT statement, query( ) returns an object that provides access to the retrieved rows. Each time you call the fetchRow( ) function of this object, you get the next row returned from the query. When there are no more rows left, fetchRow( ) returns a false value, making it perfect to use in a while( ) loop. This is shown in Example 7-31.
Example 7-31. Retrieving rows with query( ) and fetchRow( )
require 'DB.php';
$db = DB::connect('mysql://hunter:w) restaurant');
$q = $db->query('SELECT dish_name, price FROM dishes');
while ($row = $q->fetchRow( )) {
print "$row[0], $row[1] \n";
}
Example 7-31 prints:
Walnut Bun, 1.00
Cashew Nuts and White Mushrooms, 4.95
Dried Mulberries, 3.00
Eggplant with Chili Sauce, 6.50
The first time through the while( ) loop, fetchRow( ) returns an array containing Walnut Bun and 1.00. This array is assigned to $row. Since an array with elements in it evaluates to true, the code inside the while( ) loop executes, printing the data from the first row returned by the SELECT query. This happens three more times. On each trip through the while( ) loop, fetchRow( ) returns the next row in the set of rows returned by the SELECT query. When it has no more rows to return, fetchRow( ) returns a value that evaluates to false, and the while( ) loop is done.
To find out the number of rows returned by a SELECT query (without iterating through them all), use the numrows( ) function of the object returned by query( ). Example 7-32 reports how many rows are in the dishes table.
Example 7-32. Counting rows with numrows( )
require 'DB.php';
$db = DB::connect('mysql://hunter:w) restaurant');
$q = $db->query('SELECT dish_name, price FROM dishes');
print 'There are ' . $q->numrows( ) . ' rows in the dishes table.';
With four rows in the table, Example 7-32 prints:
There are 5 rows in the dishes table.
Because sending a SELECT query to the database program and retrieving the results is such a common task, DB provides ways that collapse the call to query( ) and multiple calls to fetchRow( ) into one step. The getAll( ) function executes a SELECT query and returns an array containing all the retrieved rows. Example 7-33 uses getAll( ) to do the same thing as Example 7-31.
Example 7-33. Retrieving rows with getAll( )
require 'DB.php';
$db = DB::connect('mysql:/hunter:w) restaurant');
$rows = $db->getAll('SELECT dish_name, price FROM dishes');
foreach ($rows as $row) {
print "$row[0], $row[1] \n";
}
Example 7-33 prints:
Walnut Bun, 1.00
Cashew Nuts and White Mushrooms, 4.95
Dried Mulberries, 3.00
Eggplant with Chili Sauce, 6.50
SQL Lesson: ORDER BY and LIMIT
As mentioned earlier in this chapter in Section 7.1, rows in a table don't have any inherent order. A database server doesn't have to return rows from a SELECT query in any particular pattern. To force a certain order on the returned rows, add an ORDER BY clause to your SELECT. Example 7-41 returns all the rows in the dishes table ordered by price, lowest to highest.
Example 7-41. Ordering rows returned from a SELECT query
SELECT dish_name FROM dishes ORDER BY price
To order from highest to lowest value, add DESC after the column that the results are ordered by. Example 7-42 returns all the rows in the dishes table ordered by price, highest to lowest.
Example 7-42. Ordering from highest to lowest
SELECT dish_name FROM dishes ORDER BY price DESC
You can specify multiple columns to order by. If two rows have the same value for the first ORDER BY column, they are sorted by the second. The query in Example 7-43 orders rows in dishes by price (highest to lowest). If multiple rows have the same price, then they are ordered alphabetically by name.
Example 7-43. Ordering by multiple columns
SELECT dish_name FROM dishes ORDER BY price DESC, dish_name
Using ORDER BY doesn't change the order of the rows in the table itself (remember, they don't really have any set order) but rearranges the results of the query. This affects only the answer to the query. If you hand someone a menu and ask them to read you the appetizers in alphabetical order, it doesn't affect the printed menu—just the response to your query ("Read me all the appetizers in alphabetical order").
Normally, a SELECT query returns all rows that match the WHERE clause (or all rows in a table if there is no WHERE clause). Sometimes it's helpful to just get a certain number of rows back. You may want to find the lowest priced dish available or just print 10 search results. To restrict the results to a specific number of rows, add a LIMIT clause to the end of the query. Example 7-44 returns the row from dishes with the lowest price.
Example 7-44. Limiting the number of rows returned by SELECT
SELECT * FROM dishes ORDER BY price LIMIT 1
Example 7-45 returns the first (sorted alphabetically by dish name) 10 rows from dishes.
Example 7-45. Still limiting the number of rows returned by SELECT
SELECT dish_name, price FROM dishes ORDER BY dish_name LIMIT 10
In general, you should only use LIMIT in a query that also has ORDER BY. If you leave out ORDER BY, the database program can return rows in any order. So, the "first" row one time a query is executed might not be the "first" row another time the same query is executed.
No comments:
Post a Comment