我正在尝试从mysql数据库中获取记录并对它们进行分组。以这个表为例:
+++++++++++++++++++++++++++++++++++++++++++++++++++++
DATEADDED DOCTYPE SUBJECT DETAILS
2018-03-03 9:54:54 DOCUMENT Letter Outgoing
2018-03-03 8:54:54 PARCEL Mail Incoming
2018-03-04 8:55:54 PARCEL Mail Incoming
2018-03-04 8:55:54 DOCUMENT Mail Outgoing
+++++++++++++++++++++++++++++++++++++++++++++++++++++
我想做的是按日期(不含时间)对这些数据进行分组,然后按类型分组并对其进行计数,结果如下所示:
+++++++++++++++++++
DATE: April 3, 2018
DOCUMENT - 1
PARCEL - 1
TOTAL-----------2
DATE: April 4, 2018
DOCUMENT - 1
PARCEL - 1
TOTAL-----------2
++++++++++++++++++++
已尝试使用以下代码:
<?php
foreach($page->query('SELECT id,dateAdded,COUNT(*) FROM seclogs GROUP BY DATE(dateAdded) desc') as $row) { ?><tr>
<td><?php echo date("Y-m-d", strtotime($row['dateAdded'])); ?></td>
<td><?php
$thisDate=date(strtotime($row['dateAdded']));
foreach($page->query("SELECT id,dateAdded,docType,COUNT(*) FROM seclogs WHERE DATE(dateAdded) LIKE '$thisDate' GROUP BY docType desc") as $row2) {
echo $row2['docType']."-".$row2['COUNT(*)']."<br/>"; } ?><br/><b><?php echo "Total: ".$row['COUNT(*)'].""; ?></b> </td>
</tr><?php } ?>
但它只显示以下输出:
+++++++++++++++++++
DATE: April 3, 2018
TOTAL-----------2
DATE: April 4, 2018
TOTAL-----------2
++++++++++++++++++++
我一直在工作,到处寻找这个问题,但找不到任何答案。任何帮助都将不胜感激。
1条答案
按热度按时间ws51t4hk1#
您想要的确切输出实际上是一种表示方式,可以在php代码中相当容易地处理。至于查询,以下是我将用于您需要的原始数据的内容:
当然,这不会给出您想要的输出,至少不完全是这样。现在需要做的是使用一个循环,只打印每个日期/类型组开头的日期。