Michal Patriak Michal Patriak - 6 months ago 14
SQL Question

Need to figure out total hours between check-in and check-out times

I am trying to create a check-in/check-out table in my database. My check-in form works without issue, inserting the time into my database. The problem occurs when I try to check out. Everything is good on the first entry...

enter image description here

But when I try to check in and check out again, this happens...

enter image description here

So far so good, but when I check out...

enter image description here
Currently, my code updates the

out
column and
totalTime
column of all matching
child_id
's.

Here is my code:

// Select the correct child from the database
$sql_childID = "SELECT id FROM child
WHERE firstName = '$childFirstName'
AND lastName = '$childLastName'";
$result = $pdo->query($sql_childID);
$row = $result->fetch();
$var = $row['id'];

// Insert the check out time for the child
$query = "UPDATE checkinout
SET `out` = :nowTime
WHERE child_id = $var
AND `in` IS NOT NULL";
$statement = $pdo->prepare($query);
$statement->bindValue(':nowTime', date("YmjHis"));
$statement->execute();

// Select check in time for specified child
$sql_inTime = "SELECT `in` FROM checkinout
WHERE child_id = $var";
$inResult = $pdo->query($sql_inTime);
$inRow = $inResult->fetch();
$inTime = strtotime($inRow['in']);

// Select the check out time for specified child
$sql_outTime = "SELECT `out` FROM checkinout
WHERE child_id = $var";
$outResult = $pdo->query($sql_outTime);
$outRow = $outResult->fetch();
$outTime = strtotime($outRow['out']);

// Find total hours
$totalTime = abs($outTime - $inTime)/(60*60);

// Update totalHours column for specified child
$queryTotalTime = "UPDATE checkinout
SET totalTime = :totalTime
WHERE child_id = $var
AND 'out' IS NOT NULL";
$statement = $pdo->prepare($queryTotalTime);
$statement->bindValue(':totalTime', $totalTime);
$statement->execute();

Answer

I think you could do all of this in your first update statement using TIMESTAMPDIFF rather than figuring the total time with PHP:

UPDATE checkinout
SET 
    out = NOW(),
    totalTime = TIMESTAMPDIFF(SECOND, `in`, NOW()) / 3600
WHERE
    child_id = $var
    AND out IS NULL

The criteria WHERE out IS NULL will only update rows that do not have a value in the out column yet.