Transcript PHP Bible – Chapter 16: Displaying Queries in Tables

PHP Bible
Chapter 16: Displaying Queries In Tables
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
1
 Wiley and the book authors, 2002
Summary



Mapping database tables to HTML tables
Reusable functions for table display using SELECT
Displaying complex relational table data
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
2
 Wiley and the book authors, 2002
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
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
3
 Wiley and the book authors, 2002
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
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
4
 Wiley and the book authors, 2002
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:
1.
2.
3.
4.
5.
6.
7.
8.

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
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
5
 Wiley and the book authors, 2002
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>');
}
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
6
 Wiley and the book authors, 2002
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);
?>
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
7
 Wiley and the book authors, 2002
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
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
8
 Wiley and the book authors, 2002