PHP —

Adding a second or subsequent query

Often one query isn't enough to gather all the information required for a report or component in a web database application. The panel is a good example: it is difficult to formulate a single query that can retrieve the wine details (wine_name, year, and description), the winery_name, the inventory data (cost and case_cost), and the varieties (from the wine_variety and grape_variety tables).

It is possible to write a single query, but the query needs post-processing to remove duplicate information before presentation. A natural join of wine, winery, inventory, wine_variety, and grape_variety produces one row per variety of each wine. So, for example, a Cabernet Merlot variety wine is two rows in the output, one row for Cabernet and one row for Merlot. The post-processing involves consolidating the two rows into one HTML <table> row for presentation by using an if statement to check that all other values are identical.

In many cases, more than one query is issued to produce a consolidated result. In the case of the panel, the existing query is used to get most of the information (all the data from wine, winery, and inventory). The second query is nested inside the first; that is, for each row retrieved from the result set of the first query, you run the new query to get the varieties. The result is that the script runs four queries: one to retrieve the three wines, and three queries to get their varieties.

Let's return to Example 4-10. The first query has not changed and still returns one row per inventory of each of the most recently added wines that has a written review. For each wine, the script produces a heading showing the year, winery_name, and wine_name.

It is after this query is run and the year, winery_name, and wine_name output that the new functionality of an additional query begins. In this example, a function, showVarieties( ), is called. This function runs a query to find the varieties of a particular wine with a wine_id value that matches the parameter $wineID:

$query = "SELECT gv.variety
          FROM grape_variety gv,
               wine_variety wv, wine w
          WHERE w.wine_id = wv.wine_id
          AND wv.variety_id = gv.variety_id
          AND w.wine_id = $wineID
          ORDER BY wv.id";

For example, the query identifies that the first-listed 1999 Binns Hill Vineyard Morfooney with wine_id=191 is a Cabernet Sauvignon. The results are ordered by wine_variety.id so that, as in previous examples, a Cabernet Merlot can be distinguished from a Merlot Cabernet.

The subsequent processing of the second query follows a similar pattern to the first. A mysql_query( ) retrieves all result rows with mysql_fetch_array( ) and prints out the only attribute retrieved, $row["variety"], the grape variety of the wine. The connection isn't closed because it's needed, later to find the next wine's varieties.

This multiple-query approach is common and is used throughout the winestore; the approach is used in the panel to produce order receipts for presentation and email confirmation, and in many of the stock and customer reports.