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:
SELECT * 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 )
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?
SELECT * FROM ( SELECT games.*, @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) UNION (SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10) UNION (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.
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 ^^