One *MAJOR* mistake, one typo.
When you use GROUP BY, you *MUST* specify *ALL* the columns from the SELECT that are NOT aggregate function results. MySQL allows you to omit columns, unlike any other DB (a huge mistake in my opinion), but then the results are *NOT* what you expect!
And look closely at
INNER JOIN genres g ON (g.id = mc.genre_id)
Where did "mc" come from ???
So:
Code:
SELECT m.id, m.title, m.year GROUP_CONCAT(g.genre) AS genres
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.id = mg.genre_id)
GROUP BY m.id, m.title, m.year
ORDER BY m.id
DESC LIMIT 0,4
*PLEASE* learn to *TEST* your queries in a MySQL tool *BEFORE* using them in your PHP code! PHP gives horrible error diagnostics compared to a good tool.
*********
Other comments: There is really no point in using a FOREIGN KEY declaration if you will create MYISAM tables. MyISAM does *NOT* enforce referential integrity. If you want referential integrity, and especially if you want to use features such as ON DELETE CASCADE, you *must* use INNODB.
The ID column in your moviegenres table is pointless and useless. It adds nothing at all to the database capabilities.
A better declaration for that table would be:
Code:
CREATE TABLE moviegenres (
movie_id INT UNSIGNED NOT NULL,
genre_id INT UNSIGNED NOT NULL,
PRIMARY KEY (movie_id, genre_id)
);
(And go ahead and add in the foreign keys, but only if you change the engine to INNODB.)