Need help in ON DUPLICATE KEY with Time Involve

[newphpbees]

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:

<?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');
?>

 

[Maq]

All the syntax you need is right in the manual - http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html