...

View Full Version : Get movie info and genres with 1 query



destas
02-16-2013, 01:37 PM
How can i get movie info from 'movies' table and genres of this movie from 'genres' table?



$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
02-17-2013, 01:30 AM
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
02-17-2013, 11:32 AM
Doesn't show movies for me...


$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


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


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


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
02-17-2013, 09:19 PM
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
02-18-2013, 04:26 AM
Did i did something wrong?

Mysql version: 5.5.20



<?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
02-18-2013, 04:34 PM
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
02-19-2013, 10:23 AM
I get same error...


$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
02-19-2013, 08:03 PM
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
02-20-2013, 05:01 PM
Stupid mistake :thumbsup:

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




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
02-20-2013, 07:11 PM
??? 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
02-25-2013, 01:47 PM
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.



$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:


$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
02-25-2013, 05:07 PM
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
02-25-2013, 09:33 PM
I add:


GROUP BY m.title


and now show movies. But how to fix genres? homepage with same code show genres nice...



$TMPL['moviegenres'] = str_replace(',', ', ', $TMPL['genres']);


Why in genre page show one genre tons times?

Old Pedant
02-25-2013, 10:20 PM
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
02-26-2013, 09:23 AM
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




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


Same problem... :(

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/en/group-by-functions.html#function_group-concat

So...


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:


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.


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


WHERE `genre` LIKE '%s'


Now i change it to


WHERE g.genre LIKE '%s'


And same problem. It seems problem is in php?



$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 :D
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? :D

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


$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 :D

First query to get current genre.


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


Second


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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum