Issue in saving 0000-00-00 00:00:00 or IsNull values

[newphpcoder]

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]

test it by inserting a row of literal values...

INSERT INTO payroll.reg_att(EMP_NO, LOGIN, LOGOUT, TotalHours)
VALUES ( 11111, '2012-02-29' , '0000-00-00' , 937 )

[newphpcoder]

test it by inserting a row of literal values...

INSERT INTO payroll.reg_att(EMP_NO, LOGIN, LOGOUT, TotalHours)
VALUES ( 11111, '2012-02-29' , '0000-00-00' , 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]

perhaps you could do a SHOW CREATE TABLE for further investigation

[newphpcoder]

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]

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

[newphpcoder]

Something what?

Thank you

[r937]

something else

[newphpcoder]

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]

i don't do php but that doesn't look valid

[newphpcoder]

I solve by adding

OR DATE(LOGOUT) = '0000-00-00'

Thank you