CODEWITHSUNDEEP
phpmysql
Kenny
Kenny

Reputation: 5410

Group concat double results

I have been working on an application that runs the following query:

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 the query in my query browser or straight via SSH it returns the following results (example:)

enter image description here

In my PHP code I do the following to loop over the 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 } ?>

While the expected out is expected to be:

Drama, Adventure

The output is:

Drama, Drama, Drama, Adventure, Adventure, Adventure

Any idea why this is happening as I can't figure it out?

PS: I know a work-around would be to just add array_unique() when I explode the comma separated list of the genres but I want to know the root of the problem.

Basically when I run the query manually the genres show up fine, when it runs in the applications, it shows double while the rest of the info still shows just fine.

Upvotes: 3

Views: 129

Answers (3)

Kenny
Kenny

Reputation: 5410

Solved it, problem was with the query, I forgot to 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

Upvotes: 0

Minesh
Minesh

Reputation: 2302

It seems like you have forgotten GROUP BY clause in SQL, due to which duplicate records are written.

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

Are you grouping by the proper movie id? It could be the reason that you are getting all genres grouped into one..

Upvotes: 0

Related Questions