Group double results
I am working on an application that makes the following request:
SELECT m.*, GROUP_CONCAT(g.title) as genres
FROM movie m
INNER JOIN genre_movie gm ON gm.movie_id = m.id
INNER JOIN genre g ON gm.genre_id = g.id
WHERE m.imdb_id = '454876'
When I run a request in the request browser or directly via SSH, it returns the following results (example :)
In my PHP code, I do the following to iterate over genres:
<?php $get = mysql_query($query); // $query is the above query ?>
<?php $movieInfo = mysql_fetch_assoc($get); ?>
<?php $genres = explode(",", $movieInfo['genres']); ?>
<?php foreach ($genres as $genre) { ?>
<li class="clear"><a><span class="value"><?php echo $genre; ?></span></a></li>
<?php } ?>
The expected result is expected:
Drama, Adventure
Output:
Drama, Drama, Drama, Adventure, Adventure, Adventure
Any idea why this is happening as I can't figure it out?
PS: I know the workaround should only be to just add array_unique()
when I blow up the comma separated list of genres, but I want to know the root of the problem.
Basically, when I run the query manually, genres show up well, when run in apps, it shows twice, while the rest of the information still shows just fine.
It looks like you forgot the GROUP BY clause in SQL that causes duplicate records to be written.
Are you going by the appropriate movie ID? This might be the reason why you get all genres grouped into one.
Solved, the problem was with the request, I forgot the GROUP BY:
SELECT m.*, GROUP_CONCAT(g.title) as genres
FROM movie m
INNER JOIN genre_movie gm ON gm.movie_id = m.id
INNER JOIN genre g ON gm.genre_id = g.id
WHERE m.imdb_id = '454876' GROUP BY m.id