PHP Classes

File: exportComplexQueries.php

Recommend this page to a friend!
  Classes of Charles A. LaFleur   Export Spreadsheets   exportComplexQueries.php   Download  
File: exportComplexQueries.php
Role: Example script
Content type: text/plain
Description: Demonstrates use of complex queries and row formulas
Class: Export Spreadsheets
Create Excel spreadsheet from data in MySQL table
Author: By
Last change:
Date: 12 years ago
Size: 3,460 bytes
 

Contents

Class file image Download
<?php
// include the export_spreadsheet_class
require_once ("export_spreadsheets_class.php");

// create an instance of the export_spreadsheet_class object
$myExport = new exportSpreadsheets();

// set instance properties
 
$myExport->mysqlServer = "localhost";
 
$myExport->mysqlUsername = "exportUser";
 
$myExport->mysqlPassword = "exportPassword";
 
$myExport->mysqlDatabaseName = "export_spreadsheets";

$workbook_array = array ();

$workbook_array['Best Customers'] = array (
'connection' => array ('mysqlQuery'=>'select Concat(RTrim(customers.firstName), " ", RTrim(customers.lastName)) as customer, sum(orders.totalPrice) as revenue from customers, orders where orders.customerID=customers.id group by lastName, firstName order by revenue desc'),
'sheet' => array ('pageTitle'=>'Best Customers', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' ),
'column1' => array ('fieldName'=>'customer', 'columnTitle'=>'Customer', 'alignment'=>'left','sum'=>'no'),
'column2' => array ('fieldName'=>'revenue', 'columnTitle'=>'Total Revenue', 'numberFormat'=>'0.00', 'sum'=>'yes')
);

$workbook_array['Most Popular Products'] = array (
'connection' => array ('mysqlQuery'=>'select products.name, sum(orders.orderQuantity) as totalQuantity, sum(orders.totalPrice) as revenue from products, orders where orders.productID=products.id group by productID order by totalQuantity desc'),
'sheet' => array ('pageTitle'=>'Most Popular Products', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' ),
'column1' => array ('fieldName'=>'name', 'columnTitle'=>'Product Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ('fieldName'=>'totalQuantity', 'columnTitle'=>'Units Sold', 'sum'=>'no'),
'column3' => array ('fieldName'=>'revenue', 'columnTitle'=>'Total Revenue', 'numberFormat'=>'0.00','sum'=>'yes')
);

$workbook_array['Price Changes'] = array (
'connection' => array ('mysqlQuery'=>'select products.name, sum(orders.orderQuantity) as totalQuantity, orders.unitPrice from products, orders where orders.productID=products.id group by productID, unitPrice order by name asc, unitPrice desc'),
'sheet' => array ('pageTitle'=>'Sales Volume Compared to Price', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' ),
'column1' => array ('fieldName'=>'name', 'columnTitle'=>'Product Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ('fieldName'=>'totalQuantity', 'columnTitle'=>'Units Sold', 'sum'=>'no'),
'column3' => array ('fieldName'=>'unitPrice', 'columnTitle'=>'Unit Price', 'numberFormat'=>'0.00','sum'=>'no'),
'column4' => array ('fieldName'=>'rowFormula', 'formula'=>'=IF(ISBLANK(column2)+ISBLANK(column3),"",column2*column3)', 'columnTitle'=>'Revenue', 'numberFormat'=>'0.00','vAlign'=>'top', 'sum'=>'yes')
);

$workbook_name = "complex.xls";
$myExport->export_spreadsheets($workbook_name,$workbook_array);

?>