Organize Data based on Timestamp mysql + PHP

Question

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.

*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
        )

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)

Answer 1

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

The output from this is :

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

Answer 2

SELECT IF( DATEDIFF( NOW( ) , `timestamp` ) =0, "Today", if( DATEDIFF( NOW( ) , `timestamp` ) =1, "Yesterday", DATE_FORMAT( `timestamp`, '%M %d' ) ) ) AS day,
id,timestamp,seen
FROM reports
ORDER BY timestamp DESC

Trying to solve your issue through query,check if it is helpful for you.

As per given screenshot, after today and yesterday this query will give you month and date.

Answer 3

What about get all the rows and checking the date in PHP then group the results which exists at the same time-frame in separate array like the following :

$notifications = $db->prepare("SELECT * FROM reports
ORDER BY timestamp DESC");
$notifications->execute();
$rows = $notifications->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row ){
 if ( date('Ymd') == date('Ymd', strtotime($row['timestamp'])) ){
     $today_rows[] = $row;
    }
else if (date('Ymd', strtotime('yesterday')) == date('Ymd', strtotime($row['timestamp'])) ){
 $yesterday_rows[] = $row;
 }
else if () ... etc 
}

Now you can use $today_row , $yesterday_rows ... etc in your view and display them as you want.

Answer 4

There are multiple ways to approach this. First, you can create separate calls and merge the data on php side.

Second, you can use subquery but I don't recommend subqueries as they are kind of tricky and can hog up a lot of resources.

Third, you can use unions. You create 3 separate queries to select based on different scenarios and then use union. This way, you will get all the results in one call.

SELECT *, 'today' as day FROM table WHERE DATE(from_unixtime(comment_date)) = CURRENT_DATE
UNION
SELECT *, 'yesterday' as day FROM table WHERE MONTH(from_unix_MONTH(CURRENT_DATE)) = MONTH(CURRENT_DATE)

So basically, unions are different select statements put together into one query. You can union as many select statements as you like. HOWEVER, note that you want to have all select statements return the SAME COLUMNS or it will error. You also might wanna read up on UNION ALL. There are some minor differences between UNION and UNION ALL but not very.

Answer 5

You should alter your MySQL query to get date difference as well.

SELECT *,DATEDIFF(NOW(),`timestamp`) as `dateDifference` FROM `reports` ORDER BY `dateDifference`

an example is provided here http://sqlfiddle.com/#!9/ecad0/6

Then run an if statement or a switch-case on dateDifference to organize your results the way you want. Example of a suitable switch-case statement is given below

switch (true) {
    case ($dateDifference===0):
        echo "Today's reports!";
        break;
    case ($dateDifference===1):
        echo "Yesterday's reports!";
        break;
    case ($dateDifference>1):
        echo "Even older reports!";
        break;
    case ($dateDifference<0):
        echo "An unexpected error occurred. Please contact your system administrator!";
}