every 3 product for one category by one mysql query

[deimon]

Hello, i have a some eshop, and i need create some page, where customers shows for example 3 products per one category.
But Categories are many :(

My idea is one query, that create table, where are

category - name
1 - product
1 - product #2
1 - product #3
2 - product
2 - product #2
2 - product #3
3 - product
3 - product #2
3 - product #3
.....
and i by php added after 3 lines text about for what category is it. In all categories are minimal 3 products, so isnt problem in bad creating text lines. Or isnt problem added one column with name or id category. But question is

Is possible by one mysql query accept 3 products per category ?

Or i must send standart query for every category ?

Thanks.

[TheMadScientist]

Is possible by one mysql query accept 3 products per category ?

Or i must send standart query for every category ?

Yes and sort of...

The easiest way I can think of is to write your standard selects, then make them a UNION so they are all selected at once...

$select="
(SELECT cols FROM table WHERE category=1 ORDER BY whatever LIMIT 3)
UNION
(SELECT cols FROM table WHERE category=2 ORDER BY whatever LIMIT 3)
UNION
(SELECT cols FROM table WHERE category=3 ORDER BY whatever LIMIT 3)
ORDER BY category
";

[rocknbil]

I'm not really sure what you're asking. Would a set query work?

select * from products where category in (1,2,3);