I’m trying to create an news module and everything works allmost fine.
The issue is that when creating a news article you are ablle to set an startdate and an enddate for the news…If you don’t set the enddate the enddate would look like this in my db table:
0000-00-00 00:00:00
Thats OK I think, when I do this:
$thisdate = date("Y-m-d 00:00:00");
mysql_query("SELECT * FROM news WHERE startdate <= '$thisdate' AND enddate >= '$thisdate' AND user_id=".$user." ORDER BY id DESC");
I dont get the news out with no enddate set… How do I solve this?
CREATE TABLE news ( id int(11) NOT NULL AUTO_INCREMENT, fk_owner_id int(11) NOT NULL, title varchar(255) COLLATE utf8_unicode_ci NOT NULL, content text COLLATE utf8_unicode_ci NOT NULL, startdate datetime NOT NULL, enddate datetime NOT NULL, img varchar(255) COLLATE utf8_unicode_ci NOT NULL, imgtype int(11) NOT NULL, active int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Working with datetimes that don’t allow null values is about as fun as playing in NYC traffic.
Course, what’s really fun is when you come across legacy code that expects 0000-00-00 to be the default, and setting things to null to gain sanity borks the hole system.
Now I realise why my suggestions were ONLY working locally.
I tested @jmansa’s submitted script on a table with a field set to TIMESTAMP with a default of TIMESTAMP and with attributes set to “on update to CURRENT_TIMESTAMP”. Date settings which seem sensible rather than a default to a meaningless none or NULL.
Surprisingly when the field was changed for testing to datetime the datetime values were retained. This is why my script worked locally.