[RESOLVED] math with php / mysql

[severndigital]

basically,
i need to calculate hours worked.

currently i have a mysql database housing worker info in one table and the timesheet information in another table.

i am gathering the timesheet information into the following fields

id
workerid (from the worker table)
datein (injected with the php command date(Y-m-d)
timein(injected with the php command date(HⓂs)
dateout (same as above)
timeout (same as above)

i have a range of date that need to be calcuated in two different variables

$paystart = 2006-07-01;
$payend = 2006-07-15;

i need to select the entries in the timesheet table and

1. sort them by worker id
2. get the entries from within the range
3. subtract $timein from $timeout in each entry
4. total the result from 3 into a variable.

my main question is, is there a way to do this using a single sql statement?
or will i have to run multiple querys and process them through multiple loops?

i know this is a tough one.

also, if changing the way the timesheet data is housed would help in the math process i am willing to make any changes nessicarry.

thanks in advance.

chris

[tsinka]

Hi,

one question:

Can datein and dateout in one record span over more than one day ? Or can it happen that datein/timein is something like 2006-07-10 23:50 and dateout/timeout 2006-07-11 01:25 ?

Thomas

[severndigital]

Good Question.

the answer is no.

the entries will be for a single day only.

thanks,
chris

[tsinka]

Try something like this:

  SELECT w.id, SUM(TIME_TO_SEC(TIMEDIFF(t.timeout,t.timein))) AS total 
    FROM worker w, timesheet t
   WHERE w.id = t.workerid
     AND t.datein <= '2006-07-15'
     AND t.dateout >= '2006-07-01'
GROUP BY w.id
ORDER BY w.id

That should select the total in seconds grouped by worker id. But you should make sure that there are no overlapping date/time ranges for one worker because the total would be too high in that case.

That query calculates the data based on all records where datein<rangeend and dateout>rangestart (all records within the specified range).

If you want to get the total for all workers (including 0 for those workers that don't have any records in the given range) then use a left join.

Thomas

[severndigital]

i ran that query and got the following

the used

$row = mysql_fetch_array($gettime);

echo $row[0];

i originally got a recource #4 error, becuase i was echoing the var, and not the array.

did i do this correctly or am i missing something.

thanks,
chris

[tsinka]

that is a hint that you forgot to get the row but just echoed the mysql result id. Do something like:

// connect to database, $dbLink is the link identifier
$strSQL = "the query i posted";
$hndRes = mysql_query($strSQL, $dbLink);

while ($row = mysql_fetch_assoc($hndRes)) {
    echo "ID: ".$row["id"]." - ".$row["total"]."<br>\n";
}

That should list the ids and the total in seconds for each id.

Thomas

[severndigital]

ahh .. yeah .. thank you so much.

whoo hoo!!!

thank you thank you thank you thank you!!!