Accessing Databases with ADODB - Pivot Tables

(Page 10 of 12 )

Another handy feature of ADODB is its generation of SQL for pivot tables. Also known as cross-tabs (which, in turn, is short for cross-tabulations), pivot tables are a way of summarizing totals across multiple categories. You can create a pivot table that summarizes how many of each flavor various ice cream eaters have ordered. This requires the introduction of two new tables: eaters, which holds information about people eating ice cream, and orders, which holds information about which flavors the eaters order to eat. These tables look like this:

CREATE TABLE eaters (
   id int(10) unsigned NOT NULL,
   name varchar(255) default NULL,
   PRIMARY KEY (id)
);
CREATE TABLE orders (
   eater_id int(11) default NULL,
   ice_cream_id int(11) default NULL
);

Both tables need to be populated with some sample data:

INSERT INTO eaters VALUES (1,'Ireneo Funes');
INSERT INTO eaters VALUES (2,'Bioy Casares');
INSERT INTO eaters VALUES (3,'John Vincent Moon');
INSERT INTO eaters VALUES (4,'Teodelina Villar');
INSERT INTO orders VALUES (1,2);
INSERT INTO orders VALUES (1,3);
INSERT INTO orders VALUES (1,4);
INSERT INTO orders VALUES (2,4);
INSERT INTO orders VALUES (2,3);
INSERT INTO orders VALUES (3,1);
INSERT INTO orders VALUES (4,1);
INSERT INTO orders VALUES (4,4);
INSERT INTO orders VALUES (2,4);
INSERT INTO orders VALUES (3,1);

The following example generates a pivot table summarizing how many of each flavor each person has ordered. The record set that holds the results has one row for each flavor and one column for each person. It also has a column for the total number of orders per flavor. The PivotTableSQL() function generates the SQL query that creates the record set that holds the pivot table results. You need to pass it five arguments. The first is a connection handle. The second is a string containing a comma-separated list of tables from which to retrieve data. The third argument is the name of the database field that should be used for the rows of the table, and the fourth argument is the name of the field that should be used for the columns of the table. The last argument is the appropriate SQL to link together the different tables in the query. In this example, you join eaters and orders on eaters.id and orders.eater_id, and you join ice_cream and orders on ice_cream.id and orders.ice_cream_id. The PivotTableSQL() function is defined in a separate file, pivottable.inc.php, so you require that at the beginning of the example:

// Load the pivot table code
require 'adodb/pivottable.inc.php';
// Load the rs2html code
require 'adodb/tohtml.inc.php';

// Connect to the database
$conn = &ADONewConnection('mysql');
$conn->connect('localhost','phpgems','phpgems1','phpgems');

// Generate the query
$sql = PivotTableSQL($conn,
                'eaters, ice_cream, orders',
                'ice_cream.flavor',
                'name', 
                'eaters.id = orders.eater_id and ' . 
                'ice_cream.id = orders.ice_cream_id');

// Send the query to the database and get a record set
$rs = $conn->execute($sql);
// Display the results
rs2html($rs);

Figure 2-7 shows the pivot table from this example.

Figure 2-7. A pivot table summarizes totals across categories.

Each row contains the number of orders each person placed for that row’s flavor, as well as the total number of orders for that flavor. Each column contains the number of orders that column’s person placed for each flavor.

Instead of passing PivotTableSQL() a single column name as a fourth argument, you can also pass an array of labels and SQL expressions. PivotTable() treats each of the labels as column headers and uses the SQL expressions to determine what values are grouped into each column. For example, the following code summarizes each person’s orders grouped into calorie ranges:

$sql = PivotTableSQL($conn, 
         'eaters, ice_cream, orders', 
         'name', 
        array('diet' => 'calories > 0 and calories <= 20', 
         'fatty' => 'calories > 30', 
         'bland' => 'calories = 0'), 
         'eaters.id = orders.eater_id and ice_cream.id = orders.ice_cream_id');

$rs = $conn->execute($sql);
rs2html($rs);

Figure 2-8 shows the pivot table this code generates.

Figure 2-8. A pivot table can use custom labels for categories.

In this pivot table, each row contains the number of orders placed by that row’s person for a flavor whose calories fall into each category: diet for flavors with more than 0 but fewer than or equal to 20 calories, fatty for flavors with more than 30 calories, and bland for flavors with no calories. The total column contains the total number of orders placed by the row’s person.

This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now.

Next: Caching >>
 

More Database Articles
More By Apress Publishing

Comments On: Accessing Databases with ADODB

· i want the programming code for the add delete update useing the adodb ontrols.   >>> Post your comment now!