PHP/MySQL Query, Count & Output

Question

I have a MySQL database with a games table. I also have publishers and developers tables. Each row in the games table has publisher and developer columns, which correspond with the other two tables.

• games.Publisher & publishers.PublisherID
• games.Developer & developers.DeveloperID

I'm trying to count the number of games each publisher and developer has, and display these with php into two tables on separate web pages. I know I need to create a loop to populate the number of games for each publisher/developer present in the table rows, but I can't figure out how.

Answer 1

You can do a count in SQL.

SELECT p.*, count(g.publisher) as number_of_games
FROM publishers p
LEFT JOIN games g ON (g.publisher = p.publisherID)
GROUP BY p.publisher WITH ROLLUP

This will give you the count per publisher with a total added to the bottom row.
If you just want the total, drop the last time from the query.

The query is the same for developers, just replace publisher with developer

Links:
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
http://www.tizag.com/mysqlTutorial/