php - 动态数据透视表 mysqli 和 PHP
问题描述
我在动态数据透视表上使用 mysqli 和 PHP 时有点挣扎,因为我能够在 PHP 中获得静态数据透视表,但不是动态数据透视表。
我在 MySQL Workbench 中构建了一个动态 Pivot,它看起来不错,而且工作完美。
斗争是 PHP,因为我试图查询多或语句,两者都失败了,因为我理解不推荐多查询,但我现在被卡住了。
如您所见,我为 PHP 编写的代码
function get_all_records() {
$conn = getdb();
$Sql = "SET @sql = NULL;";
$Sql .= "SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(suo.date = ''', date, ''', suo.units_ordered, 0)) AS ', CONCAT('`',date,'`') ) ) INTO @sql FROM tbl_sku_units_order;";
$Sql .= "SET @sql = CONCAT('SELECT ls.sku AS list_sku,
COALESCE(MIN(suo.sku), 'NotSold' ) AS sold_sku,
', @sql, ' ,
COALESCE( SUM(suo.units_ordered), 0 ) AS total_sold
FROM tbl_list_sku AS ls
LEFT JOIN tbl_sku_units_order AS suo
ON suo.sku = ls.sku
GROUP BY ls.sku
ORDER BY total_sold DESC');";
$Sql .="PREPARE stmt FROM @sql;";
$Sql .="EXECUTE stmt;";
$Sql .="DEALLOCATE PREPARE stmt;";
$result = mysqli_multi_query($conn, $Sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "<tr>
<td>" . $row['list_sku'] . "</td>
<td>" . $row['today_sold'] . "</td></tr>";
}
// echo "<tr> <td><a href = '' class = 'btn btn-danger' id = 'status_btn' data-loading-text = 'Changing Status..'>Export</a></td></tr>";
echo "</tbody></table></div>";
} else {
echo "<P class = 'text-center'>You have no recent QTY Daily Inventory</P>";
}
}
它根本不起作用。我也喜欢将 Workbench 工具导出到 PHP 代码,但缺少大部分重要功能
$query = "SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(suo.date = ''', date, ''', suo.units_ordered, 0)) AS ', CONCAT(\"`\",date,\"`\") ) ) INTO @sql FROM tbl_sku_units_order";
if ($stmt = $conn->prepare($query)) {
$stmt->execute();
$stmt->bind_result($date);
while ($stmt->fetch()) {
printf("%s\n", $date);
}
$stmt->close();
因为这个缺失的功能是
SET @sql = CONCAT('SELECT ls.sku AS list_sku,
COALESCE( MIN(suo.sku), "Not Sold" ) AS sold_sku,
', @sql, ' ,
COALESCE( SUM(suo.units_ordered), 0 ) AS total_sold
FROM tbl_list_sku AS ls
LEFT JOIN tbl_sku_units_order AS suo
ON suo.sku = ls.sku
GROUP BY ls.sku
ORDER BY total_sold DESC');
解决方案
这应该比你做的更容易。您不需要在文本协议中使用 PREPARE 和 EXECUTE。并且无需INTO @sql
在 SQL 中查询。只需在 PHP 代码中格式化查询并执行它。
// expand the expression so it's easier to see if parens are balanced
$Sql = "
SELECT GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(suo.date = ''', date, ''', suo.units_ordered, 0)) AS `', date, '`'
)
)
FROM tbl_sku_units_order;";
$gc_result = $conn->query($Sql);
// always check for errors
if (!$gc_result) {
echo "ERROR in SQL: $Sql\n{$conn->error}\n";
}
$gc_row = $gc_result->fetch_row();
$pivot_columns = $pivot_row[0];
// and check if this string is empty,
// because there might be no data to pivot
if (!$pivot_columns) {
$pivot_columns = 'NULL';
}
$Sql = "
SELECT ls.sku AS list_sku,
COALESCE(MIN(suo.sku), 'NotSold') AS sold_sku,
$pivot_columns,
COALESCE(SUM(suo.units_ordered), 0) AS total_sold
FROM tbl_list_sku AS ls
LEFT JOIN tbl_sku_units_order AS suo
ON suo.sku = ls.sku
GROUP BY ls.sku
ORDER BY total_sold DESC";
$pivot_result = $conn->query($Sql);
// always check for errors
if (!$pivot_result) {
echo "ERROR in SQL: $Sql\n{$conn->error}\n";
}
推荐阅读
- bash - BASH 脚本中的 gzip 可以在文件完全解压缩之前给出退出状态吗?如何防止这种情况?
- javascript - Ember 3 计算属性
- microsoft-graph-api - 权限不足,无法完成操作 - Graph API
- java - OOP 创建设计模式
- mongodb - 按具有不同值的相同键对 MongoDB 集合进行排序
- python - Python:通过从字符串读取将多个子元素附加到父元素
- php - Symfony 4 & Doctrine 2 在从集合中删除(第一个)项目后序列化导致转换为 JSON 对象而不是数组
- git - 如何恢复头部之前分支中的多个更改,但都来自同一个子分支?
- sqlite - SQLite Unix时间戳获取早于2天前的日期
- c# - 如何知道 Windows 应用商店应用程序已在 C# 或 VB 中终止?