不知道如何从 MySql 查询中获取数据

Question

我早些时候问了一个关于如何执行此查询的问题并收到了很好的回复。不幸的是，它比我通常运行的查询要高级一些，所以我不知道如何检索结果并将它们显示在我的网页上。这是原始问题原始问题的链接   <?php// Associative array$result = $db->query("SELECT Name,       COALESCE(AVG(CASE WHEN mth = 1 THEN PTS END), 0) AS Jan,       COALESCE(AVG(CASE WHEN mth = 2 THEN PTS END), 0) AS Feb,       COALESCE(AVG(CASE WHEN mth = 3 THEN PTS END), 0) AS Mar,       COALESCE(AVG(CASE WHEN mth = 4 THEN PTS END), 0) AS Apr,       COALESCE(AVG(CASE WHEN mth = 5 THEN PTS END), 0) AS May,       COALESCE(AVG(CASE WHEN mth = 6 THEN PTS END), 0) AS Jun,       COALESCE(AVG(CASE WHEN mth = 7 THEN PTS END), 0) AS Jul,       COALESCE(AVG(CASE WHEN mth = 8 THEN PTS END), 0) AS Aug,       COALESCE(AVG(CASE WHEN mth = 9 THEN PTS END), 0) AS Sep,       COALESCE(AVG(CASE WHEN mth = 10 THEN PTS END), 0) AS Oct,       COALESCE(AVG(CASE WHEN mth = 11 THEN PTS END), 0) AS Nov,       COALESCE(AVG(CASE WHEN mth = 12 THEN PTS END), 0) AS Dec,       AVG(PTS) AS AVGFROM (  SELECT Name, `Points Pass` AS PTS, MONTH(STR_TO_DATE(`OS Date`, '%a %b %e %H:%i:%s %Y')) AS mth  FROM data) dGROUP BY Name");while($row = mysqli_fetch_array($result)) {            ?>         <tr            </tr>        <?php   }if (!$result) { echo $db->error; }

Answer 1

你有几个问题：

1. 索引$row需要用引号括起来，$row['name']否则你会收到很多“未定义常量”警告；

2. $row['Jan']是检索月度数据的正确方法，您需要为其他每个月复制它；

3. 替换$avgGrade为$row['AVG']

4. 我不确定你想用什么来实现

<td class = "gScore-<?php echo $row[Jan]; ?>"></td>

你真的想要

<td class = "gScore"><?php echo $row[Jan]; ?></td>

1. 您需要将-- repeat for May to November行添加到查询即

   $result = $db->query("SELECT name,

       COALESCE(AVG(CASE WHEN mth = 1 THEN PTS END), 0) AS Jan,

       COALESCE(AVG(CASE WHEN mth = 2 THEN PTS END), 0) AS Feb,

       COALESCE(AVG(CASE WHEN mth = 3 THEN PTS END), 0) AS Mar,

       COALESCE(AVG(CASE WHEN mth = 4 THEN PTS END), 0) AS Apr,

       COALESCE(AVG(CASE WHEN mth = 5 THEN PTS END), 0) AS May,

       COALESCE(AVG(CASE WHEN mth = 6 THEN PTS END), 0) AS Jun,

       COALESCE(AVG(CASE WHEN mth = 7 THEN PTS END), 0) AS Jul,

       COALESCE(AVG(CASE WHEN mth = 8 THEN PTS END), 0) AS Aug,

       COALESCE(AVG(CASE WHEN mth = 9 THEN PTS END), 0) AS Sep,

       COALESCE(AVG(CASE WHEN mth = 10 THEN PTS END), 0) AS Oct,

       COALESCE(AVG(CASE WHEN mth = 11 THEN PTS END), 0) AS Nov,

       COALESCE(AVG(CASE WHEN mth = 12 THEN PTS END), 0) AS `Dec`,

       AVG(PTS) AS AVG

   FROM (

   SELECT name, PTS AS PTS, MONTH(STR_TO_DATE(DATE, '%a %b %e %H:%i:%s %Y')) AS mth

   FROM data

    ) d

    GROUP BY name");