Compare each array date to mysql date in php

Question

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.

Have you already tried to solve the problem by your self? What code did you use? — Erveron
– Erveron, Commented Sep 24, 2015 at 8:59
Yes, I used sql query "SELECT * FROM attendance WHERE EmployeeID = :employeeid AND ValidDate BETWEEN DATE(:fromDate) AND DATE(:toDate) ORDER BY ValidDate ASC". — Newbie N
– Newbie N, Commented Sep 24, 2015 at 9:03

MaK · Accepted Answer · 2015-09-24 09:02:13Z

1

Use a LEFT JOIN instead of an INNER JOIN, that'll do.

I guess you are doing employee INNER JOIN employee_attendance, rather do employee LEFT JOIN employee_attendance, which'll fetch all the employee results irrespective of whether it's present in the employee_attendance or not.

answered Sep 24, 2015 at 9:02

MaK

1104 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Newbie N Over a year ago

In mysql table I only have records of dates that the employees have attendance with. I want to display the result like a report showing also the dates that they don't have attendance in the selected range.

MaK Over a year ago

You must be having separate table for keeping employee data & their attendance, Right? Can you share some sample records from the table?

MaK Over a year ago

I saw your query just now. You must be having an employee table, which has employee_id, employee_name blah blah. You can modify your query as such:

SELECT * FROM employee e LEFT JOIN attendance a WHERE e.EmployeeID = a.EmployeeID WHERE a.EmployeeID = :employeeid AND a.ValidDate BETWEEN DATE(:fromDate) AND DATE(:toDate) ORDER BY a.ValidDate ASC

Newbie N Over a year ago

my problem was not about joining the two tables but about printing dates even the employee have no attendance in that given date range check my expected output above and the output that i generated.

MaK Over a year ago

I have created a sample schema for you & ran the query as well. See it in action here: link

|

Zbigniew Kisły · Accepted Answer · 2015-09-24 09:18:25Z

0

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

edited Sep 24, 2015 at 9:18

answered Sep 24, 2015 at 9:08

Zbigniew Kisły

7521 gold badge6 silver badges13 bronze badges

3 Comments

Newbie N Over a year ago

Yes, I can generate all dates like the one you post but how will I associate it to my mysql attendance records?

Newbie N Over a year ago

can i use sql query to perform the function stated above?

Newbie N Over a year ago

I'll try your solution but can you give me some more elaborate approach to get my expected output?

Collectives™ on Stack Overflow

Compare each array date to mysql date in php

2 Answers 2

7 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related