Are you doing this through some kind of web-based editor? Could be something's stripping out what looks to be an invalid HTML tag. Try reversing the order (so it's > ... < instead), or perhaps replacing them with the &lt;/&gt; entities and see if that helps.
–
Marc BJan 11 '11 at 19:05

4 Answers
4

You wouldn't happen to be running the $sql variable through the striptags() function, would you? This would be consistent with it stripping out "<= expirydate and expirydate >", as it would assume it to be an HTML tag.

I am not sure what is causing the problem. Apparently there is something stripping the html tags off as a sort of security maybe . One suggestion is to try to replace the '<' and '>' with their ASCII codes:

$sql .= '(expirydate NOT BETWEEN fromdate AND curdate()) AND (expirydate NOT BETWEEN fromdate AND 17530101) ORDER BY rand()

The 17530101 is meant to be the least value for a datetime possible. In the previous code you are checking if the expirydate isn't between fromdate and curdate() AND expirydate isn't less than any of them. That means that expirydate should be greater than fromdate,curdate for the statement to return true, which is what you are trying to achieve.

BETWEEN messes the code logic. expirydate shouldn't be between fromdate and curdata(). expirydate actually is checked to be greater than both fromdata and curdata(). It can be done with NOT BETWEEN: expirydate NOT BETWEEN fromdate AND curdate() AND expirydate NOT BETWEEN fromdate AND {basedate(maybe 0)} ORDER BY rand()
–
deadlockJan 11 '11 at 19:43

Nice idea. Unfortunately appending those strings doesn't help either. That var ends up with "select * from tbl_adsinfo where expirydate >= curdate() AND fromdate <" - everything after the lessthan sign is cut off.
–
doub1ejackJan 11 '11 at 19:52

Ok, I got it. The problem had nothing to do with PHP, it was Zend Studio and/or the XDebugger.

Getting Zend to use a debugger at all took me days and I'm sure it probably isn't entirely kosher the way I got it to run.

If you hover over a variable while debugging in Zend Studio a little window pops up to show you the contents of that variable (sometimes). This window does cut off anything that follows '<'. The variable still contains the correct string, but the IDE/Debugger is pretty misleading. Unfortunately since this particular character is a common problem because of html parsing this was a pretty confusing bug to identify. The issue is compounded if you try to compare output from a php page or if you are looking at very long output (for very long strings it seems you can only see 1024 characters in the debug variable window so the string may appear truncated).