代码之家 › 专栏 › 技术社区 › Steven Smith

动态数据透视表mysqli和PHP

pivot-table mysqli mysql php

Steven Smith · 技术社区 · 6 年前

我对mysqli和PHP在动态数据透视表上的使用感到有些困惑,因为我可以在PHP中获得静态数据透视表,但不能获得动态数据透视表。

我在MySQL Workbench中构建了一个动态Pivot,它看起来很好,工作非常好。

困难在于PHP,因为我曾尝试查询multi或statement out,但都失败了,因为我知道不建议使用multi-query,但我现在陷入了困境。

您可以看到我为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>";
    }
}

而且根本不起作用。此外,我喜欢将工作台工具导出到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');

1 回复 | 直到 6 年前

Bill Karwin 6 年前

这应该比你做的容易。您不需要在文本协议中使用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";
}