timeclock report help

[compgeek83]

I have made a simple script that has 2 links, one for an employee to clock in and one for them to clock out, this in turn updates a database that has the following fields, "id", "login", "time", "reason", where id is an auto increment, login is the username, time is the time of the "punch" and reason is a code of 1 or 2 (for now), with 1 really meaning clock in and 2 really meaning clock out.

 

What I need is a way to show this on a report, that is I select the employees username and it brings up a list of times worked between the selected dates.

 

Report

John Smith

Date                      In                  Out                      Total

2010-06-04          09:05            11:32                    02:27

 

 

any takers?  I'm stumped here

[TheBG]

So the first thing you need to do is write a query that retrieves all data with any (all) given ID.  Hint: use SELECT

[jcbones]

You are going to have problems with that set up.  It would be better to set your table up as "id","login","timein","timeout","real_date".

 

Run an Insert for clock in, and an update for clock out.

UPDATE `clock` SET `timeout`='$timeout' WHERE `login` = '$user' AND `real_date` = CURDATE()

 

Otherwise, you are going to try and make sure all of the `reason` = 1 dates, line up with `reason` = 2 dates, and account for lunch times, etc.

 

Pulling reports would be just a matter of finding the table data, and formatting with HTML.

 

[compgeek83]

You are going to have problems with that set up.  It would be better to set your table up as "id","login","timein","timeout","real_date".

 

Run an Insert for clock in, and an update for clock out.

UPDATE `clock` SET `timeout`='$timeout' WHERE `login` = '$user' AND `real_date` = CURDATE()

 

Otherwise, you are going to try and make sure all of the `reason` = 1 dates, line up with `reason` = 2 dates, and account for lunch times, etc.

 

Pulling reports would be just a matter of finding the table data, and formatting with HTML.

 

that works great, but how do I account for someone that takes a lunch break?  If the person clocks in and then out and then back in I have 2 entries for that day, when they clock out the second time it updates both entries to the time of the second clock out.

[ignace]

From what I see is that it actually does not matter, all you need to do is keep a log of clocks.

 

employees (id, ..)

clocks (employee_id, date_log);

 

Create a record each time an employee clocks-in or clocks-out. Your application would calculate the worked hours as the first is a clock-in and the next a clock-out followed by a clock-in again if they had a lunch break.

[compgeek83]

 

that works great, but how do I account for someone that takes a lunch break?  If the person clocks in and then out and then back in I have 2 entries for that day, when they clock out the second time it updates both entries to the time of the second clock out.

 

I believe I have everything down except the above problem,  can I make sure I only update 1 record with an update statement such as the following?

 

UPDATE tcc_timeclock SET timeout='$currtime' WHERE login='$employee' AND realdate= CURDATE() ORDER BY timein DESC LIMIT 1

 

it doesnt seem to work but from what I read it should only update 1 row after it orders all available rows (that match todays date and the employee in question) descending

[TOA]

 

that works great, but how do I account for someone that takes a lunch break?  If the person clocks in and then out and then back in I have 2 entries for that day, when they clock out the second time it updates both entries to the time of the second clock out.

 

I believe I have everything down except the above problem,  can I make sure I only update 1 record with an update statement such as the following?

 

UPDATE tcc_timeclock SET timeout='$currtime' WHERE login='$employee' AND realdate= CURDATE() ORDER BY timein DESC LIMIT 1

 

it doesnt seem to work but from what I read it should only update 1 row after it orders all available rows (that match todays date and the employee in question) descending

 

What's your pk? Figure that out and use DISTINCT

[compgeek83]

finally got it to work using the following update query

 

UPDATE tcc_timeclock SET timeout='$currtime' WHERE login='$employee' AND realdate= CURDATE() ORDER BY timein DESC LIMIT 1

 

turns out I didnt have a primary key assigned, once I did that to my "id" field it took right off, now it only updates the latest or newest record in the database that matches the employee in question and today's date