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

Databases
1

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

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 )
3

LOGIN and LOGOUT is DATETIME type. My concern is why in LOGIN it saves 0000-00-00 00:00:00 but in LOGOUT not :frowning:

Thank you

4

perhaps you could do a SHOW CREATE TABLE for further investigation

5

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

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

7

Something what?

Thank you

8

something else

9

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

10

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

11

I solve by adding


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

Thank you