Counting amount of Hours based on 2 Rows !?

[techiefreak05]

I have made a system where you enter in your Employee ID and password, and enters a row into the DB. If the last row you entered has han "activityID" of "In", then it enters a new row with the "activityID" of "Out" and vice-versa...

 

What I need to do, is be able to count the amount of time between 2 rows, one IN and one Out. How can I achieve this?

 

Table Setup, "timePunch":

ID, empID, activityID, timeDate

 

ID = number of the row

empID = ID of the employee

activityID = "In", or, "Out"

timeDate = Unix time stamp of time the row was entered.

 

Any help at all would be VERY appreciated!

[fenway]

You should probably self-join this table, using the empID as the join condition, then limit t1 to in and t2 to out.  I'm assuming there's only one per person, otherwise you'll have to limit 1 in a derived query.

[techiefreak05]

There are multiple rows per person. Multiple "Ins" and "Outs"... so.. how can I join the table, Ive never used a join before.

 

**I take that back - I've used a join before, but Im not sure as to how use it in this situation.

[techiefreak05]

Ok, Ive decided to show you an example of what's in my database:

 

 

ID  |  activityID  |  empID  |  timeDate

-------------------------------------------

51          In            13240      1201814815--

                                                                |--I need to be able to combine these 2, and calculate the amount of hours worked.

52          Out          13240      1201819020--

 

 

53          In            13240      1201826696--

                                                                |--I need to be able to combine these 2, and calculate the amount of hours worked.

54          Out          13240      1201826703--

[techiefreak05]

I have figured out how to connect the 2 rows with each other!

If you are punching in, then create a random string and enters it into the "key" column, but if you are logging out, get the random string from the Punch In row and enter that string into "key" column!

 

Now that they are connected, I just need to figure out how to get the rows based on the key, and then do the math between the 2 rows, then add them all together..

[rhodesa]

Personally I think it would be better to structure your table as:

 

ID  | empID  | timeIn  | timeOut

 

Then you can have a query like:

 

SELECT ID,empID,(timeOut - timeIn) as timeDiff FROM table WHERE timeIn NOT NULL AND timeOut NOT NULL

 

[techiefreak05]

Oh duh, that would be easier... cuz Im not getting anywhere right now...

 

But.. I dont get what that query does. =)

[rhodesa]

If the data in the table looked like:

 

ID  |  empID  |  timeIn        |  timeOut

-------------------------------------------

51      13240      1201814815      1201818815

52      13240      1201819020      1201824020

53      13240      1201826696      1201826702

54      13240      1201826703

 

The query would return data like this:

ID  |  empID  |  timeDiff

-------------------------------------------

51      13240      4000

52      13240      5000

53      13240      6

 

Record 54 is omitted because there is no timeOut yet. Does that make sense?

 

[techiefreak05]

oh, so I could just do:

 

<?php 
$timeDiff=$r["timeDiff"];
echo date(H,$timeDiff) . "Hours Worked";
?>

 

That would show the difference, in hours?

[rhodesa]

timeDiff will be the difference in seconds between In and Out. So to get hours, divide it by 3600 (number of seconds in an hour):

 

<?php 
$timeDiff=$r["timeDiff"];
$hoursDiff = $timeDiff / 3600;
echo $hoursDiff . " Hours Worked";
?>

[techiefreak05]

Thanks a lot!

If you wanna wait a few minutes, I'll try it out. I'm working on modifying the database the way you suggested, and I'm almost done, and boy is it easier to!

[techiefreak05]

This works, for punching in, and then updating the DB for timeOUT

but Im missing something...

When There is already a row with the IN and OUT filled, its supposed to create a new row, with timeIN, but its not..

 

<?php
$now=time();

$cc=mysql_query("SELECT * from tp2 WHERE empID = '$_POST[emp_id]' ORDER BY `ID` DESC LIMIT 1") or die(mysql_error());

if(mysql_num_rows($cc)<1){

//PUNCH ME IN
mysql_query("INSERT INTO `tp2` (`empID`, `timeIN`)
VALUES ('$_POST[emp_id]', '$now');") or die(mysql_error());

header("location: success.php");
exit;

}else{

$r=mysql_fetch_assoc($cc);
$rowID=$r["ID"];
if($r["timeOUT"]==""){
	mysql_query("UPDATE tp2 SET timeOUT = '$now' WHERE ID = '$rowID' AND empID = '$_POST[emp_id]'") or die(mysql_error());
}else{
	mysql_query("INSERT INTO `tp2` (`empID`, `timeIN`)
	VALUES ('$_POST[emp_id]', '$now');") or die(mysql_error());
}

}
?>

 

NEVERMIND.. It worked after I tried a second time... wierd.. well now just to try the diff query, and I owe you my life. =)

[rhodesa]

<?php
  $now=time();
  $cc=mysql_query("SELECT * from tp2 WHERE empID = '$_POST[emp_id]' AND timeOut IS NULL ORDER BY `ID` DESC LIMIT 1") or die(mysql_error());
  if(!mysql_num_rows($cc)){
    //PUNCH ME IN
    mysql_query("INSERT INTO `tp2` (`empID`, `timeIN`) VALUES ('$_POST[emp_id]', '$now')") or die(mysql_error());
    header("location: success.php");
    exit;
  }else{
    //PUNCH ME OUT
    $r=mysql_fetch_assoc($cc);
    $rowID=$r["ID"];
    mysql_query("UPDATE tp2 SET timeOUT = '$now' WHERE ID = '$rowID' AND empID = '$_POST[emp_id]'") or die(mysql_error());
  }
?>

[techiefreak05]

It just hit me, How can I make a query that outputs 2 rows per row in the Databse.

Like, in a table, it alternates rows, for In and Out.

 

<table>

 

<tr>

<td>In</td>

</tr>

 

<tr>

<td>Out</td>

</tr>

 

</table>

 

How can I out put that with 1 row in the DB?

[rhodesa]

<table>
<?php
  $cc=mysql_query("...put query here...");
  while($row = mysql_fetch_array($cc)){
    print "<tr><td>In: {$row['timeIn']}</td></tr>";
    print "<tr><td>Out: {$row['timeOut']}</td></tr>";
  }
?>
</table>

[techiefreak05]

I think im getting it working !

 

Thank you so much, it was bugging me so bad!

 

If I have any more questions, I'll post. But I should be good for now.

[techiefreak05]

If the following displays the amount of hours, how can I show minutes?

 

<?php 
$timeDiff=$r["timeDiff"]; //column that was made by subtracting SMALLER unix timestamp from LARGER timestamp.
$hoursDiff = $timeDiff / 3600;
echo $hoursDiff . " Hours Worked";
?>

 

Thanks.

[rhodesa]

The $timeDiff is the different in seconds. So, to get the difference in minutes, you would divide, by 60 (60 seconds per minute). But if you are looking to get something like the difference between 7pm and 9:15pm is 2hrs 15mins, you would need to look at the remainder:

 

<?php 
$timeDiff=$r["timeDiff"]; //column that was made by subtracting SMALLER unix timestamp from LARGER timestamp.
$hoursDiff = $timeDiff / 3600; //Number of hours in timeDiff
$minsDiff = ($timeDiff % 3600) / 60; //Number of minutes left after hours
echo "{$hoursDiff}hrs {$minsDiff}mins Worked";
?>

[techiefreak05]

Thank you so much!

You've been a big help!

 

also, this snippet is in a while() loop, from an SQL query, how would i be able to add up all time and all minutes

 

=)

[rhodesa]

<?php
$totatTimeDiff = 0;
while(...){
  $timeDiff=$r["timeDiff"]; //column that was made by subtracting SMALLER unix timestamp from LARGER timestamp.
  $totalTimeDiff += $timeDIff; //Add to total
  $hoursDiff = $timeDiff / 3600; //Number of hours in timeDiff
  $minsDiff = ($timeDiff % 3600) / 60; //Number of minutes left after hours
  echo "{$hoursDiff}hrs {$minsDiff}mins Worked";
}
$hoursDiff = $totalTimeDiff / 3600; //Total Number of hours
$minsDiff = ($totalTimeDiff % 3600) / 60; //Total Number of minutes left after hours
echo "Total: {$hoursDiff}hrs {$minsDiff}mins Worked";
echo 
?>

 

[rhodesa]

Update: Since I posted this, I was educated on the abilities of gmdate(). If you pass it a difference in time, It will show the number of hours/mins/secs difference. Check out this post:

 

http://www.phpfreaks.com/forums/index.php/topic,182123.msg813789.html