将每行的另一个表中的行作为数组联接 [英] Joining rows as array from another table for each row

问题描述

我正在使用PHP使用codeigniter为电影存档脚本编写代码.我正在从IMDb获取电影的信息，并将它们添加到我的数据库中.我正在使用另一个称为链接"的表为我选择的电影添加链接.

I am coding a movie archive script using codeigniter for myself with 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;
}

现在必须为每个具有匹配ID的电影显示链接(电影可能有多个链接).我的链接表包含以下列:'id'，'movieid'，'name'，'link'

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'

如何通过单个MySQL查询获取每部电影的链接?是否有可能获取与当前$ row电影相关的所有链接并将它们绑定到单个变量数组中?这样，我可以使用foreach()循环播放它，并显示每部电影的链接.

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.

顺便说一句:movies.movi​​eid和links.movi​​eid列具有相同的数据.

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

推荐答案

我认为您需要mysql的

I think you need mysql's GROUP_CONCAT

执行以下操作:-

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.

请注意结果中可以包含的最大字符长度-默认为1024个字符.读这个 Mysql group_concat_max_length 和组连接最大长度了解如何超越限制.

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.

就像丹·格罗斯曼(Dan Grossman)所说的那样，如果您认为链接中可能包含逗号，请使用其他或不常见的定界符.

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

这篇关于将每行的另一个表中的行作为数组联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！