Using HTML_Table with Database Data

(Page 1 of 2 )

In this third part of a five-part series on using HTML_Table, you will learn how to create a table from database data and how to leverage subqueries. 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).

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 theEMPLOYEEStable, of course) rather than a standard 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 theROWNUM variable in theWHEREclause. TheROWNUM column is known as an Oracle pseudo-column: the column does not exist in the table itself but instead functions 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.

Next: Leveraging Subqueries >>
 

More Database Articles Articles
More By Apress Publishing

blog comments powered by Disqus