Section 12.7. Performing a Subquery


12.7. Performing a Subquery

Sometimes you'll want to display the data in a linked table as a list instead of repeating all of the values from the joined table. For example, when listing books, it would look nicer to list authors in one cell of your table. Example 12-12 uses a second query and a loop to accomplish this.

Example 12-12. Displaying the authors in a list

 <?php require_once('db_login.php'); require_once('DB.php'); $connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database"); if (DB::isError($connection)){ die ("Could not connect to the database: <br />". DB::errorMessage($connection)); } // Display the table $query = "SELECT * FROM `books`"; $result = $connection->query($query); if (DB::isError($result)){ die("Could not query the database: <br />".$query." ".DB::errorMessage($result)); } echo '<table border="1">'; echo "<tr><th>Title</th><th>Pages</th><th>Authors</th></tr>"; while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) { echo "<tr><td>"; echo htmlentities($result_row["title"]) . '</td><td>'; echo htmlentities($result_row["pages"]) . '</td><td>'; $author_query = "SELECT * FROM `authors` WHERE `title_id` =".$result_row["title_id"];$author_result = $connection->query($author_query); if (DB::isError($author_result)){ die("Could not query the database: <br />".$author_query." ".DB::errorMessage($author_result)); } $author_count = $author_result->numRows(); if (0 == $author_count) { echo 'none'; } $counter = 0; while ($author_result_row = $author_result->fetchRow(DB_FETCHMODE_ASSOC)) { $counter++; echo htmlentities($author_result_row["author"]); if ($counter != $author_count) { echo ', '; } } echo '</td></tr>'; } echo '</table>'; $connection->disconnect(); ?> 

Define a second query and result set for the authors. For each title, a query of the authors table can retrieve a variable number of authors. Count the result set using the numRows function. To avoid an empty cell, if there were no authors, you display None. Use the $author_count variable again while looping so as not to put a comma after the last author name in the list. The result is this nicer format, shown in Figure 12-14.

Figure 12-14. Authors displayed on a single line


In Chapter 13, we'll talk about storing information in sessions and how to limit access to pages.



Learning PHP and MySQL
Learning PHP and MySQL
ISBN: 0596101104
EAN: 2147483647
Year: N/A
Pages: 135

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net