This is an archived copy of the forum for reference purposes

fRecordSet with date NULL using Build method

posted by ramascaro 8 years ago

Hi! I'm using MySQL database. I have to get records where a datetime field is null.

$result = fRecordSet::build('Socio', array('apellido~'=>$q,'fecha_baja='=>'0000-00-00 00:00:00'), array());

This statement give me this sql sentence:

SELECT socios.* FROM socios 
WHERE socios.apellido LIKE '%mas%' AND socios.fecha_baja = NULL  
ORDER BY socios.socio_id ASC

But it doesn't work, because it executes socios.fecha_baja = NULL and it would be socios.fecha_baja IS NULL, to be recognized by MySQL.

How could I fix it?

Thanks a lot!

Ral

While MySQL considers 0000-00-00 00:00:00 a valid date, PHP and most other databases do not. Thus fDatabase is converting it to NULL to prevent SQL errors with most databases.

If you actually want to check for a NULL value, you should use the following code:

$result = fRecordSet::build('Socio', array('apellido~' => $q, 'fecha_baja=' => NULL));

I personally would recommend against using 0000-00-00 00:00:00 even with MySQL since it is kind of a fake NULL value, so you might as well use NULL instead.

posted by wbond 8 years ago

It works!

Thanks wbond!

posted by ramascaro 8 years ago