join 2 tables through additional one MySQL/PHP
Let's say we have 2 tables
#film_info
[
movieID
movieValue1
movieValue2
]
#actor_info
[
actorID
actorValue1
actorValue2
]
and we also have
#film_actor
[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!
Answer
Solution:
You should
group 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: