来自SQL查询的每个日期的HTML表

Question

I need to output the result from an SQL query as a table for each date. Each date has several rows with a uniqe id, a system name and a remark.

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$dbname") or die("cannot select DB");


    $query = " 
    SELECT id, Date, System, Remark FROM records WHERE Date BETWEEN '$from' AND '$to'
    ORDER BY Date, CASE System 
        WHEN 'System1' THEN 1
        WHEN 'System2' THEN 2
        WHEN 'System4' THEN 3
        WHEN 'System5' THEN 4
        ELSE 19
        END
    ";

$result = mysql_query($query);

How do I proceed from this to generate a table that contains all rows with the same date:

<table>
<tr><th colspan="3"> Display date here</th></tr>
<tr>
    <td>Display System here</td>
    <td>Display Remark here</td>
    <td>Display id here</td>
</tr>
    <tr>
    <td>Display System here</td>
    <td>Display Remark here</td>
    <td>Display id here</td>
</tr>
    <tr>
    <td>Display System here</td>
    <td>Display Remark here</td>
    <td>Display id here</td>
</tr>
</table>

Answer 1

Try this

<php
$previous_date="";

echo "<table>";
while ($row=mysql_fetch_array($result))
{
$new_date=$row["date"];
if ($previous_date!=$new_date && $previous_date!="")
echo "</table><table>";

if ($previous_date!=$new_date)
echo "<tr><th colspan='3'>".$row["date"]."</th></tr>";

echo "<tr>";
echo "<td>".$row["system"]."</td>";
echo "<td>".$row["remark"]."</td>";
echo "<td>".$row["id"]."</td>";
echo "</tr>";

$previous_date=$new_date;
}
echo "</table>";
?>

Edited

Adding explanation:

For each loop i check if this is a new date. If yes, then i close previous table, i create a new one and i echo the table header.

I had to handle the case of the first table to be opened (this is the case when $previous_date is empty). Then i just insert the header.

The first table is opened before the while loop, and the last table closes after the while loop.