Hi,
I have built an app for the user's at the company I work for, that is, basically, an electronic punch-card system. There are several buttons that they can click, which enter information into a MySQL database for post-processing by a PHP script that the accountant can execute. This allows her to get weekly summaries of the user's work hours, break times, etc. There is a 'begin work' button, and it puts the following info into the db:
- eventID [autonumber]
- userIP [Their IP]
- userName [The username they're logged onto the pc with]
- userTime [unix timestamp]
- userEvent [1 = beginning work, 2 = finishing work, 3 = beginning break, 4 = finishing break]
There are also 'finish work', 'begin break', and 'finish break' buttons, which enter in the other userEvent's as above.
That all works fine, however now that I'm making the stats page, the headaches begin.
I am doing this at the moment:
I have a form with a dropdown listbox which lists all the user's in the database, and two javascript calendars to allow the accountant to select a date range. I then get the dates and use mktime() to get the unix timestamp at midnight of the days they selected. I put this into a query, alongwith the user events I want to search for; in this case userEvent's 1 and 2 (begin & end work), and the username.
The query will look something like this:
SELECT * FROM tbl_timelog WHERE userName='AndrewW' AND userTime BETWEEN 1115560800 AND 1116770400 AND userEvent='1' OR userEvent='2' ORDER BY userTime ASC
In case I'm doing the query incorrectly, this is what I want it to do:
Select everything from tbl_timelog if the following critieria is matched:
- userName is AndrewW
- The date of the event falls between: May 9, 2005 and May 23, 2005
- The userEvent is 1 (Beginning work) or 2 (Finishing work)
I then set a variable to equal the number 1, and loop through the query's results. I create a multideminsonal array, and if the variable is 1 (because I'm sorting the query via timestamp, from earliest to latest, it will be 1-2-1-2, etc), I create a new array inside the first dimension using the eventID (not the userEvent, the eventID is the autonumber'd ID so it's always unique), then imcrement the variable.
If it is 2, I use array_push() to add the second bit of info into that dimension - For example, it might look like this:
Array
(
[1] => Array
(
[1] => 1115874714
[2] => 1115875075
)
[5] => Array
(
[5] => 1115942722
[6] => 1115942750
)
[7] => Array
(
[7] => 1116200741
[8] => 1116200808
)
)
That's the user coming and going from work three times in the specified time period. I can then add together the differences of all the timestamps, which'll give me the total time that they worked for the specified date period.
Once I've done that, I can then do the same thing for breaks, and subtract that total away from the total time they worked, to get the total time they should be paid for!
Phew. Hope you're still reading.
My actual question is as follows... Is this the best way to do this sort of thing? I'm not entirely happy having to rely so much on the user clicking the buttons in the correct order (having said that though, they have no choice; the buttons on the app they use are only enabled under certain conditions, for example, if they click begin work, they can only click begin break, or finish work, and once they click begin break, they cannot click finish work until they click on the finish break button).
I am also having some issues with the data itself. Doing a mysql_num_rows of the query, even when it falls outside of the date range specified, is still returning rows, and I'm not sure why. All in all, this is just confusing the heck outta me, and any help, no matter what it is, would be greatly appreciated! 🙂.
Thanks
