无法弄清楚如何从MySql Query获取数据

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 AVG
FROM (
SELECT Name, `Points Pass` AS PTS, MONTH(STR_TO_DATE(`OS Date`, '%a %b %e %H:%i:%s %Y')) AS mth
FROM data
) d
GROUP BY Name");

while($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['Name']; ?></td>
<td class = "gScore-<?php echo $row['Jan']; ?>"><?php echo $row['Jan']; ?></td>
<td class = "gScore-<?php echo $row['Feb']; ?>"><?php echo $row['Feb']; ?></td>
<td class = "gScore-<?php echo $row['Mar']; ?>"><?php echo $row['Mar']; ?></td>
<td class = "gScore-<?php echo $row['Apr']; ?>"><?php echo $row['Apr']; ?></td>
<td class = "gScore-<?php echo $row['May']; ?>"><?php echo $row['May']; ?></td>
<td class = "gScore-<?php echo $row['Jun']; ?>"><?php echo $row['Jun']; ?></td>
<td class = "gScore-<?php echo $row['Jul']; ?>"><?php echo $row['Jul']; ?></td>
<td class = "gScore-<?php echo $row['Aug']; ?>"><?php echo $row['Aug']; ?></td>
<td class = "gScore-<?php echo $row['Sep']; ?>"><?php echo $row['Sep']; ?></td>
<td class = "gScore-<?php echo $row['Oct']; ?>"><?php echo $row['Oct']; ?></td>
<td class = "gScore-<?php echo $row['Nov']; ?>"><?php echo $row['Nov']; ?></td>
<td class = "gScore-<?php echo $row['Dec']; ?>"><?php echo $row['Dec']; ?></td>
<td><?php echo $row['AVG']; ?></td>
</tr>
<?php   

}
if (!$result) { echo $db->error; }

Answer 1

您有几个问题：

1. $row的索引需要用引号括起来，例如$row['name']，否则会收到很多"未定义常量"警告
2. $row['Jan']是检索每月数据的正确方法，您需要将其复制到其他每个月
3. 用$row['AVG']替换$avgGrade
4. 我不确定你想用什么来实现

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

你真的想要吗

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

5. 您需要将-- 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");