根据Timestamp mysql + PHP组织数据

问题描述：

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

I am trying to organize an output of DATA in the following way: Today, Yesterday and everything before Yesterday is present with a respective full DateTime minus the clock time of course. For a better understanding, have a look at the screenshot below:

我已经编写了这段代码:

I have written this code:

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();
}

我正在尝试找出一种将事情分解的方法，例如今天"，昨天"，昨天之前的一天"等等，只要被认为是正常的，例如可能整整一个月.

I am trying to figure out a way to split things up, for instance, "Today", "Yesterday", "Day before Yesterday" and so forth for as long as considered normal e.g. an entire month maybe.

*我将如何使用PDO准备好的语句正确地组织此结构? *

*How would I structure this up correctly with a PDO prepared statement? *

   [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时间戳记的内容.依此类推.

Clearly: I want to print everything with timestamp of TODAY. Next, everything with YESTERDAYS timestamp. And so forth.

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处理繁重的工作.我故意将某些内容分解为多行，而不是嵌套函数以使其更易于阅读.视需要进行压缩.

Here's an example of how I would handle this. I wouldn't change your query - it's fine as is. Assuming you want to show everything in the database sorted from latest to earliest post. Let PHP handle the heavy lifting. I've purposely broken some things in to multiple lines instead of nesting the functions to make it easier to read. Condense as you see fit.

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

I'm not claiming this is the BEST way to do it. Only what I use.

//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

根据Timestamp mysql + PHP组织数据

相关推荐