phpeveryday.com

PHP ADOdb Reporting Tutorial: In this post, we will discuss about how to create pivot table or cross tabulation in PHP Adodb. Before read this post, make sure you understand and read this post.

For this practice, we need 3 table: products, cities, and sales. You can create from this query:

 -- 
 -- Table structure for table `cities`
 -- 
 
 CREATE TABLE `cities` (
   `id` int(11) NOT NULL auto_increment,
   `city` varchar(150) NOT NULL,
   PRIMARY KEY  (`id`)
 );
 
 -- --------------------------------------------------------
 
 -- 
 -- Table structure for table `products`
 -- 
 
 CREATE TABLE `products` (
   `id` int(11) NOT NULL auto_increment,
   `product` varchar(150) NOT NULL,
   PRIMARY KEY  (`id`)
 );
 
 -- --------------------------------------------------------
 
 -- 
 -- Table structure for table `sales`
 -- 
 
 CREATE TABLE `sales` (
   `id` int(11) NOT NULL auto_increment,
   `date` date NOT NULL,
   `cities` int(11) NOT NULL,
   `products` int(11) NOT NULL,
   `amount` int(11) NOT NULL,
   PRIMARY KEY  (`id`)
 );
 
 

Now, import this data:

 
 INSERT INTO `cities` (`id`, `city`) 
   VALUES (1, 'Stockholm');
 INSERT INTO `cities` (`id`, `city`) 
   VALUES (2, 'Paris');
 INSERT INTO `cities` (`id`, `city`) 
   VALUES (3, 'Moscow');
 INSERT INTO `cities` (`id`, `city`) 
   VALUES (4, 'New Delhi');
 
 
 INSERT INTO `products` (`id`, `product`) 
  VALUES (1, 'Notebook');
 INSERT INTO `products` (`id`, `product`) 
  VALUES (2, 'PC');
 INSERT INTO `products` (`id`, `product`) 
  VALUES (3, 'Table Pc');
 INSERT INTO `products` (`id`, `product`) 
  VALUES (4, 'PDA');
 
 
 INSERT INTO `sales` (`id`, `date`, `cities`, 
 `products`, `amount`) VALUES (1, '2008-01-01', 1, 2, 3);
 INSERT INTO `sales` (`id`, `date`, `cities`, 
 `products`, `amount`) VALUES (2, '2008-01-01', 2, 1, 4);
 INSERT INTO `sales` (`id`, `date`, `cities`, 
 `products`, `amount`) VALUES (3, '2008-01-02', 3, 4, 4);
 INSERT INTO `sales` (`id`, `date`, `cities`, 
 `products`, `amount`) VALUES (4, '2008-01-02', 4, 3, 5);
 INSERT INTO `sales` (`id`, `date`, `cities`, 
 `products`, `amount`) VALUES (5, '2008-01-03', 1, 4, 3);
 INSERT INTO `sales` (`id`, `date`, `cities`, 
 `products`, `amount`) VALUES (6, '2008-01-03', 4, 1, 6);
 


Ok, we start creating pivot table. Create folder named "adodb" within www/test. Put adodb library in this directory (so, you get www/test/adodb/adodb -> this is library folder).

Now, create a file name "pivot.php" within www/test/adodb. Enter following code:

 <?php 
     include('adodb/adodb.inc.php'); 
     include('adodb/tohtml.inc.php'); 
     include('adodb/pivottable.inc.php'); 	
 		
     $db = ADONewConnection('mysql'); 
     $db->Connect('localhost', 'root', 'admin', 'test'); 
 
 	$sql = PivotTableSQL(
 			$db, 'cities c, products  p, sales s',
 			'city',
 			'product',
 			's.products = p.id and s.cities = c.id'
 		 );
 	$recordset = &$db->Execute($sql);
 	
 	rs2html($recordset);
 	
 	$recordset->Close();
 
 ?>


Point your browser to http://localhost/test/adodb/pivot.php
    
    

	
    
Series this article: 
ADOdb: Introduction
ADOdb: Connection Statement
ADOdb: Advance Select Statement
ADOdb: How to show tables
ADOdb: How to show fields
ADOdb: How to show databases
ADOdb: Caching of Recordset
ADOdb: Recordset to HTML
ADOdb: Multi Database Connection
ADOdb: Data Dictionary
ADOdb: Quick Export  Data
ADOdb: Insert data style
ADOdb: Replace Data
ADOdb: Log Query
PHP ADOdb: Understanding Pivot Table For Reporting
PHP ADOdb: Creating Pivot Table
PHP ADOdb: Creating Query to Build Pivot Table