php - 根据Timestamp mysql + PHP组织数据

我正在尝试通过以下方式组织DATA的输出：
今天，昨天和昨天之前的所有内容都带有相应的完整DateTime减去当然的时钟时间。为了更好的理解，请看下面的屏幕截图：

我写了这段代码：

try{
    $db = new PDO("mysql:host=" .$hostname. ";dbname=" .$database. "", "" .$user. "", "" .$pass. "");
  $db->setAttribute(PDO::ATTR_PERSISTENT, true);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $notifications = $db->prepare("SELECT * FROM reports
    ORDER BY timestamp DESC");
  $notifications->execute();

  $result = (object)$notifications->fetchAll(PDO::FETCH_OBJ);

  echo "<pre>";

  print_r($result);

    echo "</pre>";

}
catch(PDOException $e)
{
    echo $e->getMessage();
}

我正在尝试找出一种方法来分解内容，例如“ Today”，“ Yesterday”，“ Yesterday Day”等等，只要被认为是正常的，例如可能整整一个月。

*我如何使用PDO准备好的语句正确地组织它？ *

   [Today] => stdClass Object
        (
            [id] => 1
            [timestamp] => 2015-04-09 13:20:05
            [seen] => 0
        )
    [Yesterday] => stdClass Object
        (
            [id] => 2
            [timestamp] => 2015-04-08 15:30:50
            [seen] => 0
        )

显然：我想打印带有TODAY时间戳的所有内容。接下来，所有带有YESTERDAYS时间戳的内容。依此类推。

SQL：

// Today
AND DATE(from_unixtime(comment_date)) = CURRENT_DATE

// Yesterday
AND DATE(from_unixtime(comment_date)) =  DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY)

// This week
AND YEARWEEK(from_unixtime(comment_date), 1) =  YEARWEEK(CURRENT_DATE, 1)

// This month
AND YEAR(from_unixtime(comment_date)) = YEAR(CURRENT_DATE)
AND MONTH(from_unixtime(comment_date)) = MONTH(CURRENT_DATE)

最佳答案

这是我将如何处理此示例。我不会更改您的查询-很好。假设您要显示数据库中从最新到最早的帖子排序的所有内容。让PHP处理繁重的工作。我故意将某些内容分解为多行，而不是嵌套函数以使其更易于阅读。凝结如您所见。

我并不是说这是最好的方法。只有我用的。

//MOCK UP SOME DISPLAY DATA - YOU WOULD USE YOUR QUERY RESULT INSTEAD
$rows = array();
$rows[] = date('Y-m-d H:i:s');
$rows[] = date('Y-m-d H:i:s');
$rows[] = date('Y-m-d H:i:s');
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, date('n'), date('j') - 1, date('Y')));
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, 12, 24, 2014));
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, 12, 25, 2014));
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, 12, 26, 2014));
$rows[] = date('Y-m-d H:i:s', mktime(0, 0, 0, 3, 2, 2001));

//CREATE AN ARRAY OF THE REPLACEMENTS YOU WANT
$aArray = array();
$aArray[date('Y-m-d')] = 'Today';
$aArray[date('Y-m-d', mktime(0, 0, 0, date('n'), date('j') - 1, date('Y')))] = 'Yesterday';
$aArray[date('Y-m-d', mktime(0, 0, 0, date('n'), date('j') - 2, date('Y')))] = 'Day before Yesterday';
$aArray['2014-12-25'] = 'Christmas 2014';

//INITIALIZE SOME VARIABLES
$cLastHeader = '';
$cCurrHeader = '';

//THIS WOULD BE YOUR QUERY RESULTS LOOP
foreach ($rows AS $nNull => $cDate) {
    $cLookup = substr($cDate, 0, 10);  //TRIM OUT THE TIME FROM CURRENT RECORD

    //IS DATE IN ARRAY? IF NOT, FORMAT
    if (isset($aArray[$cLookup])) {
        $cCurrHeader = $aArray[$cLookup];
    } else {
        $cCurrHeader = $cLookup; //WOULD SHOW 'YYYY-MM-DD'
        $cCurrHeader = date('F Y', strtotime($cLookup)); //WOULD SHOW 'MONTH YYYY'
    }

    //HAS HEADER CHANGED? IF SO PRINT
    if ($cCurrHeader != $cLastHeader) {
        $cLastHeader = $cCurrHeader;
        print($cCurrHeader . "\n");
    }

    //PRINT RECORD
    print("\t" . $cDate . "\n");
}

输出是：

Today
    2015-05-28 18:40:35
    2015-05-28 18:40:35
    2015-05-28 18:40:35
Yesterday
    2015-05-27 00:00:00
December 2014
    2014-12-24 00:00:00
Christmas 2014
    2014-12-25 00:00:00
December 2014
    2014-12-26 00:00:00
March 2001
    2001-03-02 00:00:00

yesterday

php - 根据Timestamp mysql + PHP组织数据