PHP: filter same column two times in query

Question

$result = mysql_query("SELECT * FROM users_msgs WHERE uID = '$USER' AND date<'$lastmsg' AND date BETWEEN $Yday AND $today ORDER by date DESC LIMIT 10");

Im getting 0 rows while there should be 1..

But my other query,

 $result = mysql_query("SELECT * FROM users_msgs WHERE uID = '$USER' AND date > $today 
AND date<'$lastmsg'
ORDER by date DESC LIMIT 10");

works fine, and also filters the same column twice?

So what is the problem my first query?

Answer 1

If the type of date is DATE, DATETIME or TIMESTAMP (it should be, if it isn't), then you must put your variable in between simple quotes : date BETWEEN '$Yday' AND '$today'

Make sure your variables contain the values you think they contain. Also, you can test if your query returns an error : mysql_query('...') or exit(mysql_error());

Answer 2

The two queries are using different criteria to filter on the date column.

Both require date to be less than $lastmsg, but the first query (which you said doesn't work) requires date to also be greater than or equal to $Yday or less than or equal to $today. The second query requires that date be greater than $today. The filtration is opposite of each other.

There's no issue with multiple filter criteria on a column -- you just have to have the data to satisfy the requirements in order to get results.