如何使用mysql和php将一对多转换为平面数组

Question

How can I format a one to many relationship to one 'row' using mysql or php?

Main Table

movie_id name
1        Portland
2        Blazers

Many Table

movie_id actor
1        Brandon
2        Greg

Below is the psudo code I have mapped out. Not sure if there is any thing native to PHP or MYSQl that would be more efficient before I continue. I'm thinking I have two queries. One with the movie information. One with the actor information. Once they are arrays I will loop through to get a single record for each movie with the actors.

movieInfo = = []
actorInfo = = []

Foreach(movieActorId)
    If(actorId = movieActorId)
         arrayPush(movieInfo[actorArray],actorInfo[name]);

The final array would look like this

movieInfo = array('movie_id' => '1', 
                    'movie_name' => 'porland', 
                    'actor' => array(Brandon,Greg)
                    );

Answer 1

Maybe this is what you need:

SELECT m.movie_id, m.name AS movie_name, GROUP_CONCAT(a.actor SEPARATOR ',') AS actor
FROM movies m
JOIN actors a ON m.movie_id = a.movie_id
GROUP BY m.movie_id;

// Run query

$movieInfo = array();
while ($array = mysqli_fetch_assoc($result)) {
    $array['actor'] = explode(',', $array['actor']);
    $movieInfo[] = $array;
}