Creating Online Reports with Oracle and PHP

Learn about different techniques for creating online reports with Oracle and PHP

Downloads for this article: Oracle Database 10g Express Edition Zend Core for Oracle

Published July 2006

The ability to generate database-driven reports is a "must-have" in most Web-based applications. In order to create a complex report, most developers resort to employing composite data sets--those sets that cannot be retrieved by running just a single SQL query but rather by executing a collection of smaller queries in a loop.

Although this approach is often appropriate, there are alternatives. In this recipe, you will learn alternative techniques that rely on variable binding and Oracle's CURSOR expressions and measure these against the traditional approach. All the code is included, so you should be able to replicate the testing conditions and do some benchmarking of your own.

Setting Up the Data Structure

For this recipe I am going to reuse the example from the "Inserting Multiple Rows With One Database Call Using PHP and Oracle Collections" recipe that illustrated how to use Oracle cursors to speed up bulk data inserts. In that recipe, you created a simple database structure that could be used to support a hypothetical e-commerce Web application. Here you will add another table that can be used to track status changes to each order (Order received -> Order processed -> Order packed -> Order shipped). In other words, by looking at this table, you will be able to tell the current status, as well as the history of status changes, for any given order.

Here is the rehashed data structure:

Here is the SQL code to create the tables above:

CREATE TABLE CUSTOMERS ( 
  CUSTOMER_ID    NUMBER        NOT NULL, 
  CUSTOMER_NAME  VARCHAR2 (100)  NOT NULL, 
  CONSTRAINT PK_CUSTOMERS
  PRIMARY KEY ( CUSTOMER_ID ) ) ; 
/
CREATE TABLE ITEMS ( 
  ITEM_ID           NUMBER        NOT NULL, 
  ITEM_DESCRIPTION  VARCHAR2 (50)  NOT NULL, 
  ITEM_PRICE        NUMBER        NOT NULL, 
  CONSTRAINT PK_ITEMS
  PRIMARY KEY ( ITEM_ID ) ) ; 
/
CREATE TABLE ORDERS ( 
  ORDER_ID     NUMBER        NOT NULL, 
  ORDER_DATE   DATE          NOT NULL, 
  CUSTOMER_ID  NUMBER, 
  CONSTRAINT PK_ORDERS
  PRIMARY KEY ( ORDER_ID ) ) ; 

ALTER TABLE ORDERS ADD  CONSTRAINT FK_CUSTOMER
 FOREIGN KEY (CUSTOMER_ID) 
  REFERENCES CUSTOMERS (CUSTOMER_ID) ;
/
CREATE TABLE ORDER_ITEMS ( 
  ORDER_LINE_ID  NUMBER        NOT NULL, 
  ORDER_ID       NUMBER        NOT NULL, 
  ITEM_ID        NUMBER        NOT NULL, 
  CONSTRAINT PK_ORDER_ITEMS
  PRIMARY KEY ( ORDER_LINE_ID ) ) ; 

ALTER TABLE ORDER_ITEMS ADD  CONSTRAINT FK_ITEM
 FOREIGN KEY (ITEM_ID) 
  REFERENCES ITEMS (ITEM_ID) ;

ALTER TABLE ORDER_ITEMS ADD  CONSTRAINT FK_ORDER
 FOREIGN KEY (ORDER_ID) 
  REFERENCES ORDERS (ORDER_ID) ;

/
CREATE TABLE ORDER_TRACKING ( 
  RECORD_ID       NUMBER        NOT NULL, 
  CURRENT_STATUS  VARCHAR2 (100), 
  ORDER_ID        NUMBER, 
  RECORD_DATE     DATE, 
  CONSTRAINT PK_ORDER_TRACKING
  PRIMARY KEY ( RECORD_ID ) ) ; 

ALTER TABLE ORDER_TRACKING ADD  CONSTRAINT FK_ORDER_TRACKING
 FOREIGN KEY (ORDER_ID) 
  REFERENCES ORDERS (ORDER_ID) ;

CREATE SEQUENCE ORDER_LINE_SQ
INCREMENT BY 1;
/

CREATE SEQUENCE ORDER_STATUS_SQ
INCREMENT BY 1;
/

CREATE SEQUENCE ORDER_LINE_SQ
INCREMENT BY 1;
/

//connect to the database using your login name and password
$schema = "my_schema";
$ password = "my_password";

$db = oci_connect('$schema, $password) or die;

$arr_order_items = array(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15); //all items on the list
$arr_status = array("Received","Processed","Packed","Shipped");

//create 200 orders for CUSTOMER_ID = 1

for ($order_id = 1;$order_id<=200;$order_id++) {
   $sql = "INSERT INTO ORDERS (order_id,customer_id,order_date) VALUES (".$order_id.",1,SYSDATE)";
   $stmt = oci_parse($db,$sql);
   oci_execute($stmt);
//add 15 items to each order
   foreach ($arr_order_items as $item) {
      $sql = "INSERT INTO ORDER_ITEMS (order_line_id,order_id,item_id) VALUES 
      (ORDER_LINE_SQ.nextval,".$order_id.",".$item.")";
      $stmt = oci_parse($db,$sql);
      oci_execute($stmt);
   }
//populate status for each order	
   foreach ($arr_status as $status) {
      $sql = "INSERT INTO ORDER_TRACKING (order_id,current_status,record_date) VALUES 
      (".$order_id.",'".$status."',SYSDATE)";
      $stmt = oci_parse($db,$sql);
      oci_execute($stmt);
  }
}

Report Requirements

First, create an order history report for your only customer (CUSTOMER_ID = 1). The report will present the complete list of customer's orders with two subsections: one that shows the items included with each given order, and one that lists this order's status update history. In the following sections, you will create this report using three different techniques: by executing a series of stand-alone database calls in loop, by executing the same stand-alone queries with variable binding, and by using Oracle's CURSOR expressions in a single query.

$stmt = oci_parse($db,"SELECT order_id,order_date,customer_name FROM CUSTOMERS,ORDERS WHERE 
ORDERS.customer_id = 1 AND ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID");

oci_execute($stmt);

while ($orders = oci_fetch_assoc($stmt)) {
   $arr_order_status = array(); // to temporarily store status history for given order id
   $arr_order_items = array(); // to temporarily store item list for given order id
   $arr_order_line = array(); // to temporarily store complete order information for given order id
		
   $arr_order_line =    array("ORDER_ID"=>$orders["ORDER_ID"],"ORDER_DATE"=>$orders["ORDER_DATE"], 
   "CUSTOMER_NAME"=>$orders["CUSTOMER_NAME"]);

//first "sub-query"
   $stmt2 = oci_parse($db,"SELECT current_status,record_date FROM ORDER_TRACKING WHERE order_id = ".$orders["ORDER_ID"]);

   oci_execute($stmt2);

   while ($status = oci_fetch_assoc($stmt2)) { //note that this function is PHP5 only, use OCIFetchInto for PHP4
      $arr_order_status[] = array("CURRENT_STATUS"=>$status["CURRENT_STATUS"], 
      "RECORD_DATE"=>$status[ "RECORD_DATE" ]);
   }

   $arr_order_line["STATUS"] = $arr_order_status;

//second "sub-query"
   $stmt3 = Oci_parse($db,"SELECT item_description,item_price FROM ITEMS,ORDER_ITEMS WHERE 
   ORDER_ITEMS.ITEM_ID = ITEMS.ITEM_ID AND ORDER_ITEMS.ORDER_ID = ".$orders["ORDER_ID"]);
   oci_execute($stmt3);
   while ($items = oci_fetch_assoc($stmt3)) {
      $arr_order_items[] = array("ITEM_DESCRIPTION"=>$items["ITEM_DESCRIPTION"],   
      "ITEM_PRICE"=>$items["ITEM_PRICE"]);
   }

   $arr_order_line["ITEMS"] = $arr_order_items;

//add new line to the orders array
   $arr_orders[] = $arr_order_line;
}

$stmt = oci_parse($db,"SELECT order_id,order_date,customer_name FROM CUSTOMERS,
                  ORDERS WHERE ORDERS.customer_id = 1 AND ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID");

//parse the statement to get status update history and bind it to the variable
$stmt2 = oci_parse($db,"SELECT current_status,record_date FROM ORDER_TRACKING WHERE order_id = :var");
oci_bind_by_name($stmt2, ":var", $order_id,32);

//parse the statement to get item list and bind it to the variable
$stmt3 = oci_parse($db,"SELECT item_description,item_price FROM ITEMS,
                   ORDER_ITEMS WHERE ORDER_ITEMS.ITEM_ID = ITEMS.ITEM_ID AND ORDER_ITEMS.ORDER_ID = :var");
oci_bind_by_name ($stmt3, ":var", $order_id,32);
	
oci_execute($stmt); //execute order list statement
	
while ($orders = oci_fetch_assoc ($stmt)) {
   $arr_order_status = array();
   $arr_order_items = array();
   $arr_order_line = array();
		
   $arr_order_line = array("ORDER_ID"=>$orders["ORDER_ID"],"ORDER_DATE"=>$orders["ORDER_DATE"], 
   "CUSTOMER_NAME"=>$orders["CUSTOMER_NAME"]);
		
   $order_id = $orders["ORDER_ID"]; //current order id

//first "sub-query"
   oci_execute($stmt2);
   while ($status = oci_fetch_assoc($stmt2)) {
      $arr_order_status[] = array("CURRENT_STATUS"=>$status["CURRENT_STATUS"], 
      "RECORD_DATE"=>$status[ "RECORD_DATE" ]);
   }

   $arr_order_line["STATUS"] = $arr_order_status;

//second "sub-query"
   oci_execute($stmt3);
   while ($items = oci_fetch_assoc($stmt3)) {
      $arr_order_items[] = array("ITEM_DESCRIPTION"=>$items["ITEM_DESCRIPTION"], 
      "ITEM_PRICE"=>$items["ITEM_PRICE"]);
   }

   $arr_order_line["ITEMS"] = $arr_order_items;
		
//add new line to the orders array
   $arr_orders[] = $arr_order_line;
}

$stmt = oci_parse($db,"SELECT order_id,order_date,customer_name, 
CURSOR(SELECT current_status,record_date FROM ORDER_TRACKING WHERE order_id = ORDERS.order_id) as status,
CURSOR(SELECT item_description,item_price FROM ITEMS,
       ORDER_ITEMS WHERE ORDER_ITEMS.ITEM_ID = ITEMS.ITEM_ID AND ORDER_ITEMS.ORDER_ID = ORDERS.order_id) AS items
FROM CUSTOMERS, ORDERS WHERE ORDERS.customer_id = 1
AND ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID");

oci_execute($stmt);
while ($orders = oci_fetch_assoc ($stmt)) {
   $arr_order_status = array();
   $arr_order_items = array();
   $arr_order_line = array();

   $arr_order_line = array("ORDER_ID"=>$orders["ORDER_ID"],"ORDER_DATE"=>$orders["ORDER_DATE"],  
   "CUSTOMER_NAME"=>$orders["CUSTOMER_NAME"]);
		
//process cursors
   oci_execute($orders["STATUS"]); //get data from the nested collection
   while ($status = oci_fetch_assoc($orders["STATUS"])) {
      $arr_order_status[] = array("CURRENT_STATUS"=>$status["CURRENT_STATUS"],   
      "RECORD_DATE"=>$status["RECORD_DATE"]);
   }
		
   $arr_order_line["STATUS"] = $arr_order_status;
		
   oci_execute($orders["ITEMS"]);
   while ($items = oci_fetch_assoc($orders["ITEMS"])) {
      $arr_order_items[] = array("ITEM_DESCRIPTION"=>$items["ITEM_DESCRIPTION"], 
      "ITEM_PRICE"=>$items["ITEM_PRICE"]);
   }
		
   $arr_order_line["ITEMS"] = $arr_order_items;
		
//add new line to the orders array
   $arr_orders[] = $arr_order_line;
}

Viewing the Report

The report data is now contained in the $arr_orders array. Here is a little snippet of code that loops through the array and renders the data as an HTML table for easy viewing. Since in our example all orders are identical, this snippet will only show the first two orders.

for($i=0;$i<=1;$i++) { //show only the first two orders
   echo "<table cellpadding=\"2\" cellspacing=\"2\" width=\"500\" border=\"1\">";
   echo "<tr><td colspan=\"2\">Order # ".($i+1)." (".$arr_orders[$i]["ORDER_ID"]." "
   .$arr_orders[$i]["ORDER_DATE"].",".$arr_orders[$i]["CUSTOMER_NAME"].")</td></tr>";
   echo "<tr><td valign=\"top\">";
   foreach($arr_orders[$i]["STATUS"] as $status) {
      echo $status["RECORD_DATE"]." - ".$status["CURRENT_STATUS"]."<br />";
   }
   echo "</td><td valign=\"top\">";
   foreach($arr_orders[$i]["ITEMS"] as $item) {
      echo $item["ITEM_DESCRIPTION"]." - ".$item["ITEM_PRICE"]."<br />";
   }
   echo "</td></tr></table>";
}

Benchmarking

Finally, review the results and decide which method would be the most efficient way to produce reports for your system. Keep in mind, however, that no two systems are identical and there are many factors to consider while optimizing for best performance. Some factors may be database related (like fine-tuning the indexes), some may involve the PHP or network optimization, and so on. As to the simple system discussed here, feel free to draw your own conclusions by looking at the tables below:

200 orders

1,000 orders

Conclusion

There are, of course, more ways to provide same functionality--for example, by using an Oracle stored procedure with REF CURSORs to output the data. As you can see, in this particular test, it turned out that using stand-alone sub-queries would provide better performance. However, this test is by no means comprehensive and I encourage you to use the code presented in this recipe to create your own tests and find out which method works best for you.

Send us your comments