0
$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?

Johnson
  • 818
  • 4
  • 21
  • 39
  • Can you show some table content? In your first query you have `date` between `$Yday` and `$today` but in your second query you have `date > $today`. Is this contradictory? By the way: You should tag the question with "mysql". It has nothing to do with php. ;) – Flinsch Oct 30 '10 at 18:23
  • the second query is to output messages from today. the first is for yesterday, which does not work. – Johnson Oct 30 '10 at 18:26

2 Answers2

0

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());

Vincent Savard
  • 34,979
  • 10
  • 68
  • 73
  • If it doesn't return any error, disregard my post (well, not for the column type :-p), the problem is what your variable contains. Either it cannot be true or your variables are off. Also, you should use DATE/DATETIME/TIMESTAMP for numerous reasons : human readable dates, being able to use date functions, easier to use, etc. You can read this post for more information : http://stackoverflow.com/questions/2948494/unix-timestamp-vs-datetime – Vincent Savard Oct 30 '10 at 18:39
0

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • the second query is to output messages from today. the first is for the yesterday messages, which does not work, so there isnt anything wrong with the first one you say? – Johnson Oct 30 '10 at 18:34
  • @Johnson: There's no syntactical error -- it's just a matter of checking if you have data to satisfy what you are looking for. There is the chance of $lastmsg being a date *after* $Yday which could be excluding records you thought you should see. – OMG Ponies Oct 30 '10 at 18:39
  • Hello. thank you, just found out it was because the other record i thought i would see had the same date as $lastmsg and then it didnt show, Is there a way to fix this? – Johnson Oct 30 '10 at 19:01
  • @Johnson: Do you need the comparison to $lastmsg for the first query? Or could you replace $Yday with $lastmsg? Ideally, you shouldn't have the multiple filtration on the same column if you don't need it. – OMG Ponies Oct 30 '10 at 19:02
  • @Johnson: You'll have to provide detail for me to know how you want the dates handled. Maybe you need an OR, rather than AND, to separate the `date` comparison? – OMG Ponies Oct 30 '10 at 19:39
  • @Omg ponies. Ok i have this query for when you press "more", after the first query that has LIMIT 10. then it takes the last date (the 10nd) ( $lastmsg ) from the first query, and then on this one i want it to start from where the first query ended, that's why im using <''.. AND before i forget to mention, the date should be from yesterday time.. – Johnson Oct 31 '10 at 08:30