The Oracle + PHP Cookbook

Creating Online Reports with Oracle and PHP


by Mikhail Seliverstov

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:

figure 1

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;
/

                              
And a sequence to make it simple to come up with a unique primary key whenever a row is inserted in the Order_Items table:
                                 
CREATE SEQUENCE ORDER_LINE_SQ
INCREMENT BY 1;
/

                              
And here's the code to populate the test data. Assume that the CUSTOMERS table is pre-populated with the customer CUSTOMER_ID = 1 and the ITEMS table contains 15 items with ITEM_IDs from 1 to 15:
                                 
//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);
  }
}

                              
At this point the ORDERS table should be populated with 200 orders placed by customer 1. Each order comes with 15 items in the ORDER_ITEMS table and 4 statuses in the ORDER_TRACKING table.

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.

Simple sub-queries. This approach is, no doubt, the most traditional way of building complex reports. In this example you will execute a simple query to retrieve the list orders for given customer id and then loop through the list of orders and execute two sub-queries, one to get the status change history and the other to get the item list. The results will be stored in an associative array $arr_orders and later presented as an HTML table.

                                 
$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;
}

                              
Simple sub-queries with variable binding. This approach is similar to the previous technique; it also relies on executing stand-alone database queries in a loop. However, this time you will use variable binding.

In theory, using variable binding should result in faster query execution, since Oracle wouldn't have to parse each and every new statement. Also, binding is generally safer, since it reduces the risk of a SQL injection attack.

                                 
$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;
}
Single query with Oracle CURSOR sub-queries. The third approach involves using Oracle's CURSOR expressions in a single database call. Even though there is still quite a bit of looping involved, this time, instead of querying the original database tables you will query the collections included in the initial call.
                                 
$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

Attempt # Method 1, sec. Method 2, sec. Method 3, sec.
1 4.54 4.31 5.48
2 4.09 4.33 5.40
3 4.46 4.40 5.36
4 4.39 4.41 5.38
5 4.12 4.50 5.70

1,000 orders

Attempt # Method 1, sec. Method 2, sec. Method 3, sec.
1 33.51 37.99 34.15
2 29.16 37.76 33.73
3 29.11 38.19 35.60
4 28.84 37.79 44.18
5 29.13 38.67 37.29

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.


Mikhail Seliverstov is a Web Programming Team Leader for the office of Development and Alumni Relations at McGill University in Montreal. For the past three years, he has helped lead a large Oracle+PHP development project as a principal application architect and developer.

Send us your comments