jfquestiaux
Well-Known Member
I have a list with several dates elements. Some records have some dates filled in, others don't, so I am using the advanced search to look for records with empty dates.
For dates, there is no obvious way to do it because an "empty" date has actually '0000-00-00 00:00:00' in the database column, so an advanced search on "is empty" does not work.
A search on "date_element IS EQUAL TO 0000-00-00 00:00:00" works though, or at least should work because the search return an SQL error.
The problem is in the WHERE condition: the query produced has
WHERE ( `contacts`.`date_MdB` = (0000-00-00 00:00:00) )
where it should be
WHERE ( `contacts`.`date_MdB` = '0000-00-00 00:00:00' )
(this works when run directly in phpmyadmin)
Tested on Joomla 3.2.2/Fabrik 3.1 + latest GitHub (24).
By the way, it's not very user friendly to have the user enter '0000-00-00 00:00:00' as search value. Is there another way to look for empty date elements?
For dates, there is no obvious way to do it because an "empty" date has actually '0000-00-00 00:00:00' in the database column, so an advanced search on "is empty" does not work.
A search on "date_element IS EQUAL TO 0000-00-00 00:00:00" works though, or at least should work because the search return an SQL error.
The problem is in the WHERE condition: the query produced has
WHERE ( `contacts`.`date_MdB` = (0000-00-00 00:00:00) )
where it should be
WHERE ( `contacts`.`date_MdB` = '0000-00-00 00:00:00' )
(this works when run directly in phpmyadmin)
Tested on Joomla 3.2.2/Fabrik 3.1 + latest GitHub (24).
By the way, it's not very user friendly to have the user enter '0000-00-00 00:00:00' as search value. Is there another way to look for empty date elements?