65.9K
CodeProject 正在变化。 阅读更多。
Home

通过 PDO 访问的数据库表中字段的交叉

会员 4206974
starIconstarIconstarIconstarIconstarIcon

5.00/5 (5投票s)

2013年12月2日

CPOL

5分钟阅读

viewsIcon

34478

downloadIcon

328

CrossData 是一个 PHP 对象，用于从 PDO 访问的数据库的一个表中的两个字段创建 HTML 表格或 PHP 数组。

引言

本文是对文章 "Crossing fields of a MySQL table" 的更新，反映了该包的主要演变，主要是为了替换对 MySQL 数据库的访问（PHP 5.5.x 中已弃用的功能）。这也提供了一个机会，将此功能扩展到可以通过 PDO[1] 访问的数据库以及一些其他实现，包括新的对象属性、改进的数据库访问以及数据的呈现。

CrossData 是一个 PHP 对象，用于从表的两个字段创建 HTML 表格或交叉数据数组。交叉可以从简单的计数或百分比到对第三个字段的某些分组函数。

该脚本可以包含在 PHP 应用程序中，也可以与 JOOMLA! 和 WordPress 一起使用。

背景

CrossData 旨在在 PDO 环境中实现 MS Access 中的 TRANSFORM 函数（语法更简单）。

Using the Code

感兴趣的读者可以在下载文件中找到完整的文档。

要使用 CrossData，我们必须有一个已打开的 PDO 数据库，包含 crossdata.php 脚本，创建一个对象，然后调用交叉函数。

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

在查看 cross 命令的语法之前，必须说明 Cross 函数可以有两个可选参数：标题和布尔值，用于在获取数据数组而不是格式化表格。在示例（以及 演示）中，表格结构是

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*'*，该字段是平均值的对象。

对于带百分比的操作，我们可以有一些替代方案

  • % 返回为总计的百分比。
  • % SUM 第三个字段的总和占总计的百分比。
  • % ROWS 第三个字段的总和占每行总计的百分比。
  • % COLS 第三个字段的总和占每列总计的百分比。

平均值和百分比可以有小数，默认值为 2，这可以通过对象属性 precision 来更改。第二个属性 ifEmpty 用于控制在找到空值时数据的表示，而（新的）属性 decPoint 和 thousandsSep 控制数字的表示。

CrossData 示例

简单示例

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");

在这种情况下，第一行、第一列、最后一行和最后一列的标题是字段名（Product、Seller）和 COUNT，此外，标题是自动构建的（试用）。

在下面的代码中，强制使用了自定义标题和标题（试用）

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");

请注意，最后一行和最后一列的标题是“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 操作符

Field 操作符可用于列出字段对于两个数据库字段的每个组合所假设的不同值，例如

CROSS Product BY Seller FIELD Town FROM orders

对于 product 和 seller 的每个组合，都有一个 seller 已销售 product 的城市列表。

高级

计算字段

我们可以计算数据库字段，但考虑到命令的（基础）扫描，它们必须用撇号括起来或不带空格编写，例如

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

(试用)

与 JOOMLA! 和 WordPress 一起使用

要与 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 指令。

使用回调函数

回调函数允许干预数据的呈现：函数必须具有以下参数

  • 单元格行坐标
  • 单元格列坐标
  • 数据数组
  • 行头（数组不包含行头）
  • 列头（数组不包含列头）
<!--?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);
?>

表格样式

CrossData 创建的 HTML 表格的类名为 CDTable；此外，表格有一个包含标题的标题，第一行是 THEAD 行。

CrossData 会插入样式 text-align:right（如果数据是数字）和 vertical-align:top;text-align:center（用于 THEAD 行）。如果我们不想进行任何样式设置，则必须将属性 noStyle=true 设置为 true。

这是表格样式的一个示例。

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

注释

  1. ^ 参见 PDO Drivers

历史

  • 2022年4月11日：初始版本
  • 2022年9月22日：通过两个回调更新源代码：用于行头的回调和用于列头的回调
© . All rights reserved.