Php - 按天记录的组记录

Question

I have a following table:

  CREATE TABLE IF NOT EXISTS "visits" ( 
  "id" int(10) unsigned NOT NULL AUTO_INCREMENT,
  "start" datetime NOT NULL,
  "finish" datetime NOT NULL,
  "name" varchar(50) NOT NULL,
  PRIMARY KEY ("id")
)

And I would like to display visits for next 2 days as shown in the following code:

<div id="day>Today</div>
<a href="#">VISIT NAME</a>
<a href="#">VISIT NAME</a>
<a href="#">VISIT NAME</a>
<div id="day>Tomorrow</div>
<a href="#">VISIT NAME</a>
<a href="#">VISIT NAME</a>
<div id="day>15-11-2013</div>
<a href="#">VISIT NAME</a>

At this moment I have only this I'm looking for PHP code :) At this moment I have only:

$link=mysql_query('select count(*), DATE_FORMAT(start,"%Y-%m-%d") as start FROM visits GROUP BY start');
            while($row=mysql_fetch_array($link))
            {
            echo'<div id="day">'.$row['start'].' '.$row['count(*)'].'</div>';
            }

but that show only

<div id="day>Today 3</div>
<div id="day>Tomorrow 2</div> 
<div id="day>15-11-2013 1</div>

Thanks in advance for your help.

Answer 1

Try this.

$sql = "
    SELECT *, DATE(`start`) AS group_name
    FROM `visits`
    WHERE DATE(`start`) BETWEEN CURDATE() AND CURDATE() + INTERVAL 2 DAY
    ORDER BY `start` ASC
";
$result = mysql_query($sql) or die(mysql_error());
$group = null;
while ($row = mysql_fetch_assoc($result)) {
    if (is_null($group) || $group != $row['group_name']) {
        $group = $row['group_name'];
        echo '<div id="day">' . $row['group_name'] . '</div>';
    }
    echo '<a href="#">' . $row['name'] . '</a>';
}