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 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Get movie info and genres with 1 query

    How can i get movie info from 'movies' table and genres of this movie from 'genres' table?

    PHP Code:
    $querynew         sprintf("SELECT * FROM `movies` ORDER BY `id` DESC LIMIT 0,4"); 
        
    $resultnew         mysql_query($querynew); 
        
    $TMPL_old         $TMPL$TMPL = array(); 
        
    $template         = new template('movielist/toplist'); 
        
    $new             ''
        while (
    $row mysql_fetch_assoc($resultnew)) { 
            
    $TMPL['genres'] = mysql_query("SELECT m.title as movie_title, 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 = mc.genre_id) 
    GROUP BY m.id"
    );
    ... 

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    I don't get it. Why do you need that first query, at all???

    What's wrong with simply
    Code:
    SELECT m.title, m.whatever, m.otherstuff, 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 = mc.genre_id)  
    GROUP BY m.id
    ORDER BY m.id
    LIMIT 4
    ???
    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.

  • Users who have thanked Old Pedant for this post:

    destas (02-20-2013)

  • #3
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Doesn't show movies for me...
    PHP Code:
        $querynew         sprintf("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 = mc.genre_id)  
    GROUP BY m.id
    ORDER BY m.id
    DESC LIMIT 0,4"
    );
        
    $resultnew         mysql_query($querynew);
        
    $TMPL_old         $TMPL$TMPL = array();
        
    $template         = new template('movielist/newmovies');
        
    $new             '';
        while (
    $row mysql_fetch_assoc($resultnew)) {
        
            
    $new .= $template->make();
        } 
    Maybe something wrong with my tables?

    Movies
    PHP Code:
    CREATE TABLE IF NOT EXISTS `movies` (
      `
    idINT(11UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `
    titleVARCHAR(128NOT NULL,
      `
    yearYEAR NOT NULL,
      `
    premiereDATE NOT NULL default '0000-00-00',
      `
    imdbVARCHAR(10NOT NULL,
      `
    addedTIMESTAMP NOT NULL default CURRENT_TIMESTAMP
    ENGINE=MyISAM DEFAULT CHARSET=utf8
    Genres
    PHP Code:
    CREATE TABLE IF NOT EXISTS `genres` (
      `
    idINT(11UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `
    genreVARCHAR(256NOT NULL
    ENGINE=MyISAM DEFAULT CHARSET=utf8;
    INSERT INTO `genres` (`id`, `genre`) VALUES
    (1'comedy'),
    (
    2'fantasy'),
    (
    3'horror'); 
    Genres relationship
    PHP Code:
    CREATE TABLE IF NOT EXISTS `moviegenres` (
      `
    idINT(11UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `
    movie_idINT(10UNSIGNED NOT NULL,
      `
    genre_idINT(10UNSIGNED NOT NULL,
       
    FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`) ON DELETE CASCADE,
       
    FOREIGN KEY (`genre_id`) REFERENCES `genres`(`id`) ON DELETE RESTRICT
    ENGINE=MyISAM DEFAULT CHARSET=utf8;
    INSERT INTO `moviegenres` (`id`, `movie_id`, `genre_id`) VALUES
    (111),
    (
    212),
    (
    313); 

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    One *MAJOR* mistake, one typo.

    When you use GROUP BY, you *MUST* specify *ALL* the columns from the SELECT that are NOT aggregate function results. MySQL allows you to omit columns, unlike any other DB (a huge mistake in my opinion), but then the results are *NOT* what you expect!

    And look closely at INNER JOIN genres g ON (g.id = mc.genre_id)
    Where did "mc" come from ???

    So:
    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)   
    GROUP BY m.id, m.title, m.year 
    ORDER BY m.id 
    DESC LIMIT 0,4
    *PLEASE* learn to *TEST* your queries in a MySQL tool *BEFORE* using them in your PHP code! PHP gives horrible error diagnostics compared to a good tool.

    *********

    Other comments: There is really no point in using a FOREIGN KEY declaration if you will create MYISAM tables. MyISAM does *NOT* enforce referential integrity. If you want referential integrity, and especially if you want to use features such as ON DELETE CASCADE, you *must* use INNODB.

    The ID column in your moviegenres table is pointless and useless. It adds nothing at all to the database capabilities.

    A better declaration for that table would be:
    Code:
    CREATE TABLE moviegenres ( 
      movie_id INT UNSIGNED NOT NULL, 
      genre_id INT UNSIGNED NOT NULL, 
      PRIMARY KEY (movie_id, genre_id)
    );
    (And go ahead and add in the foreign keys, but only if you change the engine to INNODB.)
    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.

  • Users who have thanked Old Pedant for this post:

    destas (02-20-2013)

  • #5
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Did i did something wrong?

    Mysql version: 5.5.20

    PHP Code:
    <?php
    $con 
    mysql_connect("localhost","root","");
    mysql_select_db("imdb",$con);

    $result mysql_query("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)   
    GROUP BY m.id, m.title, m.year 
    ORDER BY m.id 
    DESC LIMIT 0,4"
    );

    if (!
    $result) {
        die(
    mysql_error());
    }
    else
    {
        while (
    $row mysql_fetch_assoc($result))
        {
        
    $results[] = $row;
            
    $id $rows -> id;
            
    $title $rows -> title;
            
            echo 
    $id;
            echo 
    $tile;
        }

    }

    mysql_free_result($result);

    print_r(mysql_fetch_assoc($result));

    mysql_close($con);
    ?>
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT(g.genre) AS genres FROM movies m INNER JOIN moviegenres mg O' at line 1

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    Maybe because your "AS genres" conflicts with the table name "genres"??

    You could try putting back ticks around the AS name: AS `genres`

    I actually tried that query in my copy of MySQL 5.5 and it worked fine.
    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.

  • Users who have thanked Old Pedant for this post:

    destas (02-20-2013)

  • #7
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    I get same error...
    PHP Code:
    $result mysql_query("SELECT m.id, m.title, m.year GROUP_CONCAT(g.genre) AS `genryes` 
    FROM movies m   
    INNER JOIN moviegenres mg ON (mg.movie_id = m.id)   
    INNER JOIN `genryes` g ON (g.id = mg.genre_id)   
    GROUP BY m.id, m.title, m.year 
    ORDER BY m.id 
    DESC LIMIT 0,4"
    ); 

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    You are MISSING A COMMA between m.year and GROUP_CONCAT(...) !!!

    Here is my test:
    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 moviegenres;
    +----------+----------+
    | movie_id | genre_id |
    +----------+----------+
    |        1 |        1 |
    |        1 |        2 |
    |        2 |        3 |
    |        3 |        3 |
    +----------+----------+
    4 rows in set (0.01 sec)
    
    mysql> 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)
        -> GROUP BY m.id, m.title, m.year
        -> ORDER BY m.id
        -> DESC LIMIT 0,4;
    +----+--------------------+------+----------------+
    | id | title              | year | genres         |
    +----+--------------------+------+----------------+
    |  3 | zombies            | 2012 | horror         |
    |  2 | 2001 space odyssey | 1967 | horror         |
    |  1 | kink kong          | 1939 | fantasy,comedy |
    +----+--------------------+------+----------------+
    3 rows in set (0.00 sec)
    And notice how the GROUP_CONCAT works for "kink kong". (It was a low budget spoof using a chimpanzee with strange sexual tastes.)
    Last edited by Old Pedant; 02-19-2013 at 08:07 PM.
    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.

  • Users who have thanked Old Pedant for this post:

    destas (02-20-2013)

  • #9
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Stupid mistake

    One more question. How to get genres + actors where genre name like... ?


    PHP Code:
    SELECT m.idm.titlem.yearGROUP_CONCAT(g.genre) AS genresGROUP_CONCAT(a.actor) 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 g ON (a.id ma.actor_id
    WHERE `genreLIKE '%%%s%%' 
    GROUP BY m.title
    LIMIT 
    %d, %d;"; 
    Last edited by destas; 02-20-2013 at 06:03 PM.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    ??? That didn't work?

    Maybe you need the table qualifier in there?

    And why the excess of % characters?

    WHERE g.genre LIKE '%s%'

    But of course that will find *ANY* genre that has the letter "s" (or "S" -- SQL is not case sensitive) in it.
    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.

  • #11
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    I have another problem with genres page. Maybe you know php as well like mysql and can help me.

    I need to get how many genre comedy have movies to make pages.

    PHP Code:
        $genre_name urldecode(htmlEntities($_GET['q']));
        
    $page_query mysql_query('SELECT COUNT(id), `genre` FROM `genres` WHERE `genre` LIKE "'.$genre_name.'"');
        
        
    $pages = @ceil(mysql_result($page_query0) / $MovieListPerPage);
        
    $page = (isSet($_GET['page']) AND (int)$_GET['page'] > 0) ? (int)$_GET['page'] : 1;
        
    $start = ($page 1) * $MovieListPerPage
    And another question:
    PHP Code:
        $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(g.genre) AS genres, GROUP_CONCAT(a.actor) 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 `genre` LIKE '%s' 
                            ORDER BY m.title
                            LIMIT %d, %d;"
    ;
                } else {
                    
    $query "SELECT m.id, m.title, m.year, GROUP_CONCAT(g.genre) AS genres, GROUP_CONCAT(a.actor) 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 `genre` LIKE '%s' AND char_length(genre) >= 3
                            ORDER BY m.title
                            LIMIT %d, %d;"
    ;
                }
                
    $query         sprintf($query$genre_name$start$MovieListPerPage);

                
    $result     mysql_query($query);
                
    $TMPL_old     $TMPL$TMPL = array();
                
    $template     = new template('movielist/movielist');
                
    $all         '';
                while ( 
    $TMPL mysql_fetch_assoc($result) )
                {
                    
    $TMPL['moviegenres'] = str_replace(','', '$TMPL['genres']);
                    
                    
    $all .= $template->make();
                } 
    Why this code show me only one movie and only one genre?
    example:
    Genres: comedy, comedy, comedy, comedy, comedy, comedy, comedy

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    Because you forgot the GROUP BY.

    Look at my answer in post #4. You *MUST* group by ALL fields SELECTed other than the aggregate functions.

    PERIOD.

    MUST.

    NO CHOICE.
    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.

  • #13
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    I add:
    PHP Code:
    GROUP BY m.title 
    and now show movies. But how to fix genres? homepage with same code show genres nice...

    PHP Code:
    $TMPL['moviegenres'] = str_replace(','', '$TMPL['genres']); 
    Why in genre page show one genre tons times?

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    No. Go read post #4 *AGAIN AND AGAIN AND AGAIN*
    When you use GROUP BY, you *MUST* specify *ALL* the columns from the SELECT that are NOT aggregate function results. MySQL allows you to omit columns, unlike any other DB (a huge mistake in my opinion), but then the results are *NOT* what you expect!
    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.

  • #15
    New Coder
    Join Date
    Feb 2013
    Posts
    32
    Thanks
    10
    Thanked 0 Times in 0 Posts
    PHP Code:
    SELECT m.idm.titlem.yearm.aam.bbm.ccm.ddGROUP_CONCAT(g.genre) AS genresGROUP_CONCAT(a.actor) AS actors 
    PHP Code:
    GROUP BY m.idm.titlem.yearm.aam.bbm.ccm.dd 
    Same problem...


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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