Get movie info and genres with 1 query

[destas]

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");
... 


[Old Pedant]

I don't get it. Why do you need that first query, at all???

What's wrong with simply

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

???

[destas]

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` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(128) NOT NULL,
  `year` YEAR NOT NULL,
  `premiere` DATE NOT NULL default '0000-00-00',
  `imdb` VARCHAR(10) NOT NULL,
  `added` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 


Genres

PHP Code:

CREATE TABLE IF NOT EXISTS `genres` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `genre` VARCHAR(256) NOT 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` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `movie_id` INT(10) UNSIGNED NOT NULL,
  `genre_id` INT(10) UNSIGNED 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
(1, 1, 1),
(2, 1, 2),
(3, 1, 3); 


[Old Pedant]

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:

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:

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

[destas]

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

[Old Pedant]

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.

[destas]

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"); 


[Old Pedant]

You are MISSING A COMMA between m.year and GROUP_CONCAT(...) !!!

Here is my test:

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

[destas]

Stupid mistake

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

PHP Code:

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 g ON (a.id = ma.actor_id) 
WHERE `genre` LIKE '%%%s%%' 
GROUP BY m.title
LIMIT %d, %d;"; 


[Old Pedant]

??? 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.

[destas]

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_query, 0) / $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

[Old Pedant]

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.

[destas]

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?

[Old Pedant]

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!

[destas]

PHP Code:

SELECT m.id, m.title, m.year, m.aa, m.bb, m.cc, m.dd, GROUP_CONCAT(g.genre) AS genres, GROUP_CONCAT(a.actor) AS actors 


PHP Code:

GROUP BY m.id, m.title, m.year, m.aa, m.bb, m.cc, m.dd 


Same problem...