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