Previous Section  < Day Day Up >  Next Section

7.8 Retrieving Data from the Database

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)mp3s@db.example.com/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)mp3s@db.example.com/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)mp3s@db.example.com/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: SELECT

The SELECT command retrieves data from the database. Example 7-34 shows the syntax of SELECT.

Example 7-34. Retrieving data
SELECT column1[, column2, column3, ...] FROM tablename

The SELECT query in Example 7-35 retrieves the dish_name and price columns for all the rows in the dishes table.

Example 7-35. Retrieving dish_name and price
SELECT dish_name, price FROM dishes

As a shortcut, you can use * instead of a list of columns. This retrieves all columns from the table. The SELECT query in Example 7-36 retrieves everything from the dishes table.

Example 7-36. Using * in a SELECT query
SELECT * FROM dishes

To restrict a SELECT statement so that it matches only certain rows, add a WHERE clause to it. Only rows that meet the tests listed in the WHERE clause are returned by the SELECT statement. The WHERE clause goes after the table name, as shown in Example 7-37.

Example 7-37. Restricting the rows returned by SELECT
SELECT column1[, column2, column3, ...] FROM tablename

       WHERE where_clause

The where_clause part of the query is a logical expression that describes which rows you want to retrieve. Example 7-38 shows some SELECT queries with WHERE clauses.

Example 7-38. Retrieving certain dishes
; Dishes with price greater than 5.00

SELECT dish_name, price FROM dishes WHERE price > 5.00



; Dishes whose name exactly matches "Walnut Bun"

SELECT price FROM dishes WHERE dish_name = 'Walnut Bun'



; Dishes with price more than 5.00 but less than or equal to 10.00

SELECT dish_name FROM dishes WHERE price > 5.00 AND price <= 10.00



; Dishes with price more than 5.00 but less than or equal to 10.00,

; or dishes whose name exactly matches "Walnut Bun" (at any price) 

SELECT dish_name, price FROM dishes WHERE (price > 5.00 AND price <= 10.00)

       OR dish_name = 'Walnut Bun'

Table 7-3 lists some operators that you can use in a WHERE clause.

Table 7-3. SQL WHERE clause operators

Operator

Description

=

Equal to (like = = in PHP)

<>

Not equal to (like != in PHP)

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

AND

Logical AND (like && in PHP)

OR

Logical OR (like || in PHP)

( )

Grouping



When you are only expecting one row to be returned from a query, use getRow( ). It executes a SELECT query and returns the values for just one row. Example 7-39 uses getRow( ) to display the least expensive item in the dishes table. The ORDER BY and LIMIT parts of the query in Example 7-39 are explained in the sidebar SQL Lesson: ORDER BY and LIMIT.

Example 7-39. Retrieving a row with getRow( )
require 'DB.php';

$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');

$cheapest_dish_info = $db->getRow('SELECT dish_name, price

                                   FROM dishes ORDER BY price LIMIT 1');

print "$cheapest_dish_info[0], $cheapest_dish_info[1]";

Example 7-39 prints:

Walnut Bun, 1.00

When you want only one column from one row, use getOne( ). It executes a SELECT query and returns a single value: the first column from the first row returned. Example 7-40 uses getOne( ) to find the name of the least expensive dish.

Example 7-40. Retrieving a value with getOne( )
require 'DB.php';

$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');

$cheapest_dish = $db->getOne('SELECT dish_name, price

                              FROM dishes ORDER BY price LIMIT 1');

print "The cheapest dish is $cheapest_dish";

Example 7-40 prints:

The cheapest dish is Walnut Bun

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梛ust 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.


    Previous Section  < Day Day Up >  Next Section