I've got a MYSQL5 database and PHP 5. I want a question for any games websites index page that only chooses the very first 12 from each group of games. Here's what I've to date.

$db->query("SELECT * FROM  `games` WHERE status = 'game_published'  AND `featured` =  '1' ORDER BY `category`");

The php code then groups games of the identical category together and shows them. But yeah it does not limit the amount of games from each category like I would like.

Here is what the dwelling on the table appears like: i49.tinypic.com/aysoll.png

This is a blog publish which seems like things i am attempting to do: http://www.e-nformation.net/content/view/title/MySQL+Top+N+in+each+group+(group+inner+limit) However I can't understand it.

Any assistance is appreciated.

To make use of the strategy in the posts you mention, you'll need a method to order the games. They are using article date. They choose the amount of older articles for your company, and say there can not be a lot more than three.

In case your games table comes with an auto-increment column known as id, you are able to choose the very best 10 games per category like:

FROM     games g1
WHERE    status = 'game_published'  
         AND featured = '1' 
         AND 10 > 
         SELECT   COUNT(*)
         FROM     games g2
         WHERE    g2.status = 'game_published'  
                  AND g2.featured = '1' 
                  AND g1.category = g2.category
                  AND g2.id > g1.id

The where condition states there can not be a lot more than 10 rows with similar category along with a greater ID.

There might be a far more elegant solution, but you can easily perform query for every category. First get a listing of groups:

SELECT DISTINCT(category) FROM  `games`;

Then take each one of the results and query for 12 rows:

SELECT * FROM games WHERE status = 'game_published'  
AND `featured` =  '1' AND `category` = $category LIMIT 12;

Obviously you have to then add type of ranking row (and order because of it) to obtain the top 12.

Note: There might be a method to do that having a single query, however it escapes me right now.

What about this?

       @rn := CASE WHEN @category=category THEN @rn + 1 ELSE 1 END AS rn,
       @category := category
    FROM games, (SELECT @rn := 0, @category := NULL) AS vars
    WHERE status = 'game_published' AND featured = '1'
    ORDER BY category
) AS T1
WHERE rn <= 12

you could utilize UNION, as not speaking about million of types...


(SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10)
(SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10)
(SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10)

For those who have variety of groups inside your PHP/ASP, you will get this union quickly.

More: http://dev.mysql.com/doc/refman/5.0/en/union.html

EDIT: Here's most likely most helpful resource: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Utilize it well ^^