Newbie N Newbie N - 5 months ago 18
MySQL Question

Compare each array date to mysql date in php

I have to create a php program that shows attendance of each employee by selected date range ie January 8, 2015 to January 21, 2015. I have to print each date from the date range specified then associate it to every attendance date that the employee have record with. Here's the expected output:

DATE am IN am OUT pm IN pm OUT

2015-01-08 08:13 17:51
2015-01-09 08:08 11:57 13:03 17:10
2015-01-10 08:18 08:20 11:20 12:01
2015-01-11
2015-01-12 08:05 11:59 12:00 12:4
2015-01-13 08:16 17:33 17:35
2015-01-14 08:05 12:00 12:45 18:05
2015-01-15 07:43 12:00 12:38 17:09
2015-01-16
2015-01-17 08:29 12:45 16:57
2015-01-18
2015-01-19 08:02 18:28
2015-01-20
2015-01-21 07:50 07:52 12:07 12:34


But what I can only output is this:

DATE am IN am OUT pm IN pm OUT

2015-01-08 08:13 17:51
2015-01-09 08:08 11:57 13:03 17:10
2015-01-10 08:18 08:20 11:20 12:01
2015-01-12 08:05 11:59 12:00 12:4
2015-01-13 08:16 17:33 17:35
2015-01-14 08:05 12:00 12:45 18:05
2015-01-15 07:43 12:00 12:38 17:09
2015-01-17 08:29 12:45 16:57
2015-01-19 08:02 18:28
2015-01-21 07:50 07:52 12:07 12:34


The requirements given to me by the HR are to show also the dates that they have no attendance with. Can somebody help me with this problem.

Answer

Depending on how did you approach the problem, you can generate all dates in range in php like this:

private function generateAllDates($from, $to){
    $from = gmdate("Y-m-d", strtotime($from));
    $to = gmdate("Y-m-d", strtotime($to));
    $allDates[] = $from;
    $tempDate = $from;
    while($tempDate < $to){
        $tempDate = gmdate("Y-m-d", strtotime("+1 day", trtotime($tempDate)));
        $allDates[] = $tempDate;
    }
    return $allDates;
}

and then for each date output sql data or nothing.

Alternatively you can generate all dates like and cross join with your output:

CROSS JOIN (
    SELECT DISTINCT DATE_FORMAT(updated, '%Y-%m-%d') as day FROM YOUR_TABLE_NAME
    WHERE updated BETWEEN "2015-07-09" - INTERVAL 60 DAY AND "2015-07-09" order by day DESC
) dates