php - 如何根据 mySQL 中的日期拆分行并使用 PHP 显示它？

Question

我在我的页面中保存了用户的访问和时间。 mySQL 如下所示:

id  - user - page - timestamp
340 - 1 - page1 - 2009-05-18 22:11:11
339 - 1 - page3 - 2009-05-18 22:10:01
338 - 2 - page4 - 2009-05-18 22:08:05
337 - 1 - page2 - 2009-05-18 22:06:00
336 - 2 - page1 - 2009-05-18 22:15:10
...
...
280 - 1 - page5 - 2009-05-17 14:10:10
279 - 2 - page 3 - 2009-05-17 12:05:00

如何使用 PHP 获得以下输出？

预期输出 (show.php?user=1)

2009-05-18

page 1 - 2009-05-18 22:11:11 last page
page3 - 2009-05-18 22:10:01 stayed for 1 minute 10 seconds
page2 - 2009-05-18 22:06:00 stayed for 4 minutes 1 second

2009-05-17

page 5 - 2009-05-17 14:10:10 last page
page 3 - 2009-05-17 12:05:00 stayed for 2 hours 5 minutes 10 seconds

到目前为止我做了什么

$sql= mysql_query(SELECT user, page, timestamp FROM paths WHERE DATE(`date`) = '2009-05-18'); // must not set a date, but get all the dates

while($row = mysqli_fetch_array($sql)) {
        echo $row["page"]. " - " . $row["timestamp"]. "<br>";
//echo this timestamp - previous timestamp
//if different day then start again
    }

Answer 1

其实我对此有一个想法，但它对时间没有用；

sql = "SELECT user, page, timestamp FROM paths WHERE DATE(`date`) = '2009-05-18')" ORDER BY page ASC,timestamp DESC;

$a = null;
while($row = mysqli_fetch_array($sql)) {
    $a[] = $row;
}

$i = 0;

while(i<sizeof($a)){
    if($a[i]->page == $a[i+1]->page){
        $sub = (int) $a[i+1]->timestamp - (int) $a[i]->timestamp;
        echo '' .$a[i]->page. '-' .$a[i]->timestamp.' stayed for '.$sub. 'miliseconds';
    }
    i = i+2;
}