Need Help with a very simple Employee Time Clock

[madjack87]

I have read almost every forum post I could find on this subject and still couldnt find what I am looking for.

 

What I need this program to do is allow an employee to punch in and punch out as often as needed. Then I need to be able to calculate the total time they were punched in for that day.

 

This is what I currently have set up.

 

The Database

id(auto inc) -- employee -- date -- time

 

Once the employee logs in I have a button they can click which will create a record in the database. What I need is to be able to do is run a mysql_query and show their total time.

 

I know there is still a lot left to script and code still. But I wanted to know if I am on the right track with the database. I wasnt sure if I should have two buttons "clock in" and "clock out" and record them in different columns, or if having one column for both clock in and out was sufficient for calculating hours.

 

I do not need the ability to change hours for an admin because I will be able to manually go into the database if need be. What I do need is to be able to keep this records so that you can look at past weeks.

 

<?php 
$user = $_SESSION['myusername'];
$result = mysql_query("SELECT * FROM hours WHERE member='$user'");

while ($row = mysql_fetch_array($result)){
echo $row['date'] . " " . $row['time'] . "<br />";
}die (mysql_error());

?>

 

This is what I currently have just to display all the records from the logged in user. I need to be able to subtract time and organize by date then by week.

 

Any help will be appreciated.

 

Thanks in advance.

[JakeTheSnake3.0]

Perhaps the database should just hold time-in and time-out data.  The type would be INT which would hold the Unix Timestamp (generated by PHP's call to time()).  You would then format the timestamp when needed using PHP's date() function.  This way sorting the timestamps is much simpler as they're all just integers.  Extracting duration from integers is pretty easy, no?

[madjack87]

Well right not it is using the built in mysql time and date function. CURTIME() CURDATE().

 

What would be your reasoning for the time in and time out? Do you have any advice on how I can create the while loop to subtract the two times?

 

I am still looking for help on this so any input is valued.

[Eiolon]

The database needs a way to know if a time is clocked in / clocked out so it knows which times to subtract.

 

If you have an employee that has clocked in and out 6 times in one day, how would it know which two to subtract?

 

If an employee clocks in 2 times, but only clocked out 1 time, it needs to know not to subtract the last time clocked in because there has been no clock out yet.

[JakeTheSnake3.0]

You don't need to use a while loop to subtract something.  But given the complex nature of what can possibly go wrong with a time card (human error) you'll most likely be using a lot of IF statements.

 

Building on what Eiolon said and what I'm implying, is that if you only have a time entry with no way to determine whether or not it was an in/out scenario, you could end up with something like this:

 

1) User 'punches in' at 9:00AM on Friday

2) User forgets to 'punch out' at the end of the day

3) User 'punches in' at 9:00AM on Saturday

4) ...User profits 24 hour work shift!??!  If you assume that "every other" time entry is an "out", this is the scenario you get!  The time table gets completely screwed up because you didn't account for human error.

 

There's really a lot to this if you don't want to run into problems.  However this all can be done in pseudo-code and really doesn't have much specifically to do with PHP.

[madjack87]

Ok so A clock in and clock out seems to be necessary. Well thats easy enough for me.

 

What I really need/needed help with is how to subtract times. I would like to be able to run a query so that they can see there current weeks hours.

 

Monday 

Clock in 8am

Clock out 4pm

 

Tuesday

Clock in 8am

Clock out 11:30am

Clock in 12:00pm

Clock out 4pm

 

Etc.

 

Totals hours 42.5

 

 

I have no ideal where to start. Creating the database is easy as is running basic mysql query.

 

Any Thoughts?

               

[JakeTheSnake3.0]

Even if you store the timestamps in the database in a different way, you can use PHP's strtotime() function to convert the timestamp into a UNIX timestamp (which is just an integer).  The best way to test out things like this is to make a dedicated "test page".  For example:

 

My domain is "youbetcha.net".  I have a password-protected test directory right beneath it which holds a single .php file called 'index.php'.  So to do simple testing, I'd go to "http://youbetcha.net/test/".  In the index.php file, I'd just have a vanilla HTML layout with a simple PHP script testing whatever it is I wanted to test.

 

So in this case, you'd put the following in your test file:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" style="width:100%">
<head>
<title>File Content-Type Test</title>
</head>
<body>

<?php

$datein = 'September 5, 2010';
$dateout = 'September 6, 2010';

$di = strtotime($datein);
$do = strtotime($dateout);
// Seeing as how UNIX timestamps are just integers, use simple subtraction.
// Keeping in mind that they are still integers, $diff will also be an integer.
// Seeing as how UNIX timestamps are measured in seconds since Unix Epoch
// We need to do some basic math to convert it into hours.  Rounding is up
// to you.  See PHP's documentation on time() @ php.net/time
$diff = $do - $di;

echo 'Date In: ' . $datein . ' > Unix Timestamp: ' . $di . '<br />';
echo 'Date Out: ' . $dateout . ' > Unix Timestamp: ' . $do . '<br />';
echo 'Time Difference (Hours): ' . ($diff/60)/60;

?>

</body>
</html>

[QuickOldCar]

Ever consider using http://timeclock.sourceforge.net/index.php

 

The hard work is already done for you.

[PFMaBiSmAd]

If you store the matching in/out DATETIME pairs together in one row (id, user_id, datetime_in, datetime_out) you can perform the calculation for the pair directly in the query when you select the data and you can even sum the totals for each user_id per time period directly in the query. See the mysql timediff function for example - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff