Use the query() method to retrieve information from the database. Pass it an SQL query for the database. It returns a PDOStatement object that provides access to the retrieved rows. Each time you call the fetch() method of this object, you get the next row returned from the query. When there are no more rows left, fetch() returns a value that evaluates to false, making it perfect to use in a while() loop. This is shown in Example 8-30.
Example 8-30. Retrieving rows with query() and fetch()
$q = $db->query(‘SELECT dish_name, price FROM dishes’);
while ($row = $q->fetch()) {
print “$row[dish_name], $row[price] \n”;
}
Example 8-30 prints:
Walnut Bun, 1
Cashew Nuts and White Mushrooms, 4.95
Dried Mulberries, 3
Eggplant with Chili Sauce, 6.5
The first time through the while() loop, fetch() returns an array containing Walnut Bun and 1. 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, fetch() returns the next row in the set of rows returned by the SELECT query. When it has no more rows to return, fetch() returns a value that evaluates to false, and the while() loop is done.
By default, fetch() returns an array with both numeric and string keys. The numeric keys, starting at 0, contain each column’s value for the row. The string keys do as well, with key names set to column names. In Example 8-30, the same results could be printed using $row[0] and $row[1].
If you want to find out how many rows a SELECT query has returned, your only foolproof option is to retrieve all the rows and count them. The PDOStatement object provides a rowCount() method, but it doesn’t work with all databases. If you have a small number of rows and you’re going to use them all in your program, use the fetchAll() method to put them into an array without looping, as shown in Example 8-31.
Example 8-31. Retrieving all rows without a loop
$q = $db->query(‘SELECT dish_name, price FROM dishes’);
// $rows will be a four-element array; each element is
// one row of data from the database
$rows = $q->fetchAll();
If you have so many rows that retrieving them all is impractical, ask your database program to count the rows for you with SQL’s COUNT() function. For example, SELECT COUNT(*) FROM dishes returns one row with one column whose value is the number of rows in the entire table.
SQL Lesson: SELECT
The SELECT command retrieves data from the database. Example 8-32 shows the syntax of SELECT.
Example 8-32. Retrieving data
SELECT column1[, column2, column3, …] FROM tablename
The SELECT query in Example 8-33 retrieves the dish_name and price columns for all the rows in the dishes table.
Example 8-33. 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 8-34 retrieves everything from the dishes table.
Example 8-34. 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 8-35.
Example 8-35. 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 8-36 shows some SELECT queries with WHERE clauses.
Example 8-36. 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 8-3 lists some operators that you can use in a WHERE clause.
If you are expecting only one row to be returned from a query, you can chain your fetch() call onto the end of query(). Example 8-37 uses a chained fetch() to display the least expensive item in the dishes table. The ORDER BY and LIMIT parts of the query in Example 8-37 are explained in the sidebar “SQL Lesson: ORDER BY and LIMIT” on page 176.
Example 8-37. Retrieving a row with a chained fetch()
$cheapest_dish_info = $db->query(‘SELECT dish_name, price
FROM dishes ORDER BY price LIMIT 1′)->fetch();
print “$cheapest_dish_info[0], $cheapest_dish_info[1]”;
Example 8-37 prints:
Walnut Bun, 1
SQL Lesson: ORDER BY and LIMIT
As mentioned in “Organizing Data in a Database” on page 156, 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 8-38 returns all the rows in the dishes table ordered by price, lowest to highest.
Example 8-38. Ordering rows returned from a SELECT query
SELECT dish_name FROM dishes ORDER BY price
To order from highest to lowest value, add DESC (descending) after the column that the results are ordered by. Example 8-39 returns all the rows in the dishes table ordered by price, highest to lowest.
Example 8-39. 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 8-40 orders rows in dishes by price (highest to lowest). If multiple rows have the same price, then they are ordered alphabetically by name.
Example 8-40. 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 him 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 8-41 returns the row from dishes with the lowest price.
Example 8-41. Limiting the number of rows returned by
SELECT SELECT * FROM dishes ORDER BY price LIMIT 1
Example 8-42 returns the first (sorted alphabetically by dish name) 10 rows from dishes.
Example 8-42. 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 an ORDER BY clause. 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.
Source: Sklar David (2016), Learning PHP: A Gentle Introduction to the Web’s Most Popular Language, O’Reilly Media; 1st edition.