如何比较两个不同的SQL表并获得不同的值？

Question

I'm sorry about the title question, it is not very clear at what I want to do, but I didn't know how to explain, please read this for further explanation:

I have two tables, one is 'employees' which has employeesName and employeesInTime (time when they are supposed to get in work).

Then I have a seconde table where employees log the time they got in work, it has the rows employeesName, timeIn and timeOut.

What I need to do is implement a system where I can see all the 'in' hours of each employee and tell whether they got on time, late or didnt go to work at all.

Final report should look something like this:

Employees Name // In // Out // Month - Jan

                               1 / 2 / 3 / 4 / 5 / 6 / ......
john smith      8:00  17:00    P   P   P   L   M   P  .......

(P for in time, L for late and M for missing). It should also tell at what time they got in when you hover over the letter.

What I got so far is a table with all employees and the time they are supposed to get in and out.

For the other part (the monthly report) I'm completely stuck.

I got this:

$query2 = "select * from info where `inout`='in' ";
$result2 = mysql_query($query2);

if($result2 === FALSE) {
die(mysql_error()); // valid only on development stage for debugging
}

while($row = mysql_fetch_array($result2))
{
 $timeIn = "".$row['timestamp']."";
 $name = "".$row['fullname']."";
 $timeIn = $timeIn + @$tzo;
 echo '<strong>'.$name . '</strong>: ' . date($timefmt, $timeIn) .'<br />';
}

mysql_free_result($result2);

which gives me all the in's from employees, but problem is I get the same employee multiple times. What I need to get all the in's from one employee, for all the employees.

Any points on what direction should I go would be greatly appreciated. I think I should somehow join both tables to get the result I want, but no matter how hard I think I can't figure which query will get all the in's from one employee, and then get it for all the employees so it looks like the desired table report.

If this has already been answered in a previous question please kindly point me to it, and I'm very sorry I have googled and searched here at stackoverflow but haven't found anything similar.

Thank you for your time.

edit: here is the SQLFiddle

Answer 1

Writing a query, that returns 31 fields for the 31 days in the month would not be a good solution, therefore you're going to need a query, that returns each employee as many times, as long the month is.

I'll give a rough outline of what I would do, so you have work too, to figure out the details :). First create a table from the first table, that would have one array for each employee:

$employees = array( 
    'emp1' => array('name' => 'emp1', 'in' => 8, 'out' => 16), 
    'emp2' => array(...),
     ...);

Then query the second table for this month's ins and outs, and save it into the previous array:

foreach($row = mysql_fetch_array($result2)) {
    $day = ...; // count it from timestamp
    $timeOfDay = ....; // count it from timestamp
    $employees[ $row['name'] ]['checks'][$day][ $row['inout'] ] = $timeOfDay;
}

Yes, that is a complex array of arrays.

And lastly, print the report:

foreach($row = mysql_fetch_array($result2)) {
    $day = ...; // count it from timestamp
    $timeOfDay = ....; // count it from timestamp
    $employees[ $row['name'] ]['checks'][$day][ $row['inout'] ] = $timeOfDay;
}

foreach($employees as $employee) {
    print $employee['name'] . ':';
    // for loop through days, 
    // otherwise missed days would not be shown
    for( $day = 1; $day < $maxDay; $day++) {
        // it would be nice to check, whether 'in' and 'out' values both exist
        if (array_key_exists($day, $employee['checks'])) {
            if ($employee['in'] <= $employee['checks'][$day]['in'] &&
                $employee['out'] <= $employee['checks'][$day]['out']) {
                    print ' P ';
            } else {
                    print ' L ';
            }
        } else {
            print ' M ';
        }
    }
}