我从sql数据库中的数据生成一个列表。问题是列表在每个备注之前打印"系统"名称,而不是在 1 下列出包含相同"系统"名称的所有行的"备注"。
法典:
<?php
require("dbcon.php");
$query = "
SELECT
id,
Date,
System,
Remark
FROM records
WHERE Date BETWEEN '$from' AND '$to'
ORDER BY Date
";
echo "<div id='heading'>EVENT LOG: Last week</div><br />";
$result = mysql_query($query);
while ($row=mysql_fetch_array($result)) { ?>
<ul><?php echo $row["System"]; ?>
<li><?php echo $row["Remark"]; ?></li></ul>
<?php } ?>
这给了我一个输出,如下所示:
Systemname1
- Log entry for Systemname 1 (stored under Remark in database)
Systemname1
- Log entry for Systemname 1
Systemname2
- Log entry for Systemname 2
Systemname2
- Log entry for Systemname 2
但我想要的是:
Systemname1
- Log entry for Systemname 1
- Log entry for Systemname 1
Systemname2
- Log entry for Systemname 2
- Log entry for Systemname 2
这可能可以通过使用 foreach(( 以某种方式修复,但我不确定如何:/
我会选择使用 temp 变量与日期进行比较,但这个问题有多种解决方案:
$result = mysql_query($query);
$temp_date = '';
if(mysql_num_rows($result)) {
echo "<ul>"; // Start the list only if data exist
while ($row=mysql_fetch_array($result)) {
if($row["Date"]!=$temp_date) { // If 'Date' is equal to the previous one, skip
if($temp_date!='') { // If it's not the first list
echo "</ul><ul>"; // close the previous one and start another
}
echo $row["Date"];
$temp_date=$row["Date"];
}
echo "<li>".$row["Remark"]."</li>";
}
echo "</ul>";
}
您的查询是合理的:)
我将通过两个步骤使用代码解决此问题:
首先将 sql 结果格式化为可用于渲染的内容:
$nestedResult = array();
foreach (mysql_fetch_assoc($result) as $row) {
if (!isset($nestedResult[$row['Date']])) {
$nestedResult[$row['Date']] = array();
}
$nestedResult[$row['Date']][] = $row['Remark'];
}
然后实际渲染:
echo '<ul>';
foreach ($nestedResult as $date => $remarks) {
echo '<li>' . htmlspecialchars($date) . '<ul>';
foreach ($remarks as $remark) {
echo '<li>' . htmlspecialchars($remark) . '</li>';
}
echo '</ul></li>';
}
echo '</ul>';
您可以在 PHP 中进行所有排序和组合,但这里有一个选项,其中 databse 进行分组:
<?php
require("dbcon.php");
$query = "
SELECT
id,
Date,
System,
GROUP_CONCAT(Remark, SEPARATOR ':::') AS Remark
FROM records
WHERE Date BETWEEN '$from' AND '$to'
ORDER BY Date
GROUP BY Date
";
echo "<div id='heading'>EVENT LOG: Last week</div><br />";
$result = mysql_query($query);
while ($row=mysql_fetch_array($result)) { ?>
<ul><?php echo $row["Date"]; ?>
<?php foreach(explode(':::', $row["Remark"]) as $remark) { ?>
<li><?php echo $remark; ?></li>
<?php } // END foreach ?>
</ul>
<?php } // END while ?>