The subject of this section is a case study that uses the concepts we have discussed so far in this chapter. We show how to develop page browsing tools to display results over several pages and permit users to move between these pages. We develop this code as a generic, reusable module. A modified version of the code is used in the winestore, and the winestore browsing code is included in Chapter 13.
The aim of our case study is to show how to display large result sets in pages. Each page should be able to be displayed efficiently and viewed without using the web browser’s vertical scroll bar. We also aim to make the component intuitive to use, allow direct access to any page in the results, and allow pages to be navigated using previous and next hypertext links.
We develop the module step-by-step. We begin by developing support for multiple results pages, and the previous and next links. Later in this section, we add functionality to display page numbers as links that permit direct access to a chosen page. The output of the final version of the module when it is used to browse winestore regions is shown in Figure 5-8.
In this section, as a generic page-based browser is developed, we
retire the special-purpose displayWinesList( )
function completed in Example 5-6.
It’s replaced with a new, generic, multipurpose
function browse( )
. However, before we discuss
how this is done, we describe what we need to achieve.
The following features are required when a user browses the wines in a region:
Only one page of wineries is shown at a time. When the user runs the query, only the first 20 rows of results are shown.
As in Figure 5-8, an embedded Next link displays that allows the user to move to the next page of rows. If the user is accessing the first page, the Next link runs a query that shows the second page of results; that is, rows 21 to 40.
When the user reaches the last page of results—which usually has less than 20 rows—the Next link is hidden.
An embedded Previous link is shown that moves backward through the pages.
The Previous link is hidden when the first page is displayed.
This can be further improved by adding page numbers to each page that allow direct access to other pages without repeatedly clicking on the previous or next links. We discuss this functionality later in this section.
We
show how the Previous and Next links are created with PHP in the
browse( )
function later, but
let’s return for a moment to the main body of the
browsing script. Example 5-9 shows a script that
uses the new generic browse( )
function to show
the wines made in a region. The main segment populates several new
variables that are parameters to the browse( )
function:
$pageHeader
A header for the results pages. In this case, the header is a text
string Wines of
, followed by the name of the
region being displayed; in the example, this can create the
grammatically odd Wines of All
, but fixing this is
outside the scope of this discussion.
$browseString
Part of the URL that is requested when the Previous and Next links
are clicked. The value of $browseString
is
appended immediately after the ?
in the URL and
duplicates the variables and values passed through from the
<form>
displayed to the user. In Example 5-9, $browseString
forms a
variable and value pair such as
regionName=Margaret%20River
. The PHP library
function rawurlencode( )
can encode spaces and other special characters in the URL.
$header
A 2D array that contains the HTML column headers and the names of the
attributes to be displayed in these columns. The columns are numbered
from left to right, so $header[0]
is the
information for the first column. We use associative access to the
second element for readability:
$header[0]["header"]
is the text that displays at
the top of the first HTML column in the
<table>
, while
$header[0]["attrib"]
is the name of the query
attribute in the result set displayed in the first column.
Each column should have both a header
and an
attrib
. The header
should be
human-readable text, while the attrib
is the
attribute name from the SELECT
clause of the SQL
query.
The browse(
)
function takes
these three variables—$pageHeader
,
$browseString
, and
$header
—as parameters. The current
$scriptName
is also passed and can construct URLs
for embedded links. The other parameters are the database
$connection
, and the $offset
in
the result set of the first row on the page that is displayed. The
value of $offset
is initially zero after running a
query and, because it isn’t part of the
<form>
, it’s initialized in
Example 5-9 to zero when not set. In this example,
we show only the modified section of the main component of the script
for preparing a query. The function browse( )
,
shown in Example 5-10, is called in this fragment to
provide generic browsing.
Example 5-9. Adding browsing functionality to the winestore database
// Untaint the user data $regionName = clean($regionName, 30); $scriptName = "example.5-9.php"; // Is there any user data? if (empty($regionName)) { // No, so show the <form> ?> <form action="<?=$scriptName;?>" method="GET"> <br>Enter a region to browse : <input type="text" name="regionName" value="All"> (type All to see all regions) <br> <input type="submit" value="Show wines"> </form> <br><a href="EB0596000413_2.html">Home</a> <?php } // if user data else { // Yes, there is user data so show the results // Connect to the DBMS if (!($connection = @ mysql_connect($hostName, $username, $password))) showerror( ); if (!mysql_select_db($databaseName, $connection)) showerror( ); // Set $offset to zero if not previously set if (empty($offset)) $offset = 0; // Build the query $query = "SELECT w.wine_id, w.wine_name, w.description, w.type, w.year, wry.winery_name FROM winery wry, region r, wine w WHERE wry.region_id = r.region_id AND w.winery_id = wry.winery_id"; // Add the regionName if the user has provided it if ($regionName != "All") $query .= " AND r.region_name = "$regionName""; // Add a sort on the end of the query $query .= " ORDER by w.wine_name"; // Initialize the browse( ) function parameters // Query prefix for the next/previous links $browseString = "regionName=" . rawurlencode($regionName); // Page header for the browse screen $pageHeader = "Wines of " . $regionName; // HTML <TABLE> column headers $header[0]["header"] = "Wine ID"; $header[1]["header"] = "Wine Name"; $header[2]["header"] = "Wine Type"; $header[3]["header"] = "Year"; $header[4]["header"] = "Winery"; $header[5]["header"] = "Description"; // Query attributes to display in <TABLE> columns $header[0]["attrib"] = "wine_id"; $header[1]["attrib"] = "wine_name"; $header[2]["attrib"] = "type"; $header[3]["attrib"] = "year"; $header[4]["attrib"] = "winery_name"; $header[5]["attrib"] = "description"; // Call generic browsing code to browse query browse($scriptName, $connection, $browseString, $offset, $query, $pageHeader, $header); } // end if else user data ?> </body> </html>
The initial
implementation of the
browse( )
function is
shown in Example 5-10. The structure is similar to
that of the hardcoded displayWinesList( )
, with
the additional features to display the result set page-by-page with
the embedded Previous and Next links.
Example 5-10. Generic browsing code for any query
define(ROWS, 20); // Browse through the $connection by the running $query. // Begin the display of data with row $rowOffset. // Put a header on the page, $pageHeader // Use the array $header[]["header"] for headers on // each <table> column // Use the array $header[]["attrib"] for the names // of the database attributes to show in each column // Use $browseString to prefix an embedded link // to the previous, next, and other pages function browse($scriptName, $connection, $browseString, $rowOffset, $query, $pageHeader, $header) { // (1) Run the query on the database through the // connection if (!($result = @ mysql_query ($query, $connection))) showerror( ); // Find out how many rows there are $rowsFound = @ mysql_num_rows($result); // Is there any data? if ($rowsFound != 0) { // Yes, there is data. // (2a) The "Previous" page begins at the current // offset LESS the number of ROWS per page $previousOffset = $rowOffset - ROWS; // (2b) The "Next" page begins at the current offset // PLUS the number of ROWS per page $nextOffset = $rowOffset + ROWS; // (3) Seek to the current offset if (!mysql_data_seek($result, $rowOffset)) showerror( ); // (4a) Output the header and start a table echo $pageHeader; echo "<table border="1"> <tr>"; // (4b) Print out the column headers from $header foreach ($header as $element) echo " <th>" . $element["header"] . "</th>"; echo " </tr>"; // (5a) Fetch one page of results (or less if on the // last page) for ( $rowCounter = 0; (($rowCounter < ROWS) && ($row = @ mysql_fetch_array($result)) ); $rowCounter++) { // Print out a row echo " <tr>"; // (5b) For each of the attributes in a row foreach($header as $element) { echo " <td>"; // Get the database attribute name for the // current attribute $temp = $element["attrib"]; // Print out the value of the current // attribute echo $row["$temp"]; echo "</td>"; } // end foreach attribute echo " </tr> "; } // end for rows in the page // Finish the results table, and start a footer echo " </table> <br>"; // (6) Show the row numbers that are being viewed echo ($rowOffset + 1) . "-" . ($rowCounter + $rowOffset) . " of "; echo "$rowsFound records found matching " . "your criteria <br>"; // (7a) Are there any previous pages? if ($rowOffset > 0) // Yes, so create a previous link echo " <a href="" . $scriptName . "?offset=" . rawurlencode($previousOffset) . "&" . $browseString . "">Previous</a> "; else // No, there is no previous page so don't // print a link echo "Previous "; // (7b) Are there any Next pages? if (($row != false) && ($rowsFound > $nextOffset)) // Yes, so create a next link echo " <a href="" . $scriptName . "?offset=" . rawurlencode($nextOffset) . "&" . $browseString . "">Next</a> "; else // No, there is no next page so don't // print a link echo "Next "; } // end if rowsFound != 0 else { echo "<br>No rows found matching your criteria. "; } // (7c) Create a link back to the query input page echo "<br><a href="" . $scriptName . "">Back to Search</a><br>"; }
The browse( )
function performs the following
steps that are numbered in the comments in Example 5-10:
It runs the $query
through the
$connection
. If there are rows returned from the
query, the remaining steps are followed. If not, a message is
printed.
It calculates where in the result set a Previous and Next link should
be relative to the current offset, $rowOffset
,
that was passed in as a parameter:
// (2a) The "Previous" page begins at the current // offset LESS the number of ROWS per page $previousOffset = $rowOffset - ROWS; // (2b) The "Next" page begins at the current offset // PLUS the number of ROWS per page $nextOffset = $rowOffset + ROWS;
The offsets are used later to construct the Previous and Next links.
ROWS
is the numbers of rows per HTML page, and is
defined as 20 at the beginning of Example 5-10.
It then uses mysql_data_seek( )
to seek in the result set, so that a
subsequent call to mysql_fetch_array( )
retrieves row number
$rowOffset
.
The code then prints out the page header and iterates through the
$header
array printing out the associatively
accessed "header"
elements as
<table>
headings in the first
<table>
row.
The script then retrieves and prints one page of rows from the result set (or, if there is less than a page of rows left to process, as many rows as are available).
A for
loop retrieves each row, and then a
foreach
loop prints out each attribute value in
the row according to how it’s listed in the
$header
associative array element
attrib
. To allow attributes to be referenced
associatively by name, mysql_fetch_array( )
is
used.
Having printed the data in a <table>
, the
script prints out the range of rows displayed (from
$rowOffset + 1
through $rowOffset + $rowCounter
) and the total number of rows that are
retrieved with the query.
To conclude the function, the script produces the Previous and Next embedded links if they are required, and a Back to Search link. The previous link is created with the following code fragment:
// Are there any previous pages? if ($rowOffset > 0) // Yes, so create a previous link echo "<a href="" . $scriptName . "?offset=" . rawurlencode($previousOffset) . "&" . $browseString . "">Previous</a> "; else // No, there is no previous page so don't // print a link echo "Previous ";
A Previous link is produced only if the first row
displayed—$rowOffset
—isn’t
row zero; that is, we have just produced a second or later page. The
code is a little cryptic, but it produces an embedded hypertext link
to $scriptName
, with the parameter
$browseString
that provides parameters to another
query, and the offset variable set to the value of
$previousOffset
calculated earlier.
The rawurlencode( )
function isn’t strictly needed here—we are
only coding a number—but consistently using it to create URLs
with correctly encoded characters is good practice. The Next link is
created with similar logic, and the Back to Search link is a static
link to $scriptName
without any parameters.
We have now developed a generic browser and applied it to browsing
the wines of a region. A similar skeleton to Example 5-9 can be developed to browse customers,
inventories, or orders, and all can use the generic browse( )
function.
In this section, we extend the browse( )
function to produce
page
numbers to permit direct access to the pages, removing the need for
the user to repeatedly click the Previous or Next links to find a
particular page or row. The extended fragment of browse( )
that produces the page links is shown in Example 5-11.
Example 5-11. Adding direct page access to browse( )
// (7a) Previous link code goes here // Output the page numbers as links // Count through the number of pages in the results for($x=0, $page=1; $x<$rowsFound; $x+=ROWS, $page++) // Is this the current page? if ($x < $rowOffset || $x > ($rowOffset + $numRowsToFetch - 1)) // No, so print out a link echo "<a href="" . $scriptName . "?offset=" . rawurlencode($x) . "&" . $browseString . "">" . $page . "</a> "; else // Yes, so don't print a link echo $page . " "; // (7b) Next link code goes here
The page number code consists of a for
loop that
works as follows:
The loop begins counting rows using the variable
$x
—starting at row zero—and pages
using $page
, starting on page one. The loop
finishes when $x
is equal to the number of rows in
the query result set.
In the body of the loop, if the row $x
isn’t on the current page displayed in the HTML
<table>
, an embedded link is output that is
marked with the page number $page
. The link is to
the script resource $scriptName
, with the
parameters in $browseString
and an offset of the
current value of $x
. The current value of
$x
is the first row on the page numbered
$page
. Clicking on the link requests the script
again and produces the results for $page
that
begin with the row with an offset of $x
.
For example, if $x
is row 220, and the
$page
is 11, the embedded link output by the code
fragment is:
<a href= "example.5-11.php? offset=220&regionName=Margaret%20River">11</a>
If $x
is a row on the currently displayed page,
the code outputs the page number without the embedded hypertext link.
The case study of a generic browse( )
function
is now complete. Additional features can be added, as discussed
briefly in the next section.
Features that aren’t described here but could be
incorporated in the browse( )
function include:
Configurable colors for columns, headers, and links.
Configurable column alignment and fonts.
Other layouts, such as horizontal table-based layouts. See the winestore Hot New Wines panel as an example. A horizontal layout is used in the online winestore and is accessible from the book’s web site.
Embedding of links in the body of the
<table>
so that other queries can be run by
clicking on data in the <table>
. Our
customized version for the winestore that is described in Chapter 13 supports this feature.
Spreadsheet-like features, such as the ability to click on a column
heading to sort the data by that
column.
52.14.240.252