这是我的查询：
<$ p $ （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'）; （'movies'）; $ this-> db-> $ 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'）;这是我目前得到的结果：
阵列（ [id] => 19908 [movie_title] => Zombieland [概述] => ;一个容易吓倒的家伙...... [genre_id] => 28,12,35,27 [genre_name] =>动作，冒险，喜剧，恐怖）这就是我想要的：
Array （ [id] => 19908 [movie_title] => Zombieland [overview] =>一个容易被吓坏的家伙。 ... [genres] => array（ 0 => array（'id'=> 28，'name'=> Action 1 =>数组（'id'=> 12，'name'=> Adventure ）， 1 =>数组（'id'=> 35，'name'=>喜剧）， 1 =>数组（' id'=> 27，'name'=>恐怖）））这是否可能，以及如果是这样，怎么做？
您列出的查询将包含 n 行（其中 n =电影数量），而你想要的查询将会有更多的行（movie_genre条目的＃）。你可能最好离开这个查询，并进行一些后期处理。考虑：
获得后，只需运行你的结果（如 $ result ）数组： > foreach（$ result as& $ row） { //逗号分割 $ gi_elements = explode（'，'，$ row ['genre_id' ]）; $ gn_elements = explode（'，'，$ row ['genre_name']）; //构建流派 $ row ['genre'] = array（）; for（$ i = 0; $ i< count（$ gi_elements）; $ i ++） { $ row ['genre'] [] = array（'id'=> $ gi_elements [$ i]，'name'=> $ gn_elements [$ i]）; } //清理 unset（$ row ['genre_id']）; unset（$ row ['genre_name']）; }
之后，$结果看起来完全如您所愿，无需额外的数据库工作。 / p>
编辑： 修正了一些错别字。
So, I have three tables. Movies, movies_genres and genres. I want to get a movie by its Id, and also join its 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?
解决方案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.
更多推荐
加入后分组结果？
发布评论