1. Registered User
Join Date
Jan 2012
Location
pune
Posts
16

Hello,
I have a table called "logs"

Userid Date inout_time
1 01-01-2012 09:00 am
1 01-01-2012 09:15 am
1 01-01-2012 11:02 am
1 01-01-2012 12:59 pm
1 01-01-2012 15:55 pm
1 01-01-2012 17:59 pm
2 01-01-2012 09:10 am
2 01-01-2012 10:45 am
2 01-01-2012 01:02 pm
2 01-01-2012 18:02 pm

Now i want to calculate timings of an employee inside and outside office .....
In coding language for employee userid 1 first punch is inside (09:00) , 2nd punch(09:15) is outside and 3rd punch(11:02) is inside and so on....

Now i have to calculate how much time he's inside office and outisde office in PHP

I am trying it using for loop like this ..

\$qry="select inout_time from logs order by userid,inout_time";
\$result=mysql_query(\$qry);
while(\$data=mysql_fetch_assoc(\$result))
{
\$inout[]=\$data['inout_time'];
}

// \$total_punches =count(\$inout);
for(\$i=1;\$i<= \$total_punches;\$i++)
{
// ??
}

Here (in for loop) i dint understand how to write code to store total inside office timings and outside office timings

It should be like
inside_office = (09:00 + 09:15) + (11:02 + 12:59) + ...
outside_office =(09:15 + 11:02) + (12:59 + 15:55) + ...

Hope now you understand my question .. :

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
ok so what you need to do is use the PHP date time fucntions to find the elasped time per activity.
Im assuming you work on the basis that timings alternate. ie odd timings (in the sequence) are clocking on, even timings are clocking off
when ordered by userid and in_outtime Nos 1,3,5,7 are alwasy clocking on, 2,4,6,8 are always clocking out.

it may depend on how you have stored the those values. Im surprised to find separate columns called date and in_outtime. in an ideal world anything that is date/time related should be on a single datetime datatype. doing so makes any mathmatical operation on the data much much easier. there;s a whole suite of functions in PHP (and also virtually all languages and database engines) for handling such data. in PHP they can be found here:-
PHP: Date/Time Functions - Manual

so effectively you need to subtract the preceeding value from the current value, assuming the current value is an even number in the series. the psuedo code would be something like this:-

is_clockon = true 'identifies if this record is a clock on or clock off value
'on startup it will always be a clockon
clockontime 'will store the vlaue of the clock on
elapsedtime = 0 'will store the elapsed time in minutes
do loop
if is_clockon=true then
clockontime = curent value
else
ThisTimeAtOffice = datediff(currentvalue,clockontime)
elapsedtime = elaspedtime + format(timeatoffice,"%i)
endif
//next row

if you also needed the clock off time, then tke not of the first value and subtract it (using datediff) from the last value.

3. Registered User
Join Date
Jan 2012
Location
pune
Posts
16
Thanks Healdem...