Get movie info and genres with 1 query - Page 2

Old Pedant · 02-26-2013, 07:45 PM

Oh, STUPID ME!

OF COURSE this will happen!

Because you are joining to *BOTH* genres and actors!

So for *EACH* actor, you get the SAME GENRE. And for each genre you get the SAME ACTOR!

You just need the keyword DISTINCT in there.

And, by the by, you can easily change the separator from comma to anything you want. Read here:
http://dev.mysql.com/doc/refman/5.5/...n_group-concat

So...

Code:

GROUP_CONCAT( DISTINCT g.genre SEPARATOR ', ' ) AS genres,
GROUP_CONCAT( DISTINCT a.actor SEPARATOR ', ' ) AS actors

destas · 02-27-2013, 09:17 AM

Now for each movie i get only one genre. If i click to show movies with Comedy all movies have Comedy genre now written by one-time where is another genres for this movie... I don`t understand why Actors show nice?

Old Pedant · 02-27-2013, 03:51 PM

Okay...I guess I'll have to duplicate your db to try it.

Back later.

Old Pedant · 03-01-2013, 12:38 AM

Well, you must be doing SOMETHING wrong. It worked perfectly for me.

Here is my sample data:

Code:

mysql> select * from movies;
+----+--------------------+------+------------+------+---------------------+
| id | title              | year | premiere   | imdb | added               |
+----+--------------------+------+------------+------+---------------------+
|  1 | kink kong          | 1939 | 0000-00-00 | xyz2 | 2013-02-17 16:00:52 |
|  2 | 2001 space odyssey | 1967 | 0000-00-00 | abc3 | 2013-02-17 16:01:51 |
|  3 | zombies            | 2012 | 0000-00-00 | jjj8 | 2013-02-17 16:01:51 |
+----+--------------------+------+------------+------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from genres;
+----+---------+
| id | genre   |
+----+---------+
|  1 | comedy  |
|  2 | fantasy |
|  3 | horror  |
+----+---------+
3 rows in set (0.00 sec)

mysql> select * from actors;
+----------+----------------+
| actor_id | actor          |
+----------+----------------+
|        1 | Alan Arkin     |
|        2 | Bela Lugosi    |
|        3 | Carmen Miranda |
|        4 | Don Knotts     |
+----------+----------------+
4 rows in set (0.00 sec)

mysql> select * from moviegenres;
+----------+----------+
| movie_id | genre_id |
+----------+----------+
|        1 |        1 |
|        1 |        2 |
|        2 |        3 |
|        3 |        3 |
+----------+----------+
4 rows in set (0.00 sec)

mysql> select * from movieactors;
+----------+----------+
| movie_id | actor_id |
+----------+----------+
|        1 |        1 |
|        1 |        4 |
|        2 |        2 |
|        3 |        2 |
|        3 |        3 |
|        3 |        4 |
+----------+----------+
6 rows in set (0.01 sec)

And then here are two sample queries, that get the results shown.

Code:

mysql> SELECT m.id, m.title, m.year,
    ->        GROUP_CONCAT(DISTINCT g.genre SEPARATOR ', ') AS genres,
    ->        GROUP_CONCAT(DISTINCT a.actor SEPARATOR ', ') AS actors
    -> FROM movies m
    -> INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
    -> INNER JOIN genres g ON (g.id = mg.genre_id)
    -> INNER JOIN movieactors ma ON (ma.movie_id = m.id)
    -> INNER JOIN actors a ON (a.actor_id = ma.actor_id)
    -> GROUP BY m.id, m.title, m.year
    -> ORDER BY m.title
    -> ;
+----+--------------------+------+-----------------+-----------------------------------------+
| id | title              | year | genres          | actors                                  |
+----+--------------------+------+-----------------+-----------------------------------------+
|  2 | 2001 space odyssey | 1967 | horror          | Bela Lugosi                             |
|  1 | kink kong          | 1939 | comedy, fantasy | Alan Arkin, Don Knotts                  |
|  3 | zombies            | 2012 | horror          | Don Knotts, Carmen Miranda, Bela Lugosi |
+----+--------------------+------+-----------------+-----------------------------------------+
3 rows in set (0.02 sec)

mysql> SELECT m.id, m.title, m.year,
    ->        GROUP_CONCAT(DISTINCT g.genre SEPARATOR ', ') AS genres,
    ->        GROUP_CONCAT(DISTINCT a.actor SEPARATOR ', ') AS actors
    -> FROM movies m
    -> INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
    -> INNER JOIN genres g ON (g.id = mg.genre_id)
    -> INNER JOIN movieactors ma ON (ma.movie_id = m.id)
    -> INNER JOIN actors a ON (a.actor_id = ma.actor_id)
    -> where g.genre = 'comedy'
    -> GROUP BY m.id, m.title, m.year
    -> ORDER BY m.title
    -> ;
+----+-----------+------+--------+------------------------+
| id | title     | year | genres | actors                 |
+----+-----------+------+--------+------------------------+
|  1 | kink kong | 1939 | comedy | Don Knotts, Alan Arkin |
+----+-----------+------+--------+------------------------+
1 row in set (0.00 sec)

The only difference from your stuff is that I used actor_id in my actors table instead of id. But that would make no difference.

destas · 03-01-2013, 09:48 AM

I use

PHP Code:


			
WHERE `genre` LIKE '%s'

Now i change it to

PHP Code:


			
WHERE g.genre LIKE '%s'

And same problem. It seems problem is in php?

PHP Code:


			
    $current_page     = $_GET['a'];
    
    $queryid = 'SELECT * FROM `genres` WHERE `genre` LIKE "'.$genre_name.'"';
    if (mysql_fetch_row(mysql_query($queryid)) >= 1)
    {
        if (!empty($genre_name))
        {
            if ( isSet($genre_name[1]) )
            {
                $query = "SELECT m.id, m.title, m.year, 
                        GROUP_CONCAT(DISTINCT g.genre SEPARATOR ', ') AS genres, 
                        GROUP_CONCAT(DISTINCT a.actor SEPARATOR ', ') AS actors 
                        FROM movies m 
                        INNER JOIN moviegenres mg ON (mg.movie_id = m.id) 
                        INNER JOIN genres g ON (g.id = mg.genre_id) 
                        INNER JOIN movieactors ma ON (ma.movie_id = m.id) 
                        INNER JOIN actors a ON (a.id = ma.actor_id) 
                        WHERE g.genre LIKE '%s' 
                        GROUP BY m.id, m.title, m.year, 
                        ORDER BY m.title 
                        LIMIT %d, %d";
            } else {
                $query = "SELECT m.id, m.title, m.year, 
                        GROUP_CONCAT(DISTINCT g.genre SEPARATOR ', ') AS genres, 
                        GROUP_CONCAT(DISTINCT a.actor SEPARATOR ', ') AS actors 
                        FROM movies m 
                        INNER JOIN moviegenres mg ON (mg.movie_id = m.id) 
                        INNER JOIN genres g ON (g.id = mg.genre_id) 
                        INNER JOIN movieactors ma ON (ma.movie_id = m.id) 
                        INNER JOIN actors a ON (a.id = ma.actor_id) 
                        WHERE g.genre LIKE '%s' AND char_length(g.genre) >= 3 
                        GROUP BY m.id, m.title, m.year, 
                        ORDER BY m.title 
                        LIMIT %d, %d";
            }
            $query         = sprintf($query, $genre_name, $start, $MovieListPerPage);

I have one more question

How can i make pagination of Genres? if Genre Comedy have 30~ movies $MovieLisPerPage (is 5) code must create 6 pages. How to calculate Comedy genres without genre ID?

It's good what you help me so much. Thank you for that

PHP Code:


			
    $query_pages     = mysql_query('SELECT m.id
                        FROM movies m 
                        INNER JOIN moviegenres mg ON (mg.movie_id = m.id) 
                        INNER JOIN genres g ON (g.id = mg.genre_id)
                        WHERE g.genre LIKE "'.$genre_name.'"');
    $pages             = @ceil(mysql_result($query_pages, 0) / $MovieListPerPage);
    $page             = (isSet($_GET['page']) AND (int)$_GET['page'] > 0) ? (int)$_GET['page'] : 1;
    $start             = ($page - 1) * $MovieListPerPage;

Old Pedant · 03-01-2013, 06:07 PM

> if Genre Comedy have 30~ movies $MovieLisPerPage (is 5) code must create 6 pages.

Ummm... That is what LIMIT is for.

You will first do LIMIT 0, 5
Then LIMIT 5, 5
Then LIMIT 10, 5
etc.

But I think you knew that.

I don't know what else you are asking for.

Remember: I don't use PHP, so if it's a PHP question and not a MySQL question, I am probably not the right person to ask.

destas · 03-02-2013, 10:44 AM

Can you help me with one more thing? How to get actor which has appeared in the most movies? count from movieactors and get his name from Actors ?

And if you can help me please with getting similar rows. Did i need 2 queries? i have not enough brain to do it

First query to get current genre.

PHP Code:


			
"SELECT id FROM genres WHERE `id` = '$movie_id'";

Second

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

                            WHERE g.genre = 'FIRST QUERY' AND != '{$movie_current_id}' 

                            GROUP BY m.id 

                            ORDER BY id 

                            DESC LIMIT 0,8