Clever Geek Handbook

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 :)

enter image description here

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.

+3


source to share


3 answers


It looks like you forgot the GROUP BY clause in SQL that causes duplicate records to be written.



+1


source


Are you going by the appropriate movie ID? This might be the reason why you get all genres grouped into one.



0


source


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
0


source






More articles:


All Articles