8 replies to this topic

[newphpcoder]

Good day!

I have 2 columns one for timein and one for timeout...and the data is from uploaded .xml file, my problem now is the saving of timeout in database..

in my .xml file I have this data:

Emp no Time In Time Out
100603 10/1/11 7:30 AM 10/1/11 6:00 PM
100603 10/2/11 8:00 AM 10/2/11 6:30 PM


and it saves in db
Emp no Time In Time Out
100603 2011-10-01 07:30:00 2011-10-01 18:00:00
100603 2011-10-02 08:00:00 2011-10-01 18:30:00

I want the timein and time out is formatted in 12 hours. As you can see the time out is formatted in 24 hours.

Thank you...

[Orjan]

computers don't use 12h format (12h format is only a presentation setting of the normal 24h). you need to reformat it yourself on output. use the date() function in php to modify how it's outputted.

[newphpcoder]

Thank you....

[Orjan]

Moderation notice: You now have 4 threads active in this category for one issue. Please stick to one thread, you don't get more help for posting many threads, you'll rather get less help if you spam with threads.

[newphpcoder]

Ok....I'm Sorry...I will not do it again

[Orjan]

try this in php, then save the data wherever you like directly. you don't need to calculate in db with this.

$date1in = "2011-10-01 07:30:00";$date1out = "2011-10-01 18:00:00";
$diff = date("h:i:s", strtotime($date1out) - strtotime($date1in));echo "from ". $date1in. " to ". $date1out. " is ". $diff. " hours";

[newphpcoder]

I tried this code:

<?php 

  $con = mysql_connect("localhost", "root","");

if (!$con) { 

  die(mysql_error());

}

$db = mysql_select_db("db_upload", $con);

if (!$db) { 

  die(mysql_error());

}

  

  $date1in = "2011-10-01 07:30:00";

  $date1out = "2011-10-01 18:00:00";

  $diff = date("h:i:s", strtotime($date1out) - strtotime($date1in));

  echo "from ". $date1in. " to ". $date1out. " is ". $diff. " hours";  

  

  $sql = "INSERT INTO employee (timein, timeout, total) VALUES ('$datelin', '$datelout', '$diff')";

  mysql_query($sql, $con);

?>

the output is:
from 2011-10-01 07:30:00 to 2011-10-01 18:00:00 is 10:30:00 hours

and it did not save in database.

I have revise code that encountered problem in thread compute attendance hours. This is the revise code.

Thank you

[Orjan]

the error now should be that you wrote "datelin" instead of "date1in". it's an number one, not a small letter L in my code.

[newphpcoder]

Still it did not save...I have query to sum the total hour

select  sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein)) AS totalhours from employee;

the problem is when the time is so early or late like this example:
100603 10/1/11 5:35 AM 10/1/11 1:35 PM // this is the exact schedule of employee and its 8 hours per day he time in exactly and timeout exactly also, so no problem in computing because it is 8 hours.
100603 10/2/11 5:25 AM 10/2/11 1:55 PM //in this example data the employee time in early in his schedule and time out late. It should be only 8 hours.
100603 10/3/11 5:40 AM 10/3/11 1:40 PM // in this example data the employee time in is late, so even he also late to timeout there’s no exemption because he late on his work. So it should has deduction or minus in his total hours.

and it saves it database:
100603 2011-10-01 05:35:00 2011-10-01 13:35:00
100603 2011-10-02 05:25:00 2011-10-01 13:55:00
100603 2011-10-02 05:40:00 2011-10-01 13:40:00


I want to accomplish is to get the total hours of the employee based on the employee no. And even the employee get timein early before his time or late to timeout the hours computed only 8 hours. Honestly, I don’t have idea how can be possible it is.

and I tried this code for computing the hours per day:

select  sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein)) AS totalhours from employee;

and the result of this code is:

totalhours:
08:00:00
08:30:00
08:00:00



and the result is

the first is correct because the real schedule is 5:35 AM - 1:35 PM
the second is wrong it should be 8 hours only even he timein early and timeout late.
the third is also wrong because the employee is late to timein, even he also timeout late., it should be deduct or subtract in hours the late of employee.
In my query it only sum the total hours...Someone told me that i need condition for that, but I have no idea how?
Thank you...