Cross of Fields in a Table of Database Accessible via PDO

• Download source and documentation - 306 KB
• Try demo project (external link)

Introduction

This article is an update of the article, Crossing fields of a MySQL table, that reflects the evolution of the package primarily to replace the access to the database MySQL (deprecated features in PHP 5.5.x). This was also an opportunity to extend this functionality to the databases accessible via PDO^[1] and some other implementation including new object properties, an improved access to the database and on the presentation of the data.

CrossData is a PHP object for creating an HTML table or an array of crossed data from two fields of a table. Crossing is from a simple count of occurrence or percentage to some group functions on a third field.

The script can be included in a PHP application and it can also be used with JOOMLA! and WordPress.

Background

CrossData wants to realize in the PDO environment the function TRANSFORM present in MS Access (with a simpler syntax).

Using the Code

The interested reader can find a complete documentation on the file to download.

For using CrossData, we must have a PDO database already open, include the crossdata.php script, create an object, and call the crossing function:

include 'crossadata.php';
$crObj = new CrossData;
$dbh = new PDO('mysql:host=localhost;dbname=myDBName',"user","password");
$crObj->dbh = $dbh;
echo $crObj->Cross(crossDataCommand);

Before we see the syntax of the cross command, it must be told which Cross function can have two optional parameters: title and a boolean for obtaining an array of data instead of a formatted table. In the samples (and in the demo), the table structure is:

CREATE TABLE IF NOT EXISTS 'orders' (
  'Town' varchar(25) NOT NULL,
  'Seller' varchar(20) NOT NULL,
  'Product' varchar(50) DEFAULT NULL,
  'Price' int(7) DEFAULT NULL,
  'Qty' decimal(5,2) DEFAULT NULL,
  'Sold' int(10) DEFAULT NULL
)

The Cross request has the form (there is no case sensitivity):

CROSS data_1 BY data_2 [Operation] FROM dataStore

I begin by examining the components, starting with dataStore.

• dataStore: Because after FROM clause, there is no syntax control, it is possible to put what SQL can accept here, in particular, there can be a table name, a JOIN clause, or a SELECT and, of course, a WHERE clause;
• data_1 and data_2: They are fields from dataStore and become the header of the rows and columns (we can impose a different header by adding this to the name of the field: e.g., CROSS Product 'Products sold' BY Towns FROM orders (apostrophes are necessary only if there are spaces inside));
• operation: is optional, but enables us to go beyond simple counting: we can have a SQL group function on a third (numeric) field, e.g., CROSS Product BY Seller AVG Sold 'Average sold', or a keyword FIELD for listing the different values which the third field assumes for every combination of data_1 and data_2. Note the optional label 'Average sold' after the name of the field subject of the average.

For operation with percentage, we can have some alternatives:

• % returned as a percentage of total count.
• % SUM percentage of the sum of the third field with respect to the total.
• % ROWS percentage of the sum of the third field with respect to the total of every row.
• % COLS percentage of the sum of the third field with respect to the total of every column.

Average and percentage can have decimals, whose default is 2, this can be changed by the object property precision. A second property ifEmpty is used for controlling the presentation of the data when an empty value is found and the (new) properties decPoint and thousandsSep control the number presentation.

CrossData by Examples

Simple Examples

include 'crossadata.php';
$crObj = new CrossData;
$dbh = new PDO('mysql:host=localhost;dbname=myDBName',"user","password");
$crObj->dbh = $dbh;
$crObj->ifEmpty = "---";
echo $crObj->Cross("CROSS Product BY Seller FROM orders");

In this case, the heading of the first row, first column, last row, and last column are the names of the fields (Product, Seller) and COUNT, moreover the title is built automatically (try).

In the code below, it is imposed a custom heading and title (try):

include 'crossadata.php';
$crObj = new CrossData;
$dbh = new PDO('mysql:host=localhost;dbname=myDBName',"user","password");
$crObj->dbh = $dbh;
echo $crObj->Cross("CROSS Product Products BY Seller Sellers 'Total number' FROM orders",_
                   "Products by Sellers");

Note the last row and column are headed by Total number, the second Seller is a label for the column names.

Calculations

Calculations are some GROUP BY functions, i.e., SUM, AVG, MIN, and MAX, and percentage applied at count or SUM. Below (try) is the script for creating a table of average on the field Sold.

$dbh = new PDO('mysql:host=localhost;dbname=myDBName',"user","password");
$crObj->dbh = $dbh;
$cross = "CROSS Product BY Seller AVG Sold 'Average sold' FROM orders";
$crObj = new CrossData;
$crObj->precision = 0;
echo $crObj->Cross($cross);

Calculations involving the percentage are related to the sum of the specified field and the percentages are with respect to the total general (% SUM) or to the totals for each row (% ROWS) or column (%COLS).

$crObj->dbh = $dbh;
$crObj = new CrossData;
echo $crObj->Cross("CROSS Product BY Seller % cols Sold FROM orders");

The FIELD Operator

Field operator can be used to list the different values which a field assumes for every combination of two database fields, e.g.:

CROSS Product BY Seller FIELD Town FROM orders

For each combination of product and seller, there is a list of cities in which the seller has sold the product.

Advanced

Calculated Field

We can calculate database fields, but given the scanning (rudimentary) of the command, they must be enclosed in apostrophes or written without spaces, e.g.:

CROSS Product BY Seller  % sum 'sold * 0.5' Fees FROM orders
CROSS Product BY Seller  % sum sold*0.5 Fees FROM orders

(try)

Use with JOOMLA! and WordPress

For using CrossData with JOOMLA!, we need the Sourcerer® plug-in which permits to insert JavaScript and PHP scripts, HTML tags, and CSS in an article; below is the sample for inserting CrossData in Joomla! 2.5.

{source}
<?php
    include 'musei/crossdata.php';
    // access data base
    $dbname = "joomla257";
    $linkdb = mysql_connect ("localhost", "root", "") or 
                        die ("No connessione" . mysql_error());
    mysql_select_db($dbname,$linkdb);
    // get table prefix
    $app = JFactory::getApplication();
    $prefix = $app->getCfg('dbprefix'); 
    $cross = "CROSS Product BY Seller FIELD Town FROM {$prefix}orders";
    $crObj = new CrossData;
    $crObj->ifEmpty = "-";		// if empty print -
    echo $crObj->Cross($cross,"Town where products was sold by seller");
    mysql_close($linkdb);
?>
{/source}

Note we can't use the JOOMLA! facilities for handling data, but we need to open the database (and possibly take the table prefix).

We can add PHP instruction in WordPress pages, by the plugin Exec-PHP.

Use of Callback Function

The callback function allows to intervene on the presentation of the data: the function must have these arguments:

• Cell row coordinate
• Cell column coordinate
• Array of data
• Row head (the array doesn't contain the heads of rows)
• Column head (the array doesn't contain the heads of columns)

<!--?PHP
function colorize($r,$c,$aData,$hr,$hc) {
  $hTot = $aData[$r][count($aData[$r])-1];		  // last row cell
  if ($hTot ----><?PHP
function colorize($r,$c,$aData,$hr,$hc) {
  $hTot = $aData[$r][count($aData[$r])-1];        // last row cell
  if ($hTot > 0 && $c != count($aData[$r])-1 && $r != count($aData)-1) {
    $perc = floor($aData[$r][$c]*100/$aData[$r][count($aData[$r])-1]);
    if ($perc > 60) return "<span style='color:blue'>".$aData[$r][$c]."</span>";
    if ($perc < 30) return "<span style='color:red'>".$aData[$r][$c]."</span>";
  }
  return "&nbsp;".$aData[$r][$c];
}
include 'crossdata.php';
$dbh = new PDO('sqlite:orders.sqlite');
$cross = "CROSS Seller BY Town SUM Sold/1000 'Thousands sold' FROM orders";
$crObj = new CrossData;
$crObj->dbh = $dbh;
$crObj->precision = 0;
$crObj->callBack = "colorize";
echo $crObj->Cross($cross);
?>

Style Table

The HTML table created by CrossData has the class name CDTable; moreover the table has a caption which contains the title, and the first row is a THEAD row.

CrossData inserts a style text-align:right if the data are numeric and vertical-align:top;text-align:center for the THEAD row. If we won’t do any styling, we must set the property noStyle=true.

Here is a sample of the styling table.

<style>
.CDTable td, th {border: 1px solid black;padding:2px 3px}
.CDTable td {font: normal 10pt Arial}
.CDTable th, caption {font: bold 11pt Arial;text-align: center;
             padding:2px 3px 5px 3px;vertical-align:top}
.CDTable tr:nth-child(2n+2)	{background-color:#eee;}
.CDTable tr:nth-child(2n+3)	{background-color:#ffffff;}
.CDTable tr:nth-child(1)	{background-color:#ccc;}
</style>

Notes

1. ^ See PDO Drivers

History

• 11^th April, 2022: Initial version
• 22^nd September, 2022: Updated source by two callbacks: Callback for rows headers and Callback for columns headers