Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Need help in ON DUPLICATE KEY with Time Involve

    Hi....

    I want to know what syntax should i used for UPDATE or ON DUPLICATE KEY.
    Because now, I only have syntax for insert query.,I want to add syntax for update or DUPLICATE key.

    here is my code:
    Code:
    <?php
      include 'config.php';
      
      $currentEmpID = $_SESSION['empID']; 
      
      $DATE1 = $_GET['Regfirstinput'];
      $DATE2   = $_GET['Regsecondinput'];
      
        $smarty->assign('DATE1', $DATE1);
        $smarty->assign('DATE2', $DATE2);
    
       $result = mysql_query("INSERT INTO payroll.reg_att(EMP_NO, DATE_DTR, LOGIN, LOGOUT, TotalHours) 
    SELECT EMP_NO, DATE(LOGIN), LOGIN, LOGOUT, TIMEDIFF(LOGOUT, LOGIN)  FROM attendance.employee_attendance") 
     or die(mysql_error()); 
     
     
     $result = mysql_query("UPDATE payroll.reg_att SET Rendered =  case
    when time_to_sec(time(TotalHours)) <= time_to_sec('03:00:00')
    then sec_to_time(time_to_sec('00:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('20:35:00') AND time_to_sec('21:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('06:35:00')
    then sec_to_time(time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('04:35:00') AND time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('14:35:00')
    then sec_to_time(time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('12:35:00') AND time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('21:35:00') AND time_to_sec('22:35:00')
    then sec_to_time(time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('06:00:00') AND time_to_sec('07:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('16:00:00') AND time_to_sec('16:59:00')
    then sec_to_time(time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('17:59:00')
    then sec_to_time(time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('18:00:00') AND time_to_sec('18:59:00')
    then sec_to_time(time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('21:35:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('07:35:00')
    then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '21:35:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('15:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('21:35:00') AND time_to_sec('22:35:00')
    then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('07:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('15:35:00')
    then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:00:00') AND time_to_sec('07:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('16:00:00') AND time_to_sec('16:59:00')
    then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '07:00:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('16:00:00') AND time_to_sec('16:59:00')
    then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('17:59:00')
    then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('18:00:00') AND time_to_sec('18:59:00')
    then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('20:35:00') AND time_to_sec('21:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('03:35:00') AND time_to_sec('05:35:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('12:35:00') AND time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('18:01:00') AND time_to_sec('21:35:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('04:00:00') AND time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('09:01:00') AND time_to_sec('13:35:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('06:00:00') AND time_to_sec('07:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:01:00') AND time_to_sec('16:00:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '16:00:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:01:00') AND time_to_sec('16:00:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '16:00:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('14:01:00') AND time_to_sec('17:00:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '17:00:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('15:01:00') AND time_to_sec('18:00:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '18:00:00'))))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('21:35:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('03:35:00') AND time_to_sec('05:35:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '21:35:00'))) - time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('14:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('18:01:00') AND time_to_sec('21:35:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))) - time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('06:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('10:01:00') AND time_to_sec('13:35:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))) - time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:00:00') AND time_to_sec('07:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('12:01:00') AND time_to_sec('16:00:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '16:00:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '07:00:00'))) - time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('12:01:00') AND time_to_sec('16:00:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '16:00:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))) - time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('12:01:00') AND time_to_sec('17:00:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '17:00:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))) - time_to_sec('08:00:00'))
    
    when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('12:01:00') AND time_to_sec('18:00:00')
    then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
    (time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '18:00:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
    (time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))) - time_to_sec('08:00:00'))
    END")  or die(mysql_error());       
    
    $result = mysql_query("INSERT INTO payroll.reg_hours(EMP_NO, Hours) SELECT EMP_NO, sec_to_time(SUM(time_to_sec(Rendered))) FROM payroll.reg_att GROUP BY EMP_NO") or die(mysql_error()); 
    
    
    $sql = "SELECT MIN(DATE(LOGIN)) AS FDATE, MAX(DATE(LOGIN)) AS LDATE FROM payroll.reg_att";
    $rsDate = $conn2->Execute($sql);
    
    $FDATE = $rsDate->fields['FDATE'];
    $LDATE = $rsDate->fields['LDATE'];
    
    $smarty->assign('FDATE', $FDATE);
    $smarty->assign('LDATE', $LDATE);
     $smarty->display('header_att.tpl');
     $smarty->display('RegAttendance.tpl');   
     $smarty->display('footer.tpl');
    ?>

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    This has nothing to do with PHP, moving to MySQL forum.
    On duplicate key is identical to the update statement except you don't have a table to address, nor a where condition. See the documentation here: http://dev.mysql.com/doc/refman/5.0/...duplicate.html

    Also, I can't help but think that case/when is huge. Is there not a more generic statement you can use to calculate off of?

  • Users who have thanked Fou-Lu for this post:

    newphpcoder (12-23-2011)

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    This has nothing to do with PHP, moving to MySQL forum.
    On duplicate key is identical to the update statement except you don't have a table to address, nor a where condition. See the documentation here: http://dev.mysql.com/doc/refman/5.0/...duplicate.html

    Also, I can't help but think that case/when is huge. Is there not a more generic statement you can use to calculate off of?
    This the only way i think of...Do you have any idea?

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    When i tried this query:

    Code:
    INSERT INTO payroll.reg_att (EMP_NO, DATE_DTR, LOGIN, LOGOUT, TotalHours) 
    SELECT EMP_NO, DATE(LOGIN), LOGIN, LOGOUT, TIMEDIFF(LOGOUT, LOGIN) FROM attendance.employee_attendance 
    ON DUPLICATE KEY EMP_NO = EMP_NO, DATE_DTR = DATE_DTR, LOGIN = LOGIN, LOGOUT = LOGOUT, TotalHours = TotalHours;
    I got an error:

    Error Code : 1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EMP_NO = EMP_NO, DATE_DTR = DATE_DTR, LOGIN = LOGIN, LOGOUT = LOGOUT, TotalHours' at line 5
    (0 ms taken)

  • #5
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I tried this code:

    Code:
    INSERT INTO payroll.reg_att (EMP_NO, DATE_DTR, LOGIN, LOGOUT, TotalHours)
    SELECT a.EMP_NO, DATE(LOGIN) AS DATE_DTR, a.LOGIN, a.LOGOUT, TIMEDIFF(LOGOUT, LOGIN)  AS TotalHours FROM attendance.employee_attendance a
    ON DUPLICATE KEY UPDATE EMP_NO = a.EMP_NO, DATE_DTR = DATE_DTR, LOGIN = a.LOGIN, LOGOUT = a.LOGOUT, TotalHours = TotalHours;
    But when i run again this query... The data was inserted again, but it should be Update because it's already inserted

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    my reg_att table has fields:

    ROW_ID, EMP_NO, DATE_DTR,LOGIN,LOGOUT,TotalHours,Rendered


    Thank you

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Is row_id an auto-increment PK? If so and its not a composite, you can't use an on duplicate key, since it should never have a duplicate. If there is something else that makes it unique, then that will force a conflict.
    I'm pretty sure you can use the insert/select with a on duplicate update, but you may need to wait for old pedant or guelphdad to verify.

  • Users who have thanked Fou-Lu for this post:

    newphpcoder (12-28-2011)


  •  

    Posting Permissions

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