PHP / MySQL - 加入后分组结果？ [英] PHP/MySQL - Group results after join?

问题描述

所以，我有三个表。电影，movies_genres和类型。我想要一个电影的它的Id，还加入它的类型的结果。我设法加入结果，但它不显示为我想要的。我不知道我要求的是否可能。

这是我的查询：

  SELECT`movies`。*，GROUP_CONCAT（genres.id）AS genre_id，GROUP_CONCAT（genres.name）AS genre_name 
 FROM（`movies`）
 INNER JOIN`movies_genres `
 ON`movies_genres`.`movie_id` =`movies`.`id` 
 INNER JOIN`genres` 
 ON`genres`.`id` =`movies_genres`.`genre_id` WHERE`movies`.`id` = 19908 
 GROUP BY`movies`.`id` 
  

查询由Codeigniters Active Record类生成，这里是Codeigniter代码，如果这有助于：

  $ this-> db  - > select（'movies。*，GROUP_CONCAT（genres.id）AS genre_id，GROUP_CONCAT（genres.name）AS genre_name'）; 
 $ this-> db-> from（'movies'）; 
 $ this-> db-> where（'movies.id'，$ movie_id）; 
 $ this-> db-> join（'movies_genres'，'movies_genres.movi​​e_id = movies.id'，'inner'）; 
 $ this-> db-> join（'genres'，'genres.id = movies_genres.genre_id'，'inner'）; 
 $ this-> db-> group_by（'movies.id'）; 
  

这是我当前得到的结果：

 数组
（
 [id] => 19908 
 [movie_title] => Zombieland 
 [overview] => ; An spooked guy ... 
 [genre_id] => 28,12,35,27 
 [genre_name] =>动作，冒险，喜剧，恐怖
）
  

这是我想要的：

  Array 
（
 [id] => 19908 
 [movie_title] => Zombieland 
 [overview] =& .. 
 [genres] => array（
 0 => array（
'id'=> 28，
'name'=> Action 
），
 1 => array（
'id'=> 12，
'name'=> Adventure 
），
 1 = array（
'id'=> 35，
'name'=> Comedy 
），
 1 => array（
'id'=> ; 27，
'name'=>恐怖
）
）
）
  

如果是，如何？

感谢一大早！

对不起的标题，我didn

解决方案

您列出的查询将包含 其中 n =电影＃），而你想要的查询将有更多的行（＃of movie_genre的条目）。

请考虑：

获得后，只需运行你的结果（例如 $ result ）数组：

  foreach（$ result as& $ row）
 {
 //分割逗号
 $ gi_elements = explode（'，'，$ row ['genre_id' ]）; 
 $ gn_elements = explode（'，'，$ row ['genre_name']）; 
 
 // Build genre 
 $ row ['genre'] = array（）; 
 for（$ i = 0; $ i  {
 $ row ['genre'] [] = array（'id'=& $ gi_elements [$ i]，'name'=> $ gn_elements [$ i]）; 
} 
 
 //清除
 unset（$ row ['genre_id']）; 
 unset（$ row ['genre_name']）; 
} 
  

之后，$ results将完全按照你的愿望，而不需要额外的数据库工作。 / p>

编辑： 修正了一些错别字。

So, i have three tables. Movies, movies_genres and genres. I want to get a movie by it's Id, and also join it's genres in the result. I managed to join the results, but it doesn't display as i want it to. I'm not sure if what i'm asking is possible.

This is my query:

SELECT `movies`.*, GROUP_CONCAT(genres.id) AS genre_id, GROUP_CONCAT(genres.name) AS genre_name
FROM (`movies`)
INNER JOIN `movies_genres`
    ON `movies_genres`.`movie_id` = `movies`.`id`
INNER JOIN `genres`
    ON `genres`.`id` = `movies_genres`.`genre_id` WHERE `movies`.`id` = 19908
GROUP BY `movies`.`id`

The query was generated by Codeigniters Active Record class, here is the Codeigniter code if that helps:

$this->db->select('movies.*, GROUP_CONCAT(genres.id) AS genre_id, GROUP_CONCAT(genres.name) AS genre_name');
$this->db->from('movies');
$this->db->where('movies.id', $movie_id);
$this->db->join('movies_genres', 'movies_genres.movie_id = movies.id', 'inner');
$this->db->join('genres', 'genres.id = movies_genres.genre_id', 'inner');
$this->db->group_by('movies.id');

Here is the result i'm currently getting:

Array
(
    [id] => 19908
    [movie_title] => Zombieland
    [overview] => An easily spooked guy...
    [genre_id] => 28,12,35,27
    [genre_name] => Action,Adventure,Comedy,Horror
)

And this is what i want:

Array
(
    [id] => 19908
    [movie_title] => Zombieland
    [overview] => An easily spooked guy...
    [genres] => array(
        0 => array(
            'id' => 28,
            'name' => Action
        ),
        1 => array(
            'id' => 12,
            'name' => Adventure
        ),
        1 => array(
            'id' => 35,
            'name' => Comedy
        ),
        1 => array(
            'id' => 27,
            'name' => Horror
        )
    )
)

Is this possible, and if so, how?

Thanks a bunch in advance!

And sorry for the confusing title, i didn't know quite how to put it.

解决方案

The query you listed will have n rows (where n = # of movies) whereas the query it seems you want will have many more rows (# of movie_genre's entries). You're probably better off leaving that query as it is, and doing some post processing.

Consider:

After you get it, just run your result (e.g. $result) array through something like:

foreach($result as &$row)
{
    // Split over commas
    $gi_elements = explode(',', $row['genre_id']);
    $gn_elements = explode(',', $row['genre_name']);

    // Build genre 
    $row['genre'] = array();
    for($i=0; $i<count($gi_elements); $i++)
    {
        $row['genre'][] = array('id' => $gi_elements[$i], 'name' => $gn_elements[$i]);
    }

     // Cleanup
     unset($row['genre_id']);
     unset($row['genre_name']);
}

Afterwards, $results will look exactly as you wish without extra database work.

EDIT: Fixed some typos.

这篇关于PHP / MySQL - 加入后分组结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！