SQL LIMIT returns no results where no LIMIT returns results

SELECT * FROM mm_tfs 
WHERE product_slug LIKE '%football%' 
    AND schoolid = '8' AND category_id ='21'
LIMIT 4

Returns 4 values like I'm asking, but the following statement returns 0 - is there a rule about using the OR statement that I'm not familiar with? My assumption is that it should return all of the values in 1 (or more if it weren'开发者_开发百科t for the limit).

SELECT * FROM mm_tfs 
WHERE (product_slug LIKE '%football%' 
   AND schoolid = '8' AND category_id ='21') 
OR (product_description LIKE '%football%' 
   AND schoolid = '8' AND category_id ='21') 
LIMIT 4

_{NOTE by cyberkiwi The first OR portion of Q2 is exactly the same as the WHERE clause on Q1}

product_description LIKE '%football%' AND schoolid = '8' AND category_id ='21

Without the OR Statement by itself does produce the desired result as long as it does not have the limit. When the OR statement is used with the LIMIT as well, there are 0 values returned.

SELECT * FROM mm_tfs 
WHERE product_description LIKE '%football%' 
    AND schoolid = '8' AND category_id ='21'
LIMIT 4

^-- That produces 0 results

SELECT * FROM mm_tfs 
WHERE product_description LIKE '%football%' 
    AND schoolid = '8' AND category_id ='21'

^-- That produces results

The strangest part of this is that all of these queries product the right effect in my PHPMYADMIN SQL query window, but not in the application itself.

You are repeating some of the conditions which is not necessary. Try this instead:

SELECT * FROM mm_tfs 
WHERE 
   (product_slug LIKE '%football%' 
 OR product_description LIKE '%football%')
   AND schoolid = '8' 
   AND category_id ='21'
LIMIT 4

UPDATE:

I have created the following table:

create table mm_tfs2 (schoolid varchar(2), categoryid varchar(2), 
                      description varchar(20), slug varchar(20));

And 5 times:

insert into mm_tfs2 values (8, 21, '', 'football');

And finally the query:

select * from mm_tfs2 where 
(slug like '%football%' and schoolid = 8 and categoryid = 21) 
or (description like '%football%' and schoolid = 8 and categoryid = 21) 
limit 4;
+----------+------------+-------------+----------+
| schoolid | categoryid | description | slug     |
+----------+------------+-------------+----------+
| 8        | 21         |             | football |
| 8        | 21         |             | football |
| 8        | 21         |             | football |
| 8        | 21         |             | football |
+----------+------------+-------------+----------+
4 rows in set (0.00 sec)

So I'm sorry to say that I'm not able to recreate the problem.

Try to put ( ) arround the entire condition

( (...AND...) OR (...AND...) )

Like so:

  SELECT * FROM mm_tfs WHERE 
  (  (product_slug LIKE '%football%' AND schoolid = '8' AND category_id ='21') 
   OR (product_description LIKE '%football%' AND schoolid = '8' AND category_id   
   ='21')
  ) LIMIT 4

I think you could clean it up a little bit, you're getting only rows for one school, and one category ID, so there's no reason that you should have to check for those both times:

SELECT *
FROM mm_tfs
WHERE schoolid = '8'
    AND category_id ='21'
    AND (product_slug LIKE '%football%'
         OR product_description LIKE '%football%')
LIMIT 4;