Using PHP, is it possible to select multiple rows from one table, in a MySQL database, with different WHERE clauses having a specific LIMIT for each WHERE clause.
For example:
SELECT * FROM the_table WHERE color = 'blue' LIMIT 5 OR color = 'red' LIMIT 10
I know the above statement does not work. But is there a way to do this with a single call to the database?
You can use a UNION, if I understand your post correctly:
(SELECT * FROM the_table WHERE color = 'blue' LIMIT 5)
UNION ALL
(SELECT * FROM the_table WHERE color = 'red' LIMIT 10)
SELECT * FROM the_table WHERE color = 'blue' LIMIT 5
UNION
SELECT * FROM the_table WHERE color = 'red' LIMIT 10 ;
Have a look at the official MySQL documentation regarding subqueries (there's a reference made to LIMITs here, and some cases they may not be valid in): http://dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html -- you may find an optimisation using subqueries in there, but UNION is probably quite fast and will probably be auto-optimised.
