I have two tables:
Items
ID Name Model_ID
---------------------------------
1 2010 Audi L1 1
2 2014 BMW X2 2
3 2015 Acura L3 3
4 2016 BMW X5 2
5 2012 BMW X3 2
6 2013 BMW X4 2
7 2015 Acura L1 3
8 2011 Acura L2 3
9 2011 Audi L5 1
10 2012 Audi L6 1
Brands
Model_ID Title
---------------------
1 Audi
2 BMW
3 Acura
And following query:
SELECT
b.name,
i.title,
FROM
items AS i
INNER JOIN brands AS b
ON b.Model_ID = i.Model_ID
WHERE i.status = 1
ORDER BY i.created DESC;
The above produces working array:
Array
(
[0] => stdClass Object
(
[name] => 2010 Audi L1
[title] => Audi
)
[1] => stdClass Object
(
[name] => 2014 MBW X5
[title] => BMW
)
...
)
Than I use custom function to loop through array and end up with
Array
(
[Acura] => Array
(
[0] => stdClass Object
(
[name] => 2015 Acura L1
[title] => Acura
)
...
)
[BWM] => Array
(
[0] => stdClass Object
(
[name] => 2016 BMW X5
[title] => BWM
)
...
)
[Audi] => Array
(
[0] => stdClass Object
(
[name] => 2010 Audi L1
[title] => Audi
)
...
)
)
Now I can use foreach loop and limit each Brand to show x number of items, but the idea is to do it within database, so instead of pulling all records, I would like to be able to limit to 5 items per each brand.
Note: I did not list the rest of the table fields, such as created, which is used to sort records.