对mysql表进行双迭代 - Double iteration on mysql table

Double iteration on mysql table

本文关键字：迭代 mysql | 更新日期: 2023-09-27

我试图显示提交调查的唯一日期，并将响应放置在一个伟大的，好的，公平的或坏的行。我不确定这个代码有什么问题，但它只返回最近的日期。像这样

SurveyDate | Great | Good | Fair | Bad
2016-09-26 | 34    | 12   | 22   | 5  
2016-08-24 | 10    | 5    | 12   | 3  
<?php
$w = "select distinct(datetime) from clientsurvey order by datetime desc";  
$zx = mysql_query($w);  
if (mysql_num_rows($zx) < 1) {  
echo "<tr><td colspan='5' style='text-align:center; color:#ff0000'>No survey records</td></tr>";  
}
$great = 0;
$good = 0;
$fair = 0;
$bad = 0;
while ($z = mysql_fetch_array($zx)) {
   $uniquedate = $z['datetime'];
   $xx = "select * from clientsurvey where datetime like '%$uniquedate%'";
   $zz = mysql_query($xx);
   while ($zx = mysql_fetch_array($zz)) {
       $clientrating = $zx['clientrating'];
       $datetime = $zx['datetime'];
       if ($clientrating === "Bad") {
        $bad++;
       }
       if ($clientrating === "Fair") {
        $fair++;
       }
       if ($clientrating === "Good") {
         $good++;
       }
       if ($clientrating === "Great") {
        $great++;
       }
    }
    echo "<tr><td>$uniquedate</td><td>$great</td><td>$good</td><td>$fair</td><td>$bad</td></tr>";
$great = 0;
$good = 0;
$fair = 0;
$bad = 0;
}
?>

你可以直接在MySQL中使用pivot查询来处理这个问题:

SELECT datetime AS SurveyDate,
       SUM(CASE WHEN clientrating = 'Great' THEN 1 END) AS Great,
       SUM(CASE WHEN clientrating = 'Good'  THEN 1 END) AS Good,
       SUM(CASE WHEN clientrating = 'Fair'  THEN 1 END) AS Fair,
       SUM(CASE WHEN clientrating = 'Bad'   THEN 1 END) AS Bad
FROM clientsurvey
GROUP BY datetime

PHP代码:

$query = "SELECT datetime AS SurveyDate,".
         "SUM(CASE WHEN clientrating = 'Great' THEN 1 END) AS Great,".
         "SUM(CASE WHEN clientrating = 'Good'  THEN 1 END) AS Good,".
         "SUM(CASE WHEN clientrating = 'Fair'  THEN 1 END) AS Fair,".
         "SUM(CASE WHEN clientrating = 'Bad'   THEN 1 END) AS Bad".
         "FROM clientsurvey".
         "GROUP BY datetime";
$result = mysql_query($query);
echo "datetime, bad, fair, good, great";
while ($row = mysql_fetch_array($result)) {
    $datetime = $row['datetime'];
    $bad      = $row['Bad'];
    $fair     = $row['Fair'];
    $good     = $row['Good'];
    $great    = $row['Great'];
    echo $datetime.", ".$bad.", ".$fair.", ".$good.", ".$great;
}

我没有看你的代码的其余部分，但这应该适用于多个日期;

 <?php
$w = "select distinct(datetime) from clientsurvey order by datetime desc";  
$zx = mysql_query($w);  
if (mysql_num_rows($zx) < 1) {  
echo "<tr><td colspan='5' style='text-align:center; color:#ff0000'>No survey records</td></tr>";  
}
$great = 0;
$good = 0;
$fair = 0;
$bad = 0;
while ($z = mysql_fetch_array($zx)) {
$uniquedate = $z['datetime'];
$xx = "select * from clientsurvey where datetime like '%$uniquedate%'";
$zz = mysql_query($xx);
while ($zx = mysql_fetch_array($zz)) {
       $clientrating = $zx['clientrating'];
   $datetime = $zx['datetime'];
   if ($clientrating === "Bad") {
    $bad++;
   }
   if ($clientrating === "Fair") {
    $fair++;
   }
   if ($clientrating === "Good") {
     $good++;
   }
   if ($clientrating === "Great") {
    $great++;
   }
                     echo "<tr><td>$uniquedate</td><td>$great</td><td>$good</td><td>$fair</td><td>$bad</td></tr>";
$great = 0;
$good = 0;
$fair = 0;
$bad = 0;
}
 }?>

但也许你应该像其他人建议的那样在SQL中做