PHP Bible

1. PHP Bible Chapter 20: Displaying Queries In Tables

2. Summary • Mapping database tables to HTML tables • Reusable functions for table display using SELECT • Displaying complex relational table data

3. Displaying queries in tables • Much of the point of PHP is to help you translate between a back-end database and its front-end presentation on the Web • Data can be viewed, added, removed, and tweaked as a result of your Web user's actions • The 2 big productivity points from this chapter are: • Reuse functions in simple cases. The problem of database table display shows up over and over in database-enabled site design. If the display is not complicated, you should be able to throw the same simple function at the problem rather than reinventing the wheel with each PHP page you write • Choose between techniques in complex cases. You may find yourself wanting to pull out a complex combination of information from different tables (which, of course, is part of the point of using a relational database to begin with). You may not be able to map this into a simple reusable function, but there aren't that many novel solutions either – get to know the alternatives, and you can decide how to trade off efficiency, readability, and your own effort

4. HTML tables and database tables • One-to-one mapping • HTML tables are really constructed out of rows (<TR>), and columns have no independent existence – each row has some number of table data items (<TD>) which will produce a nice rectangular array only if there are the same number of TDs for every TR within a TABLE • By contrast, fields (aka columns) in database tables are the more primary entity – defining a table means defining the fields, and then you can add as many rows as you like • The simplest case of display is where the structure of a database table or query does correspond to the structure of the HTML table we want to display – the database entity has m columns and n rows, and we'd like to display an m-by-n rectangular grid in the user's browser window, with all the cells filled in appropriately

5. HTML tables and database tables (cont.) • Example: a single-table displayer • So let's write a simple translator that queries the database for the contents of a single table and displays the results on screen. Here's the top-down outline of how the code will get the job done: • Establish a database connection (mysql_connect & mysql_select_db) • Construct a query to send to the database (SELECT) • Send the query and hold on to the result id (mysql_query) • Start outputting an HTML table (<TABLE>) • Loop through the database result rows, printing an HTML table row tag set (<TR>) at the beginning and ending of each loop • On the first execution of the loop, if desired, print out the field names • In each row, retrieve the successive fields and display them wrapped in a table data cell (<TD>) • Close the HTML table (</TABLE>) • We'd like to wrap all the preceding steps into a handy function that we can use whenever we want to

6. HTML tables and database tables (example) <?php include ('/home/php/db_vars.inc'); function display_db_table($table,$db_res,$criteria='',$field_headings=true) { $sql = 'SELECT * FROM '.$table.' '.$criteria; $result_id = mysql_query($sql,$db_res) or die ('ERROR executing query'); print('<TABLE BORDER="1">'); if ($field_headings) $heading_printed = false; else $heading_printed = true; while ($row = mysql_fetch_assoc($result_id)) { if (!$heading_printed) { print ('<TR>'); foreach ($row as $field_name => $val) print ('<TH>'.$field_name.'</TH>'); print ('</TR>'); $heading_printed = true; } print ('<TR>'); foreach ($row as $val) print ('<TD>'.$val.'</TD>'); print ('</TR>'); } print ('</TABLE>'); }

7. HTML tables and database tables (example) $db_resource = mysql_connect($hostname,$username,$password); mysql_select_db($db_name,$db_resource); if (isset($_GET['category'])) { $category_num = $_GET['category']; $sql = 'SELECT description FROM categories WHERE (category_id = '.$category_num.')'; $result_id = mysql_query($sql,$db_resource); $row = mysql_fetch_row($result_id); $category = $row[0]; $criteria = 'WHERE (category='.$category_num.')'; } else { $category = 'All Products'; $criteria = ''; } ?> <HTML> <HEAD><TITLE>Available products in <?php print($category)?></TITLE></HEAD> <BODY> <?php print ('<H1>'.$category.'</H1>'); display_db_table('catalog',$db_resource,$criteria); ?>

8. HTML tables and database tables (cont.) • Some things to notice about the preceding script: • Although the script refers to specific database tables, the display_db_table() function itself is general. You could put the function definition in an include file and use it anywhere you want • The first thing the script does is load an include file that contains the variable assignments for the database name, username, password, and server name • In the function, we use a while loop for printing rows, and a foreach loop to print the individual fields from each row • The main while loop reflects a very common idiom, which exploits the fact that the value of a PHP assignment statement is the condition. $row will either contain an array of the record returned from the SELECT statement or the value FALSE • The main body of the function shows a relationship between the catalog table and the categories table where the catalog.category field is a foreign key "pointing to" the categories.category_id field • The main body also looks for a category GET variable which could be entered into a URI in an HTML hyperlink (<A>) or typed into the address line