similar rows

[rereze]

Can you help me please with getting similar rows. Did i need 2 queries?

 

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  

 

[Barand]

That first query is a waste of space. I think it's a fair bet that if you select the id where the id = 123 then the result will be 123.

[rereze]

I don' get it.

 

WHERE mg.genre_id = g.genre_id AND mg.movie_id != '{$movie_id}' 

 

Get current movie in (which i'm looking).

$movie_id = $_GET['q'];

 

i have  3 tables:

 

INSERT INTO `movies` (`id`, `title`, `year`) VALUES
(1, 'The Lorax', 2012),
(2, 'The test', 2013);

INSERT INTO `genres` (`genre_id`, `genre`) VALUES
(1, 'comedy'),
(2, 'fantasy');

INSERT INTO `moviegenres` (`movie_id`, `genre_id`) VALUES
(1, 1),
(2, 1),
(1, 2);

And i need to get if movie have same genre (and if movie have more than one same genre than this movie will be more important than  movie with one same genre) show similar movies. Hope you understand me

Edited March 4, 2013 by rereze

[Barand]

 

First query to get current genre.

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

 

That first query is a waste of space. I think it's a fair bet that if you select the id where the id = 123 then the result will be 123.

 

I don' get it.

 

Then read the query carefully

[Barand]

is this the query you wanted

SELECT m.id, m.title, m.year,
     GROUP_CONCAT(DISTINCT g.genre ORDER BY g.genre) AS genres 
FROM movies m     
     INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
     INNER JOIN genres g ON (g.genre_id = mg.genre_id)
     INNER JOIN
         (
         SELECT genre_id FROM moviegenres
         WHERE movie_id = $movieid
        ) as gen ON mg.genre_id = gen.genre_id
WHERE m.id <> $movieid
GROUP BY m.id 
ORDER BY COUNT(DISTINCT g.genre) DESC, m.id 
LIMIT 0,8

Edited March 4, 2013 by Barand

[rereze]

Thank you very much. Could you help me with one more thing? I need to get 5 actors who have acted in the most movies. How to do it?

 

 

SELECT m.id, m.title, m.year, 
GROUP_CONCAT(DISTINCT a.actor_name ORDER BY g.actor_name) AS actors 
FROM movies m 
INNER JOIN movieactors ma ON (ma.movie_id = m.id) 
INNER JOIN actors a ON (a.actor_id = ma.actor_id) 
GROUP BY a.actor_name
LIMIT 5

[Barand]

SELECT a.actor_name,

    COUNT(ma.movie_id) as movietotal

FROM movieactors ma

    INNER JOIN actors a ON a.actor_id = ma.actor_id

    INNER JOIN movies m ON m.id = ma.movie_id 

GROUP BY a.actor_name

ORDER BY movietotal DESC

LIMIT 5

[teynon]

This kind of sounds like homework.

[rereze]

No it's no a homework. My previous movie DB was in one table. I change it to have relationship of genres and actors. But now i really don't understand how to get some stuff from it. Again i have a question.

To create pagination i need to get all movies where one of the actor have acted in this movie.

 

 

	$page_query 	= mysql_query("
	
SELECT m.id, GROUP_CONCAT(a.actor_name) AS actors 
							FROM movies m 
							INNER JOIN movieactors ma ON (ma.movie_id = m.id) 
							INNER JOIN actors a ON (m.id = ma.actor_id) 
SELECT COUNT(id), actor_name from `actors` WHERE `actor_name` = '$actor_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;