## User Tag List

1. Originally Posted by newphpcoder
My boss told me that I no need to add field for shift,

Help me...Thank you so much
I can't. Ask your boss. If he knows you can do it without that new field, then let him tell you how to do it.

2. I toldl him, but he told me i don't need to add shift because every week employment change their shift

3. Originally Posted by newphpcoder
I toldl him, but he told me i don't need to add shift because every week employment change their shift
I understand he said you don't need to add shift. But ask him how to do the query without shift.

4. he really don't know because he has no knowledge about it

5. I really don't know how to solve it, its urgent

6. Ok, so he tells you you don't need the shift, but he really has no knowledge about it. Sounds familiar

If you have to do it without the shift column, you have to decide the limits of the check in times.
For example:
- if an employee checks in between 10:35:01 and 17:35:00 then he's working the 13:35 - 21:35 shift
- if an employee checks in between 17:35:01 and 01:35:00 then he's working the 21:35 - 05:35 shift
- if an employee checks in between 01:35:01 and 07:00:00 then he's working the 05:35 - 13:35 shift
- if an employee checks in between 07:00:01 and 10:35:00 then he's working the 08:00 - 17:00 shift

Once you've decided those limits, then you can implement them in the update query.

7. Originally Posted by guido2004
Ok, so he tells you you don't need the shift, but he really has no knowledge about it. Sounds familiar

If you have to do it without the shift column, you have to decide the limits of the check in times.
For example:
- if an employee checks in between 10:35:01 and 17:35:00 then he's working the 13:35 - 21:35 shift
- if an employee checks in between 17:35:01 and 01:35:00 then he's working the 21:35 - 05:35 shift
- if an employee checks in between 01:35:01 and 07:00:00 then he's working the 05:35 - 13:35 shift
- if an employee checks in between 07:00:01 and 10:35:00 then he's working the 08:00 - 17:00 shift

Once you've decided those limits, then you can implement them in the update query.
Where i can put the limit?Sorry. I'm not familiar with that..

Thank you so much..

8. by limits, he was referring to the times within which it's obviously one shift and not another

10:35:01 and 17:35:00 would be examples of the limits for the 13:35 - 21:35 shift

these limits are your responsibility to decide

9. can you give an example coding for that?is it in query?
sorry, i really don't know how can I code it.

Thank you so much

10. Originally Posted by newphpcoder
sorry, i really don't know how can I code it.
come on, try

11. can you give me an idea and sample so i can try?

Thank you

12. Instead of the 'shift' criteria, you'll now use the 'checkin limits' criteria to decide what rows must be updated in each of the 4 update queries.
You put the 'shift' criteria in the WHERE clause.

Guess where you have to put the new criteria?

13. Not yet guess?

So you mean I will still used my update statement then in i will only change the where clause???

Thank you

14. Originally Posted by newphpcoder
So you mean I will still used my update statement then in i will only change the where clause???

Thank you
Yes

15. What column shoud i used in my where clause? the timein and timeout???

Thank you

16. Originally Posted by newphpcoder
What column shoud i used in my where clause? the timein and timeout???
yes

17. Okay, I will try later

I will update you what happen when I tried your suggested solution.

Thank you so much...

18. You mean I used this where code:
PHP Code:
``` WHERE timein BETWEEN  10:35:01 and 17:35:00  and timeout BETWEEN  10:35:01 and 17:35:00  ```
Thank you

19. Originally Posted by newphpcoder
WHERE timein BETWEEN 10:35:01 and 17:35:00 and timeout BETWEEN 10:35:01 and 17:35:00
sorry, no, not quite

let's disregard for the moment the fact that you have syntax errors (time values have to be properly delimited with single quotes, just like date values)

the main problem is logical

guido suggested this --
if an employee checks in between 10:35:01 and 17:35:00 then he's working the 13:35 - 21:35 shift
however, you want ~both~ the timein and timeout between 10:35:01 and 17:35:00

is that reasonable? does that make logical sense?

would an employee who is supposed to work the 13:35 - 21:35 shift actually punch out between 10:35:01 and 17:35:00?

20. You mean timeout between 10:35:01 - 17:35:00? yes he can timeout before his real timeout schedule...

Thank you

21. yeah, we know he can timeout before his real timeout schedule

what happens if he times in at 13:35 and times out at 21:00 (i.e. 30 min before his timeout schedule)?

your WHERE clause will exclude him, because you wrote AND timeout BETWEEN 10:35:01 and 17:35:00

as i said, this is a logical problem, not an sql proiblem

22. when he time in at 13:35 and timeout at 21:00 his rendered would be 7:30 because he timeout early.

23. Here is my syntax for importing data from .xml to database and I also the updating and insert data based on the import attendance.

PHP Code:
``` <?php \$data = array(); \$con = mysql_connect("localhost", "root",""); if (!\$con) {    die(mysql_error()); } \$db = mysql_select_db("payroll", \$con); if (!\$db) {    die(mysql_error()); } \$sql = "select * from attendance"; \$result =  mysql_query(\$sql, \$con); if (!\$result) {     die(mysql_error()); } \$total = mysql_num_rows(\$result); if (\$total > 0) {     \$sql = "delete from attendance";     \$result =  mysql_query(\$sql, \$con);     if (!\$result) {         die(mysql_error());     } } function add_employee(\$EMP_NO, \$Date, \$TimeIn, \$TimeOut)   {       global \$data;               \$con = mysql_connect("localhost", "root","");       if (!\$con){ die(mysql_error());}       \$db = mysql_select_db("payroll", \$con);       if (!\$db) {            die(mysql_error());       }       \$EMP_NO = \$EMP_NO;       \$Date = \$Date;       \$Date = substr(\$Date,0,-13);       \$TimeIn = \$TimeIn;       \$TimeOut = \$TimeOut;         \$sql = "INSERT INTO attendance (EMP_NO, DateAtt, TimeIn, TimeOut) VALUES ('\$EMP_NO', '\$Date', '\$TimeIn', '\$TimeOut')";       mysql_query(\$sql, \$con);              \$data []= array('EMP_NO' => \$EMP_NO, 'DateAtt' => \$Date, 'TimeIn' => \$TimeIn, 'TimeOut' => \$TimeOut);          }      if ( \$_FILES['file']['tmp_name'] )   {       \$dom = DOMDocument::load( \$_FILES['file']['tmp_name'] );                 \$rows = \$dom->getElementsByTagName( 'Row' );       global \$last_row;       \$last_row = false;       \$first_row = true;       foreach (\$rows as \$row)       {           if ( !\$first_row )           {                            \$EMP_NO = "";               \$Date = "";               \$TimeIn = "";               \$TimeOut = "";                                             \$index = 1;               \$cells = \$row->getElementsByTagName( 'Cell' );                          foreach( \$cells as \$cell )               {                    \$ind = \$cell->getAttribute( 'Index' );                   if ( \$ind != null ) \$index = \$ind;                                      if ( \$index == 1 ) \$EMP_NO = \$cell->nodeValue;                   if ( \$index == 2 ) \$Date = \$cell->nodeValue;                   if ( \$index == 3 ) \$TimeIn = \$cell->nodeValue;                   if ( \$index == 4 ) \$TimeOut = \$cell->nodeValue;                   \$index += 1;               }              if (\$EMP_NO=='' and \$Date=='' and \$TimeIn=='' and \$TimeOut=='') {                     \$last_row = true;               }                     else {                     add_employee(\$EMP_NO, \$Date, \$TimeIn, \$TimeOut);               }                 }           if (\$last_row==true) {               \$first_row = true;           }                else {               \$first_row = false;           }       }   }    //Update Total Hours   \$result = mysql_query("UPDATE attendance SET TotalHours = sec_to_time(unix_timestamp(TimeOut) - unix_timestamp(TimeIn))")   or die(mysql_error());       \$result = mysql_query("UPDATE attendance SET TotalHours = sec_to_time(unix_timestamp(TimeIn) - unix_timestamp(TimeOut)) WHERE Shift = 1")   or die(mysql_error());   //Update Rendered      \$result = mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') +              case                when time_to_sec(time(TimeIn)) < time_to_sec('05:35:00') then 0               else time_to_sec('05:35:00') - time_to_sec(time(TimeIn))             end +             case                when time_to_sec(time(TimeOut)) > time_to_sec('13:35:00') then 0               else time_to_sec(time(TimeOut)) - time_to_sec('13:35:00')             end            )")            or die(mysql_error());        \$result = mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') +              case                when time_to_sec(time(TimeIn)) < time_to_sec('21:35:00') then 0               else  time_to_sec('21:35:00') - time_to_sec(time(TimeIn))             end +             case                  when time_to_sec(time(TimeOut)) > time_to_sec('05:35:00') then 0                 else time_to_sec('05:35:00') - time_to_sec(time(TimeOut))                 end            )")            or die(mysql_error());                  //Update OT  \$result = mysql_query("UPDATE attendance SET OT = sec_to_time(time_to_sec(TotalHours) - time_to_sec(Rendered))")   or die(mysql_error());      //Insert Sum of Total hours   \$result = mysql_query("INSERT INTO earnings(EMP_NO, Hours) SELECT EMP_NO, sec_to_time(SUM(time_to_sec(Rendered))) FROM attendance GROUP BY EMP_NO")   or die(mysql_error());      ?>      <html>   <body>   <table>   <tr>       <th>Employee Attendance</th>   </tr>   <?php foreach( \$data as \$row ) { ?>   <tr>   <td><?php echo( \$row['EMP_NO'] ); ?></td>    <td><?php echo( \$row['DateAtt'] ); ?></td>    <td><?php echo( \$row['TimeIn'] ); ?></td>   <td><?php echo( \$row['TimeOut'] ); ?></td>   </tr>   <?php } ?>   </table>   </body>  </html> ```
I have the problem in 21:35:00 - 05:35:00
like for example:

timein 2011-10-25 21:25:00
timeout 2011-10-26 04:35:00
the output using the query:
rendered: 09:00:00
it should be: 07:00:00 because he timeout early...

and when I add this query for shifts: 13:35:00 - 21:35:00 some output in 05:35:00- 13:35:00 , and 21:35:00-05:35:00 is wrong:
PHP Code:
``` \$result = mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') +               case                when time_to_sec(time(TimeIn)) < time_to_sec('13:35:00') then 0               else time_to_sec('13:35:00') - time_to_sec(time(TimeIn))             end +             case                when time_to_sec(time(TimeOut)) > time_to_sec('21:35:00') then 0               else time_to_sec('21:35:00') - time_to_sec(time(TimeOut))             end             )")   or die(mysql_error());  ```

Thank you

24. I really don't know what would be the solution in my problem in shifts.

Thank you so much for your help....

25. In the WHERE clause, check only timein.
At least, if my logic is what you want. If not, tell us the logic that you want to use to decide what shift the employee is in.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•