无法弄清楚如何从MySql查询中获取数据 [英] Can't figure how to get data from MySql Query
本文介绍了无法弄清楚如何从MySql查询中获取数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我早些时候问过一个有关如何执行此查询的问题,并收到了好评.不幸的是,它比我通常运行的查询要先进一些,因此我无法弄清楚如何检索结果并将其显示在我的网页上.这是原始问题的链接原始问题
I asked a question earlier on how to do this query and received a great response. Unfortunately it is a bit more advanced than the queries I normally run so I cannot figure out how to retrieve the results and display them on my web page. Here is the link to the original question original 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; }
推荐答案
您遇到了一些问题:
-
$row
的索引需要用引号引起来,例如$row['name']
否则,您将收到很多未定义的常量"警告; -
$row['Jan']
是检索月度数据的正确方法,您需要在其他每个月中复制一次; - 将
$avgGrade
替换为$row['AVG']
- 我不确定您要通过 实现什么
- The indexes to
$row
need to be enclosed in quotes e.g.$row['name']
otherwise you will get a lot of "Undefined constant" warnings; $row['Jan']
is the correct way to retrieve the monthly data, you need to replicate that for each of the other months;- replace
$avgGrade
with$row['AVG']
- I'm not sure what you're trying to achieve with
<td class = "gScore-<?php echo $row[Jan]; ?>"></td>
你真的想要
<td class = "gScore"><?php echo $row[Jan]; ?></td>
- 您需要将
-- repeat for May to November
行添加到查询中,即
- you need to add the
-- repeat for May to November
rows to the query i.e.
$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");
这篇关于无法弄清楚如何从MySql查询中获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文