php - 动态数据透视表 mysqli 和 PHP

Question

我在动态数据透视表上使用 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>";
    }
}

而且它根本不起作用。我也喜欢导出到 PHP 代码的 Workbench 工具，但缺少大部分重要功能

$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');

Answer 1

这应该比你做的更容易。您不需要在文本协议(protocol)中使用 PREPARE 和 EXECUTE。并且不需要在 SQL 中查询 INTO @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";
}