Click here to Skip to main content
Click here to Skip to main content

Crossing fields of a MySQL table

By , 2 Dec 2013
Rate this:
Please Sign up or sign in to vote.

Cross percent Rows

Introduction

CrossData is a PHP object for creating an HTML table or an array of crossed data from two fields of a MySQL 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! CMS.

Background

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

Using the code

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

$link = mysql_connect (hostName, userName, password) or die ("No connection" . mysql_error());
mysql_select_db(dbName,$link);
...
include 'crossadata.php';
$crObj = new CrossData;
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

Begin by examining the components, starting with dataStore.

  • dataStore: because after FROM clause there is no syntax control, it is possible to put which SQL can be accepted, 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 data from dataStore, besides we can impose a heading appending a name, e.g., CROSS Product 'Products<br>selled' BY town 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 percentage, we can have some alternatives:

    • % data 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 or zero value is found.

CrossData by examples

Simple examples

include 'crossadata.php';
$crObj = new CrossData;
$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, besides the title is built automatically (try).

In the code below, we impose our heading and title (try):

include 'crossadata.php';
$crObj = new CrossData;
echo $crObj->Cross("CROSS Product Products BY Seller Sellers 'Total number' FROM orders","Products by Sellers");

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.

$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).

include 'crossadata.php';
$crObj = new CrossData;
echo $crObj->Cross("CROSS Product BY Seller % cols Sold FROM orders");

On 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.

Tricks and caveats

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

Use with JOOMLA!

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).

Styling table

The HTML table created by CrossData has the class name CDTable; besides the table has a caption which contains the title, and the first row is a THEAD row. 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>

License

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

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

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 2 Dec 2013
Article Copyright 2013 by Member 4206974
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid