join 2 tables through additional one MySQL/PHP


Let's say we have 2 tables


and we also have

[movieID, actorID]

what i'm trying to do is to join those movie and actro via movie_actor, so that i can select a movie and see every actor, that stars in it.

$db->prepare("SELECT * 
FROM film_info fi 
INNER JOIN film_actor fa 
ON fi.film_id = fa.film_id 
INNER JOIN actor_info ai 
ON fa.actor_id = ai.actor_id");

with something like this. It works, but gets me an array of all the film information for every actor thats stars in it. For example, if there's six actors it will return 6 arrays of same movie info, only having different[actor_name] every time.

Which stands a question: Is there any way to make it return one array with a list of all the actors? Or should i just write a function to manually pick all the actors from 6+ arrays?

Sorry for my terrible english and thanks in advance!




You shouldgroup by the movie, thengroup_concat the actors so they are all returned in the 1 movie row. You also can useusing for theon when the columns of both tables have the same name. This should do what you are asking:

SELECT fi.movie_title, group_concat(actor_name_column) 
FROM film_info fi 
INNER JOIN film_actor using(film_id) 
INNER JOIN actor_info using(actor_id) 
group by film_id

People are also looking for solutions to the problem: PHP session lost after redirect


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.