User's Manual

Navigating Through Database Records
Beta Draft Querying Data 4-9
$posturl parameter to set the HTML form's action attribute. So each Next or
Previous button click calls anyco.php.
3. Edit anyco_db.inc. Implement the db_get_page_data() function to query a
sub set of rows:
// Return subset of records
function db_get_page_data($conn, $q1, $current = 1,
$rowsperpage = 1, $bindvars = array())
{
// This query wraps the supplied query, and is used
// to retrieve a subset of rows from $q1
$query = 'SELECT *
FROM (SELECT A.*, ROWNUM AS RNUM
FROM ('.$q1.') A
WHERE ROWNUM <= :LAST)
WHERE :FIRST <= RNUM';
// Set up bind variables.
array_push($bindvars, array('FIRST', $current, -1));
array_push($bindvars,
array('LAST', $current+$rowsperpage-1, -1));
$r = db_do_query($conn, $query, $bindvars);
return($r);
}
The structure of the query in db_get_page_data() enables navigation through
a set (or page) of database rows.
The query supplied in $q1 is nested as a sub query inside the sub query
SELECT A.*, ROWNUM AS RNUM FROM $q1 WHERE ROWNUM <= :LAST
Remember the query supplied in $q1 retrieves an ordered set of rows, which is
filtered by its enclosing query to return all the rows from the first row to the next
page size ($rowsperpage) of rows. This is possible since the Oracle ROWNUM
function (or pseudo column) returns an integer number starting at 1 for each row
returned by the query in $q1.
The set of rows, returned by the sub query enclosing query $q1, is filtered a
second time by the condition in the outermost query
WHERE :FIRST <= RNUM
This condition ensures that rows prior to the value in :FIRST (the value in
$current) are excluded from the final set of rows. The query enables navigation
through a set rows where the first row is determined by the $current value and
the page size is determined by the $rowsperpage value.
The $current value associated to the bind variable called :FIRST, the
expression $current+$rowsperpage-1 sets the value associated with the
:LAST bind variable.
4. To test the changes made to your application, save the each file you modified, and
enter the following URL in your Web browser:
http://localhost/~<username>/chap4/anyco.php
Since this is the first time you request the anyco.php page, you see the
Administration department displayed: