时间戳

首页 > 解决方案 > 嵌套的 While 循环？

php - 嵌套的 While 循环？

问题描述

我有一些代码：

while($row = mysqli_fetch_array($Sat)){
    echo "['".$row['Month']."',".$row['Store A'].",".$row['Store B'].",".$row['Store C']."],";

这适用于用户拥有商店 A、B 和 C 的情况 - 但是，我想将其扩展到拥有不同数量商店的人。

我知道数组 $Sat 有标题，所以，是否可以计算标题，虽然它们不等于 Month（月份需要先行）回显标题名称，所以我将关联的行提供为我需要的格式我的下一步。

最终输出应该看起来像['Month',Store A,Store B,Store C]（如果用户有三个商店，或者['Month',Store A,Store C]如果他们只有 A & C 等。

我一直在尝试用构建它的变量替换代码，但这似乎不起作用。

我在 PHP（或真正的编码）方面的经验不是很好，只是试图调整一些东西以更好地在内部工作并保存我的理智 =）

所以，如果我的数据库是：

+-------+---------+---------+---------+
| Month | Store A | Store B | Store C |
+-------+---------+---------+---------+
| May-19|      0.0|      0.0|      0.0|
| Jun-19|     12.5|      8.3|      0.0|
| Jul-19|     10.5|     14.3|      0.0|
+-------+---------+---------+---------+

所需的输出是：['May-19',0.0,0.0,0.0],['Jun-19',12.5,8.3,0.0],['Jul-19',10.5,14.3,0.0],输入谷歌图表。

原始 SQL 查询为：

$sql_prep = "SELECT d.store_list AS NUMB, s.name AS NAME FROM data d INNER JOIN store_list s ON d.store_list=s.store_id WHERE d.status != '' AND d.store_list IN (" . $user_stores . ") GROUP BY store_list";
$sql_code = $conn->query($sql_prep);
    while($row = $sql_code->fetch_assoc()) {
    $select = $select . "CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  '" . $row['NAME'] . "',";
}
....
$sql = "SELECT
    " . $select . "
    DATE_FORMAT(date_submitted,'%b-%y') AS 'Month'
FROM data 
WHERE store_list IN (" . $user_stores . ")
GROUP BY YEAR(date_submitted), MONTH(date_submitted)
ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC";

这将编译为：

SELECT
    CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = 1 THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = 1 THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  'Store A',
    CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = 2 THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = 2 THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  'Store B',
    CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = 3 THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = 3 THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  'Store C',
    DATE_FORMAT(date_submitted,'%b-%y') AS 'Month'
FROM data 
WHERE store_list IN (1,2,3)
GROUP BY YEAR(date_submitted), MONTH(date_submitted)
ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC

和输出：

+---------+---------+---------+-------+
| Store A | Store B | Store C | Month |
+---------+---------+---------+-------+
|      0.0|      0.0|      0.0| May-19|
|     12.5|      8.3|      0.0| Jun-19|
|     10.5|     14.3|      0.0| Jul-19|
+---------+---------+---------+-------+

我发现我可以编辑我的原始代码：

while($row = mysqli_fetch_array($Sat)){
    echo "['".$row['Month']."',".$row['Store A'].",".$row['Store B'].",".$row['Store C']."],";

至

while($row = mysqli_fetch_array($Sat)){
    echo "['".$row['Month']."',".$row[0].",".$row[1].",".$row[2]."],";

因此，这与命名有关，我只需要在其中拥有与我现在拥有的商店一样多的行，而无需构建巨大的“IF THEN”查询。我仍然觉得这是一个依偎的 while 或 foreach 会这样做吗？

谢谢

标签： php

解决方案

试试下面的这段代码，我确实在 sql 语法中发现了一些类型错误：

    $sql_prep = "SELECT d.store_list AS NUMB, s.name AS NAME FROM data d INNER JOIN store_list s ON d.store_list=s.store_id WHERE d.status != '' AND d.store_list IN (" . $user_stores . ") GROUP BY store_list";
$sql_code = $conn->query($sql_prep);
$select = '';
    while($row = $sql_code->fetch_assoc()) {
        $select .= ", CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  '" . $row['NAME'] . "' "; -- right here
    }
.....
$sql = "SELECT DATE_FORMAT(date_submitted,'%b-%y') AS 'Month'
" . $select . "
FROM data 
WHERE store_list IN (" . $user_stores . ")
GROUP BY YEAR(date_submitted), MONTH(date_submitted)
ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC";

试试这个代码，它假设产生这个：

+-------+---------+---------+---------+
| Month | Store A | Store B | Store C |
+-------+---------+---------+---------+
| May-19|      0.0|      0.0|      0.0|
| Jun-19|     12.5|      8.3|      0.0|
| Jul-19|     10.5|     14.3|      0.0|
+-------+---------+---------+---------+

带数组：

['May-19',0.0,0.0,0.0],['Jun-19',12.5,8.3,0.0],['Jul-19',10.5,14.3,0.0]

要回显整个数组，您需要这个：

$result = [];
$index = 1;
while($row = mysqli_fetch_array($Sat)){
    //we know for sure that index 0 has value Month, then..
    $temp = [$row['Month']];
    $number = count($row) - 1;
    //loop thru rest of rows start from index 1
    for($i = 0; $i < $number; $i++){
        //push rows in to temporary array
        array_push($temp, $row[$i]);
    }
    //merge result in main array
    $result[$index] = $temp;
    $index++;
}
var_dump($result);

这就是我得到的：

array (size=5)
  0 => 
    array (size=5)
      0 => string 'moth1' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2
  1 => 
    array (size=6)
      0 => string 'moth2' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2
      5 => float 45.8
  2 => 
    array (size=5)
      0 => string 'moth3' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2
  3 => 
    array (size=7)
      0 => string 'moth4' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2
      5 => float 56.2
      6 => float 56.3
  4 => 
    array (size=5)
      0 => string 'moth5' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2

测试数据：

    $rows = [
       ['Month' => 'moth1', 10.0, 20.0, 30.6, 45.2],
       ['Month' => 'moth2', 10.0, 20.0, 30.6, 45.2, 45.8],
       ['Month' => 'moth3', 10.0, 20.0, 30.6, 45.2],
       ['Month' => 'moth4', 10.0, 20.0, 30.6, 45.2, 56.2, 56.3],
       ['Month' => 'moth5', 10.0, 20.0, 30.6, 45.2],
   ];

推荐阅读