在PHP中处理数据库数据时,经常需要对数据进行交叉统计分析。本文介绍的是一个PHP对象,它能够从数据库表的两个字段生成交叉数据的HTML表格或数组。这种交叉统计可以是简单的计数、百分比,也可以是第三字段上的一些分组函数。
这个工具可以被集成到PHP应用程序中,并且也可以在JOOMLA!和WordPress中使用。
CrossData对象旨在PDO环境中实现MS Access中的TRANSFORM函数的功能(具有更简单的语法)。
感兴趣的读者可以在下载的文件中找到完整的文档。要使用CrossData,必须已经打开了一个PDO数据库,包含crossdata.php脚本,创建一个对象,并调用交叉函数:
include 'crossdata.php';
$crObj = new CrossData;
$dbh = new PDO('mysql:host=localhost;dbname=myDBName', 'user', 'password');
$crObj->dbh = $dbh;
echo $crObj->Cross(crossDataCommand);
在查看cross命令的语法之前,必须说明Cross函数可以有两个可选参数:标题和一个布尔值,用于获取数据数组而不是格式化表格。在示例(以及demo)中,表结构如下:
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
);
Cross请求的形式(没有大小写敏感性):
CROSS data_1 BY data_2 [Operation] FROM dataStore
首先检查组件,从dataStore开始。
dataStore:因为在FROM子句之后,没有语法控制,所以可以在这里放置SQL可以接受的内容,特别是可以是一个表名,一个JOIN子句,或者一个SELECT,当然,还有一个WHERE子句;
data_1和data_2:它们是dataStore的字段,成为行和列的标题(可以通过在字段名称后面添加这个来强加不同的标题:例如,CROSS Product 'Products sold' BY Towns FROM orders(如果里面有空格,只需要使用单引号));
operation:是可选的,但使能够超越简单的计数:可以有一个SQL分组函数在第三个(数字)字段上,例如,CROSS Product BY Seller AVG Sold 'Average sold',或者一个关键字FIELD用于列出第三个字段对于data_1和data_2的每个组合所假设的不同值。注意在平均字段名称之后有一个可选的标签'Average sold'。
对于涉及百分比的操作,有一些选择:
平均值和百分比可以有小数,其默认值为2,这可以通过对象属性precision来更改。第二个属性ifEmpty用于控制在找到空值时数据的呈现,而(新)属性decPoint和thousandsSep控制数字的呈现。
简单示例:
include 'crossdata.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");
在这种情况下,第一行、第一列、最后一行和最后一列的标题是字段的名称(Product,Seller)和COUNT,此外,标题是自动构建的(尝试)。
在下面的代码中,它强加了自定义标题和标题(尝试):
include 'crossdata.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");
注意最后一行和列由Total number标题,第二个Seller是列名的标签。
计算是一些GROUP BY函数,即SUM、AVG、MIN和MAX,以及应用于计数或SUM的百分比。下面(尝试)是创建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);
涉及百分比的计算与指定字段的总和有关,百分比是与总一般(% SUM)或每行的总计数(% ROWS)或列(% COLS)的总计数有关的。
$crObj->dbh = $dbh;
$crObj = new CrossData;
echo $crObj->Cross("CROSS Product BY Seller % cols Sold FROM orders");
FIELD操作符可以用来列出一个字段对于两个数据库字段的每个组合所假设的不同值,例如:
CROSS Product BY Seller FIELD Town FROM orders
对于每个产品和卖家的组合,都有一个列表,显示卖家在哪些城市销售了产品。
计算字段
可以计算数据库字段,但由于命令的扫描(基本的),它们必须用单引号括起来或不包含空格书写,例如:
CROSS Product BY Seller % sum 'sold * 0.5' Fees FROM orders
CROSS Product BY Seller % sum sold*0.5 Fees FROM orders
(尝试)
要在JOOMLA!中使用CrossData,需要Sourcerer®插件,它允许在文章中插入JavaScript和PHP脚本、HTML标签和CSS;下面是在Joomla! 2.5中插入CrossData的示例。
{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}
注意不能使用JOOMLA!处理数据的设施,但需要打开数据库(可能还需要获取表前缀)。
可以通过插件Exec-PHP在WordPress页面中添加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 "" . $aData[$r][$c] . "";
if ($perc < 30)
return "" . $aData[$r][$c] . "";
}
return " " . $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);
CrossData创建的HTML表具有类名CDTable;此外,表有一个标题,标题包含标题,并且第一行是一个THEAD行。CrossData插入样式text-align:right如果数据是数字的,并且vertical-align:top;text-align:center对于THEAD行。如果不进行任何样式设置,必须设置属性noStyle=true。
以下是样式表的示例。
<style>
.CDTable td, .CDTable th {
border: 1px solid black;
padding: 2px 3px;
}
.CDTable td {
font: normal 10pt Arial;
}
.CDTable th, .CDTable 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>