How to get rows with array of ids in MySQL

Question

I'm Trying to get genres list for specify movie, there is structure of my tables

First Table is Genres

+------------------------------+
| id   |  name   | description |
+------------------------------+
| 15   |  Comedy |  Desc...    |
| 21   |  Drama  |  Desc...    |
+------------------------------+

Second Table Is Movie_Genres

+-------------------------------+
| id   | genre_id  |  movie_id  |
+-------------------------------+
|  1   |    15     |  231423    |
|  2   |    21     |  231423    |
+-------------------------------+

And I want to get all Genres for Movie with id 231423

Website is multilingual and to get movie information using this code

  $movie = $this->app->db->rawQuery("SELECT Movies.*, Movies_Content.*
                                 FROM Movies
                                 INNER JOIN Movies_Content
                                 ON Movies.id = ? AND Movies_Content.movies_id = ? AND Movies_Content.i18n = ?", array($id, $id, 'en'));

tried to use code like this but getting error

How to create query for getting all genres information for specify movie ?

Movie_Content table stores translatable data: title, movie_id, overview, i18n In Movie table storing poster, release data and other static data that don't need translating P.S. its multilingual movie website — Giorgi Chumburidze
– Giorgi Chumburidze, Commented Sep 20, 2014 at 13:18

abl · Accepted Answer · 2014-09-20 13:18:56Z

1

You can get any info from the Genres table, for the genres that belong to a specific movie, with this query:

SELECT g.*
FROM Genres g
  INNER JOIN Movie_Genres mg
    ON g.id = mg.genre_id
WHERE mg.movie_id = 231423 /* your movie id here */

answered Sep 20, 2014 at 13:18

abl

5,9684 gold badges27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get rows with array of ids in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related