本文整理汇总了PHP中PivotTableSQL函数的典型用法代码示例。如果您正苦于以下问题：PHP PivotTableSQL函数的具体用法？PHP PivotTableSQL怎么用？PHP PivotTableSQL使用的例子？那么恭喜您, 这里精选的函数代码示例或许可以为您提供帮助。
在下文中一共展示了PivotTableSQL函数的5个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: PivotTableSQL
SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce",
SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood",
SUM(1) as Total
FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID
GROUP BY CompanyName,QuantityPerUnit
*/
//=====================================================================
# example2
#
# Query the main "product" table
# Set the rows to CompanyName and QuantityPerUnit
# and the columns to the UnitsInStock for diiferent ranges
# and define the joins to link to lookup tables
# "categories" and "suppliers"
#
$sql = PivotTableSQL($gDB, 'products p ,categories c ,suppliers s', 'CompanyName,QuantityPerUnit', array(' 0 ' => 'UnitsInStock <= 0', "1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5', "6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10', "11 to 15" => '10 < UnitsInStock and UnitsInStock <= 15', "16+" => '15 < UnitsInStock'), ' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', 'UnitsInStock', 'Sum');
print "<pre>{$sql}";
$rs = $gDB->Execute($sql);
rs2html($rs);
/*
Generated SQL:
SELECT CompanyName,QuantityPerUnit,
SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum 0 ",
SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5",
SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10",
SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15",
SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+",
SUM(UnitsInStock) AS "Sum UnitsInStock",
SUM(1) as Total
FROM products p ,categories c ,suppliers s WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID 示例2: testdb
//.........这里部分代码省略.........
print "<b>Error - Replace failed, count={$cnt}</b><p>";
}
$ADODB_COUNTRECS = $savec;
}
$ret = $db->Replace('ADOXYZ', array('id' => 1000, 'firstname' => 'Harun', 'lastname' => 'Al-Rashid'), array('id', 'firstname'), $autoq = true);
if ($ret != 2) {
print "<b>Replace failed: </b>";
}
print "test A return value={$ret} (2 expected) <p>";
$ret = $db->Replace('ADOXYZ', array('id' => 1000, 'firstname' => 'Sherazade', 'lastname' => 'Al-Rashid'), 'id', $autoq = true);
if ($ret != 1) {
if ($db->dataProvider == 'ibase' && $ret == 2) {
} else {
print "<b>Replace failed: </b>";
}
}
print "test B return value={$ret} (1 or if ibase then 2 expected) <p>";
print "<h3>rs2rs Test</h3>";
$rs = $db->Execute('select * from ADOXYZ where id>= 1 order by id');
$rs = $db->_rs2rs($rs);
$rs->valueX = 'X';
$rs->MoveNext();
$rs = $db->_rs2rs($rs);
if (!isset($rs->valueX)) {
err("rs2rs does not preserve array recordsets");
}
if (reset($rs->fields) != 1) {
err("rs2rs does not move to first row: id=" . reset($rs->fields));
}
/////////////////////////////////////////////////////////////
include_once '../pivottable.inc.php';
print "<h3>Pivot Test</h3>";
$db->debug = true;
$sql = PivotTableSQL($db, 'ADOXYZ', 'firstname', 'lastname', false, 'ID', 'Sum ', 'sum', true);
$rs = $db->Execute($sql);
if ($rs) {
rs2html($rs);
} else {
Err("Pivot sql error");
}
$pear = true;
//true;
$db->debug = false;
if ($pear) {
// PEAR TESTS BELOW
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
include_once "PEAR.php";
$rs = $db->query('select * from ADOXYZ where id>0 and id<10 order by id');
$i = 0;
if ($rs && !$rs->EOF) {
while ($arr = $rs->fetchRow()) {
$i++;
//print "$i ";
if ($arr[0] != $i) {
print_r($arr);
print "<p><b>PEAR DB emulation error 1.</b></p>";
$pear = false;
break;
}
}
$rs->Close();
}
if ($i != $db->GetOne('select count(*) from ADOXYZ where id>0 and id<10')) {
print "<p><b>PEAR DB emulation error 1.1 EOF ({$i})</b></p>";
$pear = false;
}示例3: testdb
//.........这里部分代码省略.........
print "<b>Error - Replace failed, count={$cnt}</b><p>";
}
$ADODB_COUNTRECS = $savec;
}
$ret = $db->Replace('adoxyz', array('id' => 1000, 'firstname' => 'Harun', 'lastname' => 'Al-Rashid'), array('id', 'firstname'), $autoq = true);
if ($ret != 2) {
print "<b>Replace failed: </b>";
}
print "test A return value={$ret} (2 expected) <p>";
$ret = $db->Replace('adoxyz', array('id' => 1000, 'firstname' => 'Sherazade', 'lastname' => 'Al-Rashid'), 'id', $autoq = true);
if ($ret != 1) {
if ($db->dataProvider == 'ibase' && $ret == 2) {
} else {
print "<b>Replace failed: </b>";
}
}
print "test B return value={$ret} (1 or if ibase then 2 expected) <p>";
print "<h3>rs2rs Test</h3>";
$rs = $db->Execute('select * from adoxyz order by id');
$rs = $db->_rs2rs($rs);
$rs->valueX = 'X';
$rs->MoveNext();
$rs = $db->_rs2rs($rs);
if (!isset($rs->valueX)) {
err("rs2rs does not preserve array recordsets");
}
if (reset($rs->fields) != 1) {
err("rs2rs does not move to first row");
}
/////////////////////////////////////////////////////////////
include_once '../pivottable.inc.php';
print "<h3>Pivot Test</h3>";
$db->debug = true;
$sql = PivotTableSQL($db, 'adoxyz', 'firstname', 'lastname', false, 'ID');
$rs = $db->Execute($sql);
if ($rs) {
rs2html($rs);
} else {
Err("Pivot sql error");
}
$db->debug = false;
include_once "PEAR.php";
// PEAR TESTS BELOW
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$pear = true;
$rs = $db->query('select * from adoxyz where id>0 and id<10 order by id');
$i = 0;
if ($rs && !$rs->EOF) {
while ($arr = $rs->fetchRow()) {
$i++;
//print "$i ";
if ($arr[0] != $i) {
print_r($arr);
print "<p><b>PEAR DB emulation error 1.</b></p>";
$pear = false;
break;
}
}
$rs->Close();
}
if ($i != $db->GetOne('select count(*) from adoxyz where id>0 and id<10')) {
print "<p><b>PEAR DB emulation error 1.1 EOF ({$i})</b></p>";
$pear = false;
}
$rs = $db->limitQuery('select * from adoxyz where id>0 order by id', $i = 3, $top = 3);
$i2 = $i;示例4: PivotTableSQL
# example2
#
# Query the main "product" table
# Set the rows to CompanyName and QuantityPerUnit
# and the columns to the UnitsInStock for different ranges
# and define the joins to link to lookup tables
# "categories" and "suppliers"
#
$sql = PivotTableSQL(
$gDB, # adodb connection
'products p ,categories c ,suppliers s', # tables
'CompanyName,QuantityPerUnit', # row fields
# column ranges
array(
' 0 ' => 'UnitsInStock <= 0',
"1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5',
"6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10',
"11 to 15" => '10 < UnitsInStock and UnitsInStock <= 15',
"16+" =>'15 < UnitsInStock'
),
' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where
'UnitsInStock', # sum this field
'Sum' # sum label prefix
);
print "<pre>$sql";
$rs = $gDB->Execute($sql);
rs2html($rs);
/*
Generated SQL:
SELECT CompanyName,QuantityPerUnit,
SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum 0 ", 示例5: run
public static function run(&$report)
{
$report->conn->SetFetchMode(ADODB_FETCH_ASSOC);
$rows = array();
$macros = $report->macros;
foreach ($macros as $key => $value) {
if (is_array($value)) {
$first = true;
foreach ($value as $key2 => $value2) {
$value[$key2] = mysql_real_escape_string(trim($value2));
$first = false;
}
$macros[$key] = $value;
} else {
$macros[$key] = mysql_real_escape_string($value);
}
if ($value === 'ALL') {
$macros[$key . '_all'] = true;
}
}
//add the config and environment settings as macros
$macros['config'] = PhpReports::$config;
$macros['environment'] = PhpReports::$config['environments'][$report->options['Environment']];
$raw_sql = "";
foreach ($report->raw_query as $qry) {
if (is_array($qry)) {
foreach ($qry as $key => $value) {
// TODO handle arrays better
if (!is_bool($value) && !is_array($value)) {
$qry[$key] = PhpReports::renderString($value, $macros);
}
}
//TODO This sux - need a class or something :-)
$raw_sql .= PivotTableSQL($report->conn, $qry['tables'], $qry['rows'], $qry['columns'], $qry['where'], $qry['orderBy'], $qry['limit'], $qry['agg_field'], $qry['agg_label'], $qry['agg_fun'], $qry['include_agg_field'], $qry['show_count']);
} else {
$raw_sql .= $qry;
}
}
//expand macros in query
$sql = PhpReports::render($raw_sql, $macros);
$report->options['Query'] = $sql;
$report->options['Query_Formatted'] = SqlFormatter::format($sql);
//split into individual queries and run each one, saving the last result
$queries = SqlFormatter::splitQuery($sql);
foreach ($queries as $query) {
if (!is_array($query)) {
//skip empty queries
$query = trim($query);
if (!$query) {
continue;
}
$result = $report->conn->Execute($query);
if (!$result) {
throw new Exception("Query failed: " . $report->conn->ErrorMsg());
}
//if this query had an assert=empty flag and returned results, throw error
if (preg_match('/^--[\\s+]assert[\\s]*=[\\s]*empty[\\s]*\\n/', $query)) {
if ($result->GetAssoc()) {
throw new Exception("Assert failed. Query did not return empty results.");
}
}
}
}
return $result->GetArray();
}