Joining rows as array from another table for each row

I am coding a movie archive script using codeigniter for myself wi开发者_JAVA百科th PHP. I am getting movie's information from IMDb and adding them to my database. I'm adding links for movies I selected using another table called links.

This is the query that I am using to get the movies from database:

    $movies = $this->db->query("SELECT *
  FROM movies
  ORDER BY ".$order['by']." ".$order['type']."
  LIMIT ".$limit.", " . $this->config->item('moviePerPage')
  );

and I am fetching it in view file like this:

foreach ($movies->result() as $row) {
  echo $row->name;
}

Now links must be shown for each movie with the matched IDs (movies could have more than one link). My links table has these columns: 'id', 'movieid', 'name', 'link'

How can I get links for each movie with single MySQL query? Is is posible to get all links that related to current $row movie and bind them to a single variable as array? with this I can loop it with foreach() and show links for each movie.

btw: movies.movieid and links.movieid columns have the same data.

I think you need mysql's GROUP_CONCAT

Do something like this:-

SELECT 
    movies.*, 
    group_concat(links.link ', ') as links
FROM movies 
LEFT JOIN links 
ON links.movieid = movies.movieid 
GROUP BY movies.movieid

You will get a comma separated list of links for every movie. Which you can extract like this:-

foreach ($movies->result() as $row) {
  $linksArray = explode(",",$row->links);
}

Updates I think this is the only way you can get the results without having multiple result rows for a single movie with multiple links.

Just be careful of the maximum length of characters you can get in the result - by default 1024 characters. Read this Mysql group_concat_max_length and Group concat max length to know how to override the limit.

And like Dan Grossman has poined out, if you feel the links may contain comma, use a different or uncommon delimiter.

JOIN the two tables, just as your question name implies.

SELECT 
    movies.*, 
    links.* 
FROM movies 
LEFT OUTER JOIN links 
ON links.movieid = movies.movieid 
...

You will get one row per movie-link pair. If there are no links for a movie, you'll get a row with just the movie information and NULLs in the columns corresponding to the links table.

You can loop over these results and put the links for each movie in an array keyed by the movie, if you'd like.