[SOLVED] Comparing Time in conditional MYSQL query statement

[SchweppesAle]

hi, I'm looking to include a condition which compares the current time of day during our MYSQL query. However, I'm having some trouble.

 

$query = 'SELECT * FROM jos_keyword_keys WHERE (state = 0 AND (((DATEDIFF(CURDATE(), StartDate) >= 0 AND DATEDIFF(CURDATE(), EndDate) <= 0) OR (StartDate = 0000-00-00 AND EndDate = 0000-00-00)) AND (StartTime = 00:00:00 AND EndTime = 00:00:00)))';


$query = mysql_query($query);

 

before trying to implement the TIMEDIFF() method I decided to include the default StartTime and EndTime values, however even these don't seem to be working correctly. Is it the :?

[fenway]

I don't follow.... and you should always test the values of your expressions in the select column list too.

[SchweppesAle]

that's alright, I figured it out.

 

$query = 'SELECT * FROM jos_keyword_keys WHERE (state = 0 AND (((DATEDIFF(CURDATE(), StartDate) >= 0 AND DATEDIFF(CURDATE(), EndDate) <= 0) OR (StartDate = 0000-00-00 AND EndDate = 0000-00-00)) AND (((TIMEDIFF(CURTIME(), StartTime) >= 0) AND (TIMEDIFF(CURTIME(), EndTime) <= 0)) OR (StartTime = "00:00:00"  AND EndTime = "00:00:00"))))';