MySql skips first record found

in my database i have 10 records with almost exact same data , they differ only by one field ( the field is not in the query) and when i run the following query

SELECT * FROM friends WHERE user_id= 'MyUserName' AND follow_back = 0 AND until_date= '2009-10-13'  LIMIT 12 

it shows only 9 records , any one stumbled upon similar problem ? Thanks & waiting for your answers !

13.10.2009 16:47:59
Please post the results you are expecting to see and the results you are actually seeing.
James McNellis 13.10.2009 16:50:57
You might want to try this query with LIMIT 0, 12 instead of LIMIT 12 and tell us if this makes a difference.
user156676 13.10.2009 16:53:30
@Dreamon Ahh, thanks. Wasn't sure about that.
user156676 13.10.2009 16:56:40
wouldn't that show form 0 to 12 , my idea is to display 12 records only , no matter what number are they
Aviatrix 13.10.2009 16:56:58
@Aviatrix no, it's actually equal to LIMIT 12 as you can see in the MySQL Reference: dev.mysql.com/doc/refman/5.1/en/select.html
user156676 13.10.2009 17:03:33
4 ОТВЕТА
РЕШЕНИЕ

The short answer is there's nothing wrong with your query, so

user_id!='MyUserName'

or

follow_back != 0 

or

until_date != '2009-10-13'

Try just querying on one criterion at a time and see if you can norrow it down. Perhaps follow_back is NULL?

3
13.10.2009 16:59:37
ugh , yes , you are right , one of the records was NULL i didn't saw it thanks !
Aviatrix 13.10.2009 17:03:17

Are you sure, that all values in column user_id are the same? Maybe that one missing record has user_id = 'MyUserName ' (note the space).

0
13.10.2009 16:52:32
Yes im sure cuz they are auto added :)
Aviatrix 13.10.2009 16:54:48
I believe that MySQL automatically trims spaces from strings. SELECT 'test' = 'test ' returns 1.
Ian Clelland 13.10.2009 17:55:25
@Ian: And you're right: if the column is a CHAR or VARCHAR data type, trailing space doesn't matter. See also dev.mysql.com/doc/refman/5.0/en/char.html
Piskvor left the building 23.10.2009 18:38:13

When trying to debug problems like these, what I would usually do is to try solving it using a divide and conquer approach.

So try and remove one where condition at a time, then execute the query. That way you will be able to isolate the offending condition.

Good luck

1
13.10.2009 17:01:37

I had the same problem a minute ago. It turned out it wasn't the query that was the problem, but the IF where I check if anything's returned. Might want to check that.

0
3.11.2009 06:32:26