Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  2. #17
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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?

  3. #18
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Okay...I guess I'll have to duplicate your db to try it.

    Back later.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  4. #19
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  5. Users who have thanked Old Pedant for this post:

    destas (03-01-2013)

  6. #20
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    I use
    PHP Code:
    WHERE `genreLIKE '%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_pages0) / $MovieListPerPage);
        
    $page             = (isSet($_GET['page']) AND (int)$_GET['page'] > 0) ? (int)$_GET['page'] : 1;
        
    $start             = ($page 1) * $MovieListPerPage
    Last edited by destas; 03-01-2013 at 02:57 PM.
    Sorry for my poor english.

  7. #21
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    > 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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  8. #22
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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.idm.titlem.yearGROUP_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
    ,
    Last edited by destas; 03-02-2013 at 05:05 PM.
    Sorry for my poor english.


 
Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •