Click here to Skip to main content
12,241,814 members (45,259 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

9.7K views
225 downloads
7 bookmarked
Posted

Cross of fields in a table of Data Base accessible via PDO

, 10 Aug 2015 CPOL
Rate this:
Please Sign up or sign in to vote.
CrossData is a PHP object for creating an HTML table or a PHP array, crossing two fields of a table from a PDO accessed DataBase.

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 Data Base 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 others implementation including new object properties, an improved access to the data base 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 be also 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 reader interested 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 whith 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);
?>

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 this arguments:

  • cell row coordinate
  • cell column coordinate
  • array of data
  • row head (the array doesn't contains the heads of rows)
  • column head (the array doesn't contains 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 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Member 4206974
Software Developer Condor Informatique
Italy Italy
Computer literacy (software) : Languages: PHP, Javascript, SQL Autoit,Basic4Android; Frameworks: JOOMLA!
Teaching/Training skills on Office, WEB site development and programming languages.
Others : WEB site development.
UNDP Missions
feb – may 2003 Congo DR Bukavu: ground IT computer course
nov 2003 Burundi Bujumbura: Oracle Data Base course
feb 2005 Burundi Bujumbura: JAVA course
mar 2005 Mali Kati: MS Office course
oct 2006 Mali Kati: MS Office course
jun 2006 Burkina Faso Bobo Dioulasso: MS Office course
jun 2007 Burkina Faso Bobo Dioulasso: MS Office course
may 2007 Argentina Olavarria hospital: Internet application for access to medical records
apr 2008 Burkina Faso Ouagadougou: MS ACCESS and dynamic Internet applications
jun 2008 Niger Niamey: analysis of the computing needs of the Niamey hospital
may 2009 Burkina Faso Ouagadougou: MS ACCESS and dynamic Internet applications
oct 2010 Niger Niamey: analysis of the computing needs of the Niamey hospital (following)
Region Piedmont project Evaluation
mar 2006 Burkina Faso, Niger
mar 2007 Benin, Burkina Faso, Niger
sep 2008 Benin, Burkina Faso, Niger
Others
feb 2010 Burundi Kiremba hospital: MS Office course
feb 2011 Congo DR Kampene hospital: MS Office course

You may also be interested in...

Comments and Discussions

 
Questionproject Pin
phpsystems13-Nov-15 21:26
memberphpsystems13-Nov-15 21:26 
AnswerRe: project Pin
Member 420697413-Nov-15 21:54
memberMember 420697413-Nov-15 21:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 10 Aug 2015
Article Copyright 2013 by Member 4206974
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid