Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 34

interface and PHP. Now it’s time to dig deeper into select queries and refine the results so you get
exactly what you’re looking for, either in great detail or in summary.
In the first part of this chapter, we show you how to install and use the PEAR package HTML_Table to create basic HTML tables and populate them manually with constants as well as use a database table to populate an HTML table.
Next, we cover the basics of the SELECT statement using subqueries to filter query results. This includes filtering rows with the WHERE clause, and sorting rows with the ORDER BY clause. In addition, we show you how to use subqueries with other SQL statements such as UPDATE.
Finally, we take the HTML_Table examples from the first part of the chapter, the subquery tech-
niques that follow, and custom PHP functions to create a generalized application to display any database table or query in a tabular format with sorting and paging functionality.

Using HTML_Table

Be it travel options, product summaries, or movie show times, displaying information in a tabular, or grid, format is one of the most commonplace presentational paradigms in use. And while from the very beginning Web developers have stretched the original intention of HTML tables to their boundaries, the introduction of XHTML and CSS is making Web-based tabular presentations more manageable than ever. In this section, you’ll learn how to build data-driven tables using PHP, Oracle Database XE, and the PEAR package HTML_Table.
While it’s certainly possible to output database data into an HTML table by hard-coding the table tag elements and attributes within your PHP code, doing so can quickly grow tedious and error- prone. Given the prevalence of table-driven output on even simple Web sites, the problems of mixing design and logic in this manner can quickly compound. So what’s the solution? HTML_Table is already at your disposal through PEAR.
In addition to greatly reducing the amount of design-specific code you need to contend with, the HTML_Table package also offers an easy way to incorporate CSS formatting attributes into the output. In this section, you’ll learn how to install HTML_Table and use it to quickly build tabular data output. Note that the intent of this section is not to introduce you to every HTML_Table feature, but rather to highlight some of the key characteristics that you’ll most likely want to use on a regular basis. See the PEAR Web site at http://pear.php.net/package/HTML_Table for a complete breakdown of HTML_Table capabilities.

601

Installing HTML_Table

To install HTML_Table, you use the exact same commands on both the Windows platform and the Linux platform. You use pear list to review the installed packages, and pear install to install new packages. In this example, you query the PEAR installed package list and find out that HTML_Table is not yet on your Windows server, and therefore you install the HTML_Table package using the pear
install command:

■Note We introduced PEAR in Chapter 11. If you skipped it, be sure to review its highlights before continuing with
this chapter.

C:\php5.2.0>pear list

INSTALLED PACKAGES, CHANNEL PEAR.PHP.NET:
========================================= PACKAGE VERSION STATE
Archive_Tar 1.3.1 stable Console_Getopt 1.2 stable PEAR 1.4.9 stable

C:\php5.2.0>pear install -o html_table

downloading HTML_Table-1.7.4.tgz ...
Starting to download HTML_Table-1.7.4.tgz (15,004 bytes)
.....done: 15,004 bytes
downloading HTML_Common-1.2.3.tgz ...
Starting to download HTML_Common-1.2.3.tgz (4,746 bytes)
...done: 4,746 bytes
install ok: channel://pear.php.net/HTML_Common-1.2.3 install ok: channel://pear.php.net/HTML_Table-1.7.4

C:\php5.2.0>pear list

INSTALLED PACKAGES, CHANNEL PEAR.PHP.NET:
========================================= PACKAGE VERSION STATE
Archive_Tar 1.3.1 stable
Console_Getopt 1.2 stable
HTML_Common 1.2.3 stable
HTML_Table 1.7.4 stable
PEAR 1.4.9 stable
C:\php5.2.0>
The -o option automatically downloads any dependent packages. In this case, HTML_Table requires an HTML package with routines used by many other HTML packages: HTML_Common. As a result, the pear install command also installs HTML_Common.

Creating a Simple Table

At its most basic level, HTML_Table requires just a few commands to create a table. For instance, suppose you want to display an array of employee data as an HTML table. Listing 34-1 offers an introductory example that contains a simple CSS style sheet in conjunction with HTML_TABLE to format the employee data found in the $empl_report array.

Listing 34-1. Formatting Employee Data with HTML_Table (html_table_no_db.php)

<style>

table {
border-width: 1px 1px 1px 1px;
border-spacing: 2px;
border-style: outset outset outset outset;
border-color: gray gray gray gray; border-collapse: separate; background-color: white;
}
table th {
border-width: 1px 1px 1px 1px;
padding: 2px 2px 2px 2px;
border-style: inset inset inset inset; border-color: black black black black; background-color: #336699;
color: #FFFFFF;
-moz-border-radius: 0px 0px 0px 0px;
}
table td {
border-width: 1px 1px 1px 1px;
padding: 2px 2px 2px 2px;
border-style: inset inset inset inset; border-color: black black black black; background-color: white;
-moz-border-radius: 0px 0px 0px 0px;
}

</style>

<?php

// Show an HTML_Table form populated by an array
// containing employee data.

// Include the HTML_Table package require_once "HTML\Table.php";
// Assemble the data in an array

$empl_report = array(
'0' => array("100","Steven","King","SKING","515.123.4567", "17-JUN-87","AD_PRES","25000","","","90"),
'1' => array("101","Neena","Kochhar","NKOCHHAR","515.123.4568",
"21-SEP-89","AD_VP","17000","","100","90"),
'2' => array("102","Lex","De Haan","LDEHAAN","515.123.4569", "13-JAN-93","AD_VP","16000","","100","90"),
'3' => array("103","Alexander","Hunold","AHUNOLD","590.423.4567",
"03-JAN-90","IT_PROG","9000","","102","60"),
'4' => array("104","Bruce","Ernst","BERNST","590.423.4568", "21-MAY-91","IT_PROG","6000","","103","60")
);

// Create the table object

$table = new HTML_Table();

// Set the headers

$table->setHeaderContents(0, 0, "Emp ID");
$table->setHeaderContents(0, 1, "First");
$table->setHeaderContents(0, 2, "Last");
$table->setHeaderContents(0, 3, "EMail");
$table->setHeaderContents(0, 4, "Phone");
$table->setHeaderContents(0, 5, "Hire Date");
$table->setHeaderContents(0, 6, "Job ID");
$table->setHeaderContents(0, 7, "Salary");
$table->setHeaderContents(0, 8, "Comm Pct");
$table->setHeaderContents(0, 9, "Mgr ID");
$table->setHeaderContents(0, 10, "Dept ID");

// Cycle through the array to produce the table data

$tot_count = count($empl_report);
for($rownum = 0; $rownum < $tot_count; $rownum++) {
for($colnum = 0; $colnum < 11; $colnum++) {
$table->setCellContents($rownum+1, $colnum, $empl_report[$rownum][$colnum]);
}
}

// Output the data

echo $table->toHTML();

?>

Figure 34-1 shows the output of Listing 34-1.

Figure 34-1. Creating a table with HTML_Table

TWEAKING TABLE STYLES WITH CSS AND HTML_TABLE

Listing 34-1’s introduction mentions use of a CSS style sheet to tweak the table’s appearance (in this case, color, border, and padding). These styles are applied by using basic CSS principles of overriding the default table tag’s attributes with CSS-specific properties. However, when incorporating tables into more complex Web pages, using such a basic CSS strategy won’t be so easy. Fortunately, HTML_Table also supports the ability to tweak tables by passing in table-, header-, row-, and cell-specific attributes. This is accomplished with the HTML_Table() constructor for the table attributes, the setRowAttributes() method for the headers and rows, and the setCellAttributes() method for cell-specific attributes. For each, you just pass in an associative array of attributes. For example, suppose you want to mark up the table with an id attribute called empl_data. You would instantiate the table like so:

$table = new HTML_Table("id"=>"empl_data");

Creating More Readable Row Output

While the data in Figure 34-1 is fairly easy to digest, outputting large amounts of data can quickly become tedious to view. To alleviate some of the difficulty, designers often color every other table row to break up the row elements. Doing so is easy with HTML_Table. For instance, add this style to the style sheet in Figure 34-1:
td.alt {
background-color: #CCCC99;
}

Now add the following line directly following the completion of the for loops in Listing 34-1:

$table->altRowAttributes(1, null, array("class"=>"alt"));

The altRowAttributes parameters specify three things: what row to start at, what attributes to apply to the odd-numbered rows starting with the first row, and what attributes to apply to the even- numbered rows. In this example, you apply the attributes starting with row 1 (row 0 is the header for the table, and you want to leave that as is); you don’t want to apply any attributes to the odd-numbered rows, and you want to apply the background color #CCCC99 to the even-numbered rows.
Executing the revised script produces output similar to that in Figure 34-2.

Figure 34-2. Alternating row styling with HTML_Table

Creating a Table from Database Data

While using arrays as the data source to create tables is great for introducing the basic fundamentals of HTML_Table, chances are you’re going to be retrieving this information from a database. Therefore, let’s build on the previous examples by retrieving employee data from Oracle Database XE and presenting it to the user in a tabular format.
The general process really doesn’t differ much from that presented in Listing 34-1, except this time you’ll be navigating through a result set (from the EMPLOYEES table, of course) rather than a stan- dard array to populate the page. Listing 34-2 contains the code without the style tag.

Listing 34-2. Displaying Oracle Data in Tabular Format

<?php

// Show an HTML_Table form populated from a database table
// containing employee data.

// Include the HTML_Table package require_once "HTML\Table.php";
// Connect to the server and select the database
$c = @oci_connect('hr', 'hr', '//localhost/xe')
or die("Could not connect to Oracle server");

// Create the table object

$table = new HTML_Table();

// Set the headers

$table->setHeaderContents(0, 0, "Emp ID");
$table->setHeaderContents(0, 1, "First");
$table->setHeaderContents(0, 2, "Last");
$table->setHeaderContents(0, 3, "EMail");
$table->setHeaderContents(0, 4, "Phone");
$table->setHeaderContents(0, 5, "Hire Date");
$table->setHeaderContents(0, 6, "Job ID");
$table->setHeaderContents(0, 7, "Salary");
$table->setHeaderContents(0, 8, "Comm Pct");
$table->setHeaderContents(0, 9, "Mgr ID");
$table->setHeaderContents(0, 10, "Dept ID");

// Cycle through the array to produce the table data
// after calling the query to retrieve the first 5 rows

$s = oci_parse($c,
"select employee_id, first_name, last_name, email, " . "phone_number, hire_date, job_id, salary, commission_pct, " . "manager_id, department_id from employees where rownum < 6");

oci_execute($s);

$rownum = 1; // don't overwrite header of table while ($res = oci_fetch_array($s)) {
for($colnum = 0; $colnum < 11; $colnum++) {
$table->setCellContents($rownum, $colnum, $res[$colnum]);
}
$rownum++;
}

$table->altRowAttributes(1, null, array("class"=>"alt"));

// Output the data

echo $table->toHTML();

oci_close($c);

?>

Executing Listing 34-2 produces output identical to that in Figure 34-1; notice that the number
of rows returned is restricted to five, using the ROWNUM variable in the WHERE clause. The ROWNUM column is known as an Oracle pseudo-column: the column does not exist in the table itself but instead func- tions as a counter containing the row number of the result set. If the database query returns hundreds of rows, you don’t want to display them all on one page. Later in this chapter in the section “Creating Paged Output” we show you how to create paged output.

Leveraging Subqueries

A properly normalized database is key to building and managing a successful data-driven project. Of course, with this additional degree of efficiency comes complexity, not only in terms of the rigorous structuring of the database schema to ensure correspondence to the rules of normalization, but also in terms of building queries capable of stretching across multiple tables (known as a join).
As an alternative to joins, subqueries offer users a secondary means for querying multiple tables, using a syntax that is arguably more intuitive than that required for a join. This section introduces subqueries, demonstrating how they can cut lengthy joins and tedious multiple queries from your application. Keep in mind that this isn’t an exhaustive discourse on Oracle’s subquery capabilities; for a complete reference, see the Oracle Database XE online documentation at http://www.oracle.com/ pls/xe102/homepage.
Simply put, a subquery is a SELECT statement embedded within another statement. For instance, suppose that you want to create a report for the HR department that will return all employees with the same salary as an employee currently being reviewed for a salary adjustment. The relevant parts of the first ten rows of the EMPLOYEES table are shown in Figure 34-3.

Figure 34-3. Selected columns from the EMPLOYEES table

The HR department wants to find out which employees have the same salary as David Austin, employee number 105. Broken down into steps, you would need to execute two queries. First, you would need to retrieve Austin’s salary with this query and place it into a PHP variable, let’s say $sal:

select salary from employees where employee_id = 105;

Next, you would need to pass the salary in the variable $sal into a second query similar to the following:
select employee_id, first_name, last_name, salary from employees where salary = '$sal';

A subquery enables you to combine these tasks into a single query, like so:

select employee_id, first_name, last_name, salary from employees
where salary = (select salary from employees where employee_id = 105);

Figure 34-4 shows the results: all employees who have the same salary as Austin, which of course includes Austin himself.

Figure 34-4. Employees with the same salary as Austin

Performing Comparisons with Subqueries

Subqueries are also very useful for performing comparisons. For example, suppose that the HR department wants to know who makes more money than the average salary in the company. The following query will give the HR department what it needs:

select employee_id, first_name, last_name, salary from employees where salary > (select avg(salary) from employees);

You’re free to use any of the comparison operators and aggregation functions when creating subqueries; if the query returns a single row with a single column, a query enclosed in parentheses is the equivalent of a constant or another column in a comparison operation.

Determining Existence with Subqueries

Building on the HR department theme, the database supports employment history in the JOB_HISTORY table. An installalation of Oracle Database XE includes this table with data in the HR schema. The structure of the table is as follows:

SQL> describe job_history;

Name Null? Type
----------------------------------------- -------- -------------- EMPLOYEE_ID NOT NULL NUMBER(6) START_DATE NOT NULL DATE
END_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)

A given employee will be in the table only if they have changed jobs since they started with the company. Their current job and department is recorded in the EMPLOYEES table. When the employee changes jobs or departments, a new row is added to this table. Now suppose that you want to deter- mine which employees have changed jobs more than once. To find out, you can use the EXISTS clause in conjunction with a subquery to easily retrieve this information:

select employee_id, first_name, last_name, salary from employees where exists (select count(*) from job_history
where job_history.employee_id = employees.employee_id having count(*) > 1);

This query produces the results in Figure 34-5.

Figure 34-5. Employees with more than one job change

The subquery only retrieves counts from the JOB_HISTORY table that have more than one row for a given employee linked to the main query in the WHERE condition. Similarly, you can determine which employees have not changed jobs at the company by using the NOT EXISTS clause in conjunction with a subquery:
select employee_id, first_name, last_name, salary from employees where not exists (select * from job_history
where job_history.employee_id =
employees.employee_id);

This produces the output in Figure 34-6; more than ten employees at the company have never changed jobs.
Even more simply, you can find out which employees have not changed jobs by using the NOT IN clause:
select employee_id, first_name, last_name, salary from employees where employee_id not in (select employee_id from job_history);

Figure 34-6. Employees with no job changes

Database Maintenance with Subqueries

Subqueries aren’t limited solely to selecting data; you can also use this feature to manage your data- base. For instance, suppose that the HR department, by direction of upper management, wants to give a 1 percent raise to all employees who have switched jobs or departments within the last year. The UPDATE statement, with a subquery, would be as follows:
update employees set salary = salary * 1.01
where employee_id in (select employee_id from job_history where start_date > sysdate-365);
The SYSDATE column is another example of a pseudo-column; like the ROWNUM column, it does not exist in any table. Or if you prefer, you can say that pseudo-columns exist in every table. SYSDATE returns the current date and time. In this example, you want to give salary increases if the employee’s starting date from their last job change occurred within the last 365 days.

Generalizing the Output Process

Granted, hard-coding the column header names into HTML_Table is fairly easy; however, this is a task that could be used dozens of times in a single application alone. Therefore, it makes sense to devise a general solution that can be used repeatedly no matter the data. You can see the new function tabular_output() in Listing 34-3.

Listing 34-3. Generalizing the Tabular Output Task Using a Function (tabular_output.php)

function tabular_output($conn, $query)
{

// Create the table object
$table = new HTML_Table();

// Parse and execute the query
$s = oci_parse($conn, $query);
oci_execute($s);

// Cycle through each field, outputting its name
$ncols = oci_num_fields($s);
for($i = 0; $i < $ncols; $i++) {

$table->setHeaderContents(0, $i, oci_field_name($s, $i+1));
}

// Cycle through the array to produce the table data
// Begin at row 1 so don't overwrite the header
$rownum = 1;

// Reset column offset
$colnum = 0;

// Cycle through each row in the result set
while ($row = oci_fetch_array($s, OCI_RETURN_NULLS)) {
// Cycle through each column in the row while ($colnum < $ncols) {
$table->setCellContents($rownum, $colnum, $row[$colnum]);
$colnum++;
}
$rownum++;
$colnum = 0;
}

// Output the data
echo $table->toHTML();
}

For the purposes of this example, any custom CSS styling tags were removed, but you could
easily add a few additional parameters to pass this information along. For the tabular_output() function, you pass two values: the connection handle (from oci_connect()) and the query you want to run on this connection as a string.
Here is an example of how you could call tabular_output() from a PHP script:

<?php

// Show an HTML_Table form populated from a database table
// containing employee data.
// Include the HTML_Table package require_once "HTML\Table.php";
require_once "tabular_output.php";

// Connect to the server and select the database
$c = @oci_connect('hr', 'hr', '//localhost/xe')
or die("Could not connect to Oracle server");

tabular_output($c,
"select employee_id, first_name, last_name, email, " . "phone_number, hire_date, job_id, salary, commission_pct, " . "manager_id, department_id from employees where rownum < 6");

oci_close($c);

?>

Because the alternate row formatting was removed from this example, the outcome will look similar to that found in Figure 34-1; since you are not assigning your own column names to the table, the uppercase Oracle column names will appear instead.

Sorting Output

When displaying query results, it makes sense to order the information, using criteria that are convenient to the user. For example, if the HR department wants to view a list of all employees in the EMPLOYEES table, ordering the employees by last name in ascending alphabetical order will probably suffice. However, sometimes they may want to order the information using some other criteria—by salary, for example. Often such mechanisms are implemented by linking listing headers, such as the table headers used in the previous examples. Clicking any of these links will cause the table data to be sorted using that header as the criterion.
In this section, you’ll learn how to enhance the tabular_output() function created in the previous section. In fact, doing so is incredibly easy because all you need to do is make three modifications to the code. First, modify the for statement responsible for outputting the header information so that it looks like this:

for($i = 0; $i < $ncols; $i++) {
$header = "<a href='" . $_SERVER['PHP_SELF'] .
"?sort=" . oci_field_name($s, $i+1) . "'>" . oci_field_name($s, $i+1) . "</a>";
$table->setHeaderContents(0, $i, $header);
// originally: $table->setHeaderContents(0, $i, oci_field_name($s, $i+1));
}

This links each header title back to the originating script, passing the desired sortable column title to it. For example, the SALARY link looks like this:

<a href='test_tabular_output_sorted.php?sort=SALARY'>SALARY</a>

Finally, add a new variable assignment before calling the function and modify the query to change the ORDER BY clause. In this example we use a query with a few less columns than the one we used in the previous section:
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : "EMPLOYEE_ID";

tabular_output($c,
"select employee_id, first_name, last_name," . "hire_date, job_id, salary," .
"manager_id, department_id from employees where rownum < 16 " . "order by " . $sort);
The statement with a ternary operator, introduced in Chapter 3, is used to determine whether the user has clicked one of the header links. If a sort parameter has been passed via the URL the first time the script is run, that value will be the sorting criteria. Otherwise, a default of EMPLOYEE_ID is used. It’s very important that you make sure that $_GET['sort'] does indeed consist of one of the column names and does not consist of additional query statements that could retrieve unintended information or potentially modify or destroy your data. Therefore, be sure to preface the query with some sort of logic capable of determining this, such as the following:

$columns = array('EMPLOYEE_ID', 'LAST_NAME',
'HIRE_DATE', 'SALARY', 'DEPARTMENT_ID');
if (in_array($_GET['sort'], $columns)) {
// Proceed with the query
}

Of course, you could further automate the process of populating the $columns variable by using the Oracle Database XE OCI8 call oci_field_name().
Running the script for the first time results in the output being sorted by EMPLOYEE_ID. Figure 34-7 shows the output for the first 15 rows of the EMPLOYEES table sorted by the EMPLOYEE_ID column.

Figure 34-7. The EMPLOYEES table output sorted by EMPLOYEE_ID

Clicking the SALARY header re-sorts the output by salary. This sorted output is shown in Figure 34-8.

Figure 34-8. The EMPLOYEES table output sorted by SALARY

Creating Paged Output

Separating query results across several pages has become a commonplace feature for e-commerce catalogs and search engines. This feature is convenient not only to enhance readability but also to further optimize page loading. You might be surprised to learn that adding this feature to your Web site is a trivial affair, just like sorting the columns in your table. This section demonstrates how it’s accomplished.
This feature depends in part on Oracle SQL’s ROWNUM variable, which you used earlier in this chapter. You’ll also use the COUNT() aggregate function to count the total number of rows returned from a SELECT query; this number will determine when you no longer need a Next link at the bottom of the page. Your SQL statement, with the PHP variables, will look something like this:

select *
from ( select t.*, rownum as rnum from ( $pquery ) t
where rownum <= ($startrow + $pagesize - 1) )
where rnum >= $startrow

The variable $pquery contains the complete text of the query you want paged, including the sort order functionality in the previous section. The variables $startrow and $pagesize define the starting row number and page size, respectively. You’ll be able to pass the $startrow variable within the URL, much like the sort column in the previous section. You’ll set the variable $rowcount to the total number of rows returned by the query.
To complete this task, we give you the new individual pieces with an explanation of how it works, then give you the new calling script in its entirety in Listing 34-4, including the sorting functionality we introduced in the previous section.
First, you’ll set the page size:

$pagesize = 10;

Next, a ternary operator determines whether the $_GET['startrow'] parameter has been passed by way of the URL. This parameter determines the offset from which the result set should begin. If this parameter is present, it’s assigned to $startrow; otherwise, $startrow is set to 1:

$startrow = (isset($_GET['startrow'])) ? (int) $_GET['startrow'] : 1;

You’ll need to save the target query in a separate variable, $pquery, since you’re going to count the number of rows in the query result and retrieve individual rows in the query:

$pquery = "select employee_id, first_name, last_name," . "hire_date, job_id, salary ,manager_id, " . "department_id from employees order by " . $sort;
To get the total row count from the query, you will use oci_parse() and oci_execute() as follows:

$s = oci_parse($c, "select count(*) from ( $pquery )");
oci_execute($s);
$row = oci_fetch_array($s, OCI_NUM);
$rowcount = $row[0];

Next, the database query is executed and the data is output using the tabular_output() function just as in the last section. Note that the starting row is set to $startrow, and the number of entries to retrieve is set to $pagesize:

tabular_output($c, "select employee_id, first_name, last_name," . "hire_date, job_id, salary ,manager_id, " . "department_id " .
"from ( select t.*, rownum as rnum" .
" from ( $pquery ) t " .
" where rownum <= ($startrow + $pagesize - 1) ) " . "where rnum >= $startrow" );
Finally, you create the Previous and Next links. The Previous link is created only if the record offset, $startrow, is greater than 1. The Next link is created only if some records remain to be retrieved, meaning that $recordstart + $pagesize - 1 must be less than $rowcount:
// Create the 'previous' link if ($startrow > 1) {
$prev = $startrow - $pagesize;
$url = $_SERVER['PHP_SELF']."?startrow=$prev";
echo "<a href=\"$url\">Previous Page</a> ";
}

// Create the 'next' link
if ($rowcount > ($startrow + $pagesize - 1)) {
$next = $startrow + $pagesize;
$url = $_SERVER['PHP_SELF']."?startrow=$next";
echo "<a href=\"$url\">Next Page</a>";
}

Sample output showing the Previous Page and Next Page links is shown in Figure 34-9. The
complete code listing is presented in Listing 34-4; the called function tabular_output() is unchanged from earlier in the chapter.

Figure 34-9. Paged results from the EMPLOYEES table (ten results per page)

Listing 34-4. Paging Database Rows (test_tabular_output_paged.php)

<?php

// Show an HTML_Table form populated from a database table
// containing employee data, with sorting and paging
// capabilities.

require_once "HTML\Table.php";
require_once "tabular_output.php";

// Connect to the server and select the database
$c = @oci_connect('hr', 'hr', '//localhost/xe')
or die("Could not connect to Oracle server");

// default or specified sort order for results
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : "EMPLOYEE_ID";

// page size for result set
$pagesize = 10;

// starting row number; start with 1 if not specified
$startrow = (int) $_GET['startrow'];
$startrow = (isset($_GET['startrow'])) ? $startrow : 1;

// define target query for further processing
$pquery = "select employee_id, first_name, last_name," . "hire_date, job_id, salary ,manager_id, " . "department_id from employees order by " . $sort;

$s = oci_parse($c, "select count(*) from ( $pquery )");
oci_execute($s);
$row = oci_fetch_array($s, OCI_NUM);
$rowcount = $row[0];

tabular_output($c, "select employee_id, first_name, last_name," . "hire_date, job_id, salary ,manager_id, " . "department_id " .
"from ( select t.*, rownum as rnum" . " from ( $pquery ) t " .
" where rownum <= ($startrow + $pagesize - 1) ) " . "where rnum >= $startrow" );

// Create the 'previous' link if ($startrow > 1) {
$prev = $startrow - $pagesize;
$url = $_SERVER['PHP_SELF'] . "?startrow=$prev";
echo "<a href=\"$url\">Previous Page</a> ";
}

// Create the 'next' link
if ($rowcount > ($startrow + $pagesize - 1)) {
$next = $startrow + $pagesize;
$url = $_SERVER['PHP_SELF'] . "?startrow=$next";
echo "<a href=\"$url\">Next Page</a>";
}

oci_close($c);

?>

Listing Page Numbers

If you have several pages of results, the user might wish to traverse them in a nonlinear order. For example, the user might choose to jump from page one to page three, then page six to the last page, then back to page one again. Thankfully, providing users with a linked list of page numbers is surprisingly easy. Building on Listing 34-4, you start by determining the total number of pages and assigning that value to the variable $totalpages. You determine the total number of pages by dividing the total result rows by the chosen page size and rounding upward using the ceil() function:

$totalpages = ceil($rowcount / $pagesize);

Next you determine the current page number and assign it to $currentpage. You determine the current page by dividing the present record offset ($startrow) by the chosen page size ($pagesize):

$currentpage = (($startrow-1) / $pagesize) + 1;

We show you where to place these statements later in this section. Next create a function called
pagelinks(), and include it in the calling script; pass it the following four parameters:

• $totalpages: The total number of result pages, stored in the $totalpages variable.

• $currentpage: The current page, stored in the $currentpage variable.

• $pagesize: The chosen page size, stored in the $pagesize variable.

• $parameter: The name of the parameter used to pass the record offset by way of the URL. Thus far, startrow has been used, so we’ll stick with that in the following example.

The pagelinks() function appears in Listing 34-5.

Listing 34-5. The pagelinks() Function (pagelinks.php)

<?php

function pageLinks($totalpages, $currentpage, $pagesize, $parameter) {

// Start at page one
$page = 1;

// Start at record one
$recordstart = 1;

// Initialize $pageLinks
$pageLinks = "";

while ($page <= $totalpages) {
// Link the page if it isn't the current one if ($page != $currentpage) {
$pageLinks .= "<a href=\"".$_SERVER['PHP_SELF']. "?$parameter=$recordstart\">$page</a> ";

// If the current page, just list the number
} else {
$pageLinks .= "$page ";
}
// Move to the next record delimiter
$recordstart += $pagesize;
$page++;
}
return $pageLinks;
}

?>

Finally, you call pagelinks() like this:

echo "<br><p>Pages: " .
pagelinks($totalpages, $currentpage, $pagesize, "startrow") . "</p>";

You put the variable assignments for $totalpages, $currentpage, and the new echo statement before the oci_close in Figure 34-4. The end of the script test_tabular_output_paged.php now looks like the code in Listing 34-6.

Listing 34-6. Revised Tabular Output Script (test_tabular_output_paged_numbered.php)

<?php

// Show an HTML_Table form populated from a database table
// containing employee data, with sorting and paging
// capabilities.

. . .

// Page links for direct access
$totalpages = ceil($rowcount / $pagesize);
$currentpage = (($startrow-1) / $pagesize) + 1;
echo "<br><p>Pages: " .
pagelinks($totalpages, $currentpage, $pagesize, "startrow") . "</p>";

oci_close($c);

?>

Page six of the employee data with the page links in addition to all previous functionality we’ve
introduced up to this point is shown in Figure 34-10.

Figure 34-10. Paged results from the EMPLOYEES table, including page links

Summary

Now that you’ve read this chapter, you should be able to install the PEAR module HTML_Table and use it to build a Web page with data from any database table or query. In addition, you learned a couple of new ways to enhance your HTML output by tweaking the style sheets associated with your Web page.
Subqueries give you an alternative to table joins to retrieve and modify your table data. Whether you use a subquery or a join in a particular application depends on your programming style and corporate coding standards; how the Oracle SQL engine runs your query is less and less of a concern given Oracle Database XE’s advanced query optimization methods.
All throughout the chapter, we introduced a number of handy Oracle features, such as the SYSDATE
pseudo-column, the ROWNUM pseudo-column, and the aggregate function COUNT().
Finally, you took the basic output from HTML_Table() and added sorting by column headers, paging forward and backward through a result set, and adding the ability to jump directly to a specific page in the output.
In the next chapter, we tell you about another useful Oracle construct, the view. We’ll show you
how to create your own views as well as present the key Oracle provided views and when to use them.