Handling Oracle HTML Table Output

(Page 1 of 3 )

In this conclusion to a five-part article series on using HTML Table with advanced queries, you'll learn how to sort and paginate output, and list page numbers. This article is excerpted from chapter 34 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Sorting [Oracle HTML] 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 EMPLOYEEStable, 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 thetabular_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 theforstatement 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, theSALARY 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 theORDER BYclause. 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 asortparameter has been passed via the URL the first time the script is run, that value will be the sorting criteria. Otherwise, a default ofEMPLOYEE_IDis 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$columnsvariable by using the Oracle Database XE OCI8 calloci_field_name().

Running the script for the first time results in the output being sorted byEMPLOYEE_ID. Figure 34-7 shows the output for the first 15 rows of theEMPLOYEEStable sorted by theEMPLOYEE_IDcolumn.

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

Clicking theSALARYheader 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

Next: Creating Paged Output >>
 

More Database Articles Articles
More By Apress Publishing

blog comments powered by Disqus