Implementing Limit Per Category

-- data category

DROP TABLE IF EXISTS my_category;
CREATE TABLE IF NOT EXISTS my_category(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(25)
) ENGINE=MYISAM;

INSERT INTO my_category(name)
VALUES
('makanan'),
('minuman'),
('pakaian');

SELECT * FROM my_category;

+----+---------+
| id | name    |
+----+---------+
|  1 | makanan |
|  2 | minuman |
|  3 | pakaian |
+----+---------+
3 rows in set (0.00 sec)

-- data item

DROP TABLE IF EXISTS my_item;
CREATE TABLE IF NOT EXISTS my_item(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(25),
  category INT
) ENGINE=MYISAM;

INSERT INTO my_item(name,category)
VALUES
('nasi goreng',1),
('bubur ayam',1),
('sate ayam',1),
('pecel lele',1),
('empek-empek',1),
('martabak manis',1),
('mie ayam',1),
('es teh manis',2),
('coffee latte',2),
('soda gembira',2),
('kopi susu',2),
('orange juice',2),
('lemonade',2),
('t-shirt',3),
('kemeja',3),
('tuxedo',3),
('blus',3),
('batik',3);

SELECT * FROM my_item;

+----+----------------+----------+
| id | name           | category |
+----+----------------+----------+
|  1 | nasi goreng    |        1 |
|  2 | bubur ayam     |        1 |
|  3 | sate ayam      |        1 |
|  4 | pecel lele     |        1 |
|  5 | empek-empek    |        1 |
|  6 | martabak manis |        1 |
|  7 | mie ayam       |        1 |
|  8 | es teh manis   |        2 |
|  9 | coffee latte   |        2 |
| 10 | soda gembira   |        2 |
| 11 | kopi susu      |        2 |
| 12 | orange juice   |        2 |
| 13 | lemonade       |        2 |
| 14 | t-shirt        |        3 |
| 15 | kemeja         |        3 |
| 16 | tuxedo         |        3 |
| 17 | blus           |        3 |
| 18 | batik          |        3 |
+----+----------------+----------+
18 rows in set (0.00 sec)

-- Limiting Item per Category
SELECT *
FROM
(
  SELECT
    my_item.id,
    my_item.name,
    @c:=IF(@cat=my_item.category,@c+1,1) AS c,
    @cat:=my_item.category AS cat,
    my_category.name AS category
  FROM
    my_item
    JOIN my_category ON my_item.category=my_category.id,
    (
      SELECT
        @c:=0,
        @cat:=NULL
    ) AS a
  ORDER BY
    my_item.category
) AS b
WHERE c<=4
ORDER BY cat,c;

+----+--------------+----------+------+------+
| id | name         | category | c    | cat  |
+----+--------------+----------+------+------+
|  1 | nasi goreng  | makanan  |    1 |    1 |
|  2 | bubur ayam   | makanan  |    2 |    1 |
|  3 | sate ayam    | makanan  |    3 |    1 |
|  4 | pecel lele   | makanan  |    4 |    1 |
|  8 | es teh manis | minuman  |    1 |    2 |
|  9 | coffee latte | minuman  |    2 |    2 |
| 10 | soda gembira | minuman  |    3 |    2 |
| 11 | kopi susu    | minuman  |    4 |    2 |
| 14 | t-shirt      | pakaian  |    1 |    3 |
| 15 | kemeja       | pakaian  |    2 |    3 |
| 16 | tuxedo       | pakaian  |    3 |    3 |
| 17 | blus         | pakaian  |    4 |    3 |
+----+--------------+----------+------+------+
12 rows in set (0.01 sec)

-- Limiting Random Item per Category
SELECT *
FROM
(
  SELECT
    item.id,
    item.name,
    @c:=IF(@cat=item.category,@c+1,1) AS c,
    @cat:=item.category AS cat,
    item.category
  FROM
    (
      SELECT
        i.id,
        i.name,
        my_category.name AS category
      FROM
        (SELECT * FROM my_item ORDER BY RAND()) AS i
        JOIN my_category ON i.category=my_category.id
      ORDER BY
        i.category
    ) AS item,
    (
      SELECT
        @c:=0,
        @cat:=NULL
    ) AS a
  ORDER BY
    item.category
 ) AS b
WHERE c<=4
ORDER BY cat,c;

+----+----------------+------+---------+----------+
| id | name           | c    | cat     | category |
+----+----------------+------+---------+----------+
|  2 | bubur ayam     |    1 | makanan | makanan  |
|  6 | martabak manis |    2 | makanan | makanan  |
|  7 | mie ayam       |    3 | makanan | makanan  |
|  1 | nasi goreng    |    4 | makanan | makanan  |
|  9 | coffee latte   |    1 | minuman | minuman  |
| 11 | kopi susu      |    2 | minuman | minuman  |
| 12 | orange juice   |    3 | minuman | minuman  |
|  8 | es teh manis   |    4 | minuman | minuman  |
| 18 | batik          |    1 | pakaian | pakaian  |
| 14 | t-shirt        |    2 | pakaian | pakaian  |
| 15 | kemeja         |    3 | pakaian | pakaian  |
| 17 | blus           |    4 | pakaian | pakaian  |
+----+----------------+------+---------+----------+
12 rows in set (0.00 sec)