CODEWITHSUNDEEP
phpmysqldatabase
Giorgi Chumburidze
Giorgi Chumburidze

Reputation: 13

How to get rows with array of ids in MySQL

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 ?

Upvotes: 1

Views: 1364

Answers (1)

abl
abl

Reputation: 5958

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 */

Upvotes: 1

Related Questions