Hi,
I notice that in my query:
$result = mysql_query("INSERT INTO payroll.reg_att(EMP_NO, LOGIN, LOGOUT, TotalHours)
SELECT DISTINCT a.EMP_NO, a.LOGIN, a.LOGOUT, TIMEDIFF(a.LOGOUT, a.LOGIN) FROM payroll.date_upload d, attendance.employee_attendance a JOIN hris.employment em ON (a.EMP_NO = em.EMP_NO AND em.STATUS = 'Reg Operatives') WHERE DATE(LOGOUT) BETWEEN '$FROM_DATE' AND '$TO_DATE' AND d.EMP_STATUS = 'Reg Operatives' OR ISNULL(LOGIN) OR ISNULL(LOGOUT)")
or die(mysql_error());
it did not insert data where LOGOUT is 0000-00-00 00:00:00
Thank you
r937
April 23, 2012, 10:53am
2
test it by inserting a row of literal values…
INSERT INTO payroll.reg_att(EMP_NO, LOGIN, LOGOUT, TotalHours)
VALUES ( 11111, '2012-02-29' , [COLOR="#FF0000"]'0000-00-00' [/COLOR], 937 )
r937:
test it by inserting a row of literal values…
INSERT INTO payroll.reg_att(EMP_NO, LOGIN, LOGOUT, TotalHours)
VALUES ( 11111, '2012-02-29' , [COLOR="#FF0000"]'0000-00-00' [/COLOR], 937 )
LOGIN and LOGOUT is DATETIME type. My concern is why in LOGIN it saves 0000-00-00 00:00:00 but in LOGOUT not
Thank you
r937
April 24, 2012, 1:31am
4
perhaps you could do a SHOW CREATE TABLE for further investigation
this is the result of:
SHOW CREATE TABLE reg_att;
CREATE TABLE reg_att
(
REF_ID
int(11) NOT NULL AUTO_INCREMENT,
EMP_NO
varchar(20) NOT NULL,
LOGIN
datetime DEFAULT NULL,
LOGOUT
datetime DEFAULT NULL,
TotalHours
time DEFAULT NULL,
Rendered
time DEFAULT NULL,
Late
time DEFAULT NULL,
Exceed_Hour
time DEFAULT NULL,
PRIMARY KEY (REF_ID
)
) ENGINE=MyISAM AUTO_INCREMENT=113833 DEFAULT CHARSET=latin1
Thank you
r937
April 24, 2012, 1:48am
6
i tested your CREATE TABLE statement and it created the table successfully
i added three rows:
INSERT
INTO reg_att
VALUES
( 12345, 'empl#1', '2012-04-04 09:37', '2012-04-04 21:55', '12:18', '12:00', '00:18', '00:00' )
,( 56789, 'empl#2', '0000-00-00 00:00', '2012-04-04 21:55', '12:18', '12:00', '00:18', '00:00' )
,( 33333, 'empl#3', '2012-04-04 09:37', '0000-00-00 00:00', '12:18', '12:00', '00:18', '00:00' )
;
then i retrieved them:
SELECT *
FROM reg_att;
everything is as expected
therefore, you must be doing something else
because there is no problem inserting a value of ‘0000-00-00 00:00’ into either the LOGIN or LOGOUT column
Now, I realize why the LOGOUT = 0000-00-00 00:00:00 was not save because of this condition:
$FROM_DATE = 2012-03-01
$TO_DATE = 2012-03-15
WHERE DATE(LOGOUT) BETWEEN '$FROM_DATE' AND '$TO_DATE'
now how can I get even the DATE(LOGOUT) = 0000-00-00
Thank you so much
r937
April 24, 2012, 11:15am
10
i don’t do php but that doesn’t look valid
I solve by adding
OR DATE(LOGOUT) = '0000-00-00'
Thank you