Php, MySql join tables, limit records per category

Question

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.

Am I wrong or is that query all mixed up so it does not actually match the table columns? — RiggsFolly, Aug 25 '15 at 23:41
@ RiggsFolly Query is correct, as per my note I did not include the rest of the fields, only the ones needed. — Alko, Aug 25 '15 at 23:45
`i` does not have a `title` `b` does have a `title`. `b` does not have a `name`, `i` does have a `name` ?? — RiggsFolly, Aug 25 '15 at 23:52
possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — Sean, Aug 25 '15 at 23:52

score 0 · Answer 1 · answered Aug 26 '15 at 00:48

get brands:

$q = 'SELECT Model_ID AS id FROM brands ORDER BY title ASC';
$r = mysql_query($q);
$model_ids = array();
while($brand = mysql_fetch_assoc($r)) { $model_ids[] = $brand['id']; }

then make generate select query like this using Model_ID-s collected from 1st step or somewhere else (ex: from search form):

$q = array(); 
foreach($mode_ids AS $model_id) {
    $q[] = '(SELECT b.name, i.title FROM items AS i INNER JOIN brands AS b ON b.Model_ID = i.Model_ID WHERE b.Model_ID = '.(int)$model_id.' AND i.status = 1 ORDER BY i.created DESC LIMIT 5)';
}

if(!empty($q)) {
    $q = implode(' UNION ALL ', $q);
    $r = mysql_query($q);
    while($record = mysql_fetch_object($r)) {
        var_dump($record);
    }
}

as result we'll get records of resulting query:

(SELECT b.name, i.title FROM items AS i INNER JOIN brands AS b ON b.Model_ID = i.Model_ID WHERE b.Model_ID=1 AND i.status = 1 ORDER BY i.created DESC LIMIT 5)

UNION ALL

(SELECT b.name, i.title FROM items AS i INNER JOIN brands AS b ON b.Model_ID = i.Model_ID WHERE b.Model_ID=2 AND i.status = 1 ORDER BY i.created DESC LIMIT 5)

UNION ALL

(SELECT b.name, i.title FROM items AS i INNER JOIN brands AS b ON b.Model_ID = i.Model_ID WHERE b.Model_ID=3 AND i.status = 1 ORDER BY i.created DESC LIMIT 5)

Php, MySql join tables, limit records per category

1 Answers1