MySQLi - 返回二维数组

Question

有没有办法让MySQLi返回多维数组？

假设我正在从数据库中选择博客文章，并且每篇文章都有多个标签。据我所知，我会这样做：

$r = $mysqli->query("SELECT * FROM posts");
while ($post = $r->fetch_assoc()) {
   //echo blog posts
    $t = $mysqli->query("SELECT * FROM blog_tags INNER JOIN tags ON tags.id = blog_tags.tag_id WHERE post_tags.post_id = ".$post['id']);
    while($tag = $t->fetch_assoc()){
        //echo tags
    }
}

但我更喜欢这样做：

$r = $mysqli->query("SELECT * FROM posts INNER JOIN post_tags ON posts.id = post_tags.post_id INNER JOIN tags ON tags.id = post_tags.tag_id");
while($post = $r->fetch_assoc()){
    //echo post
    foreach($post['tags'] as $tag){
        //echo tags
    }
}

所以回报将是：

$posts = array(
    "id" => 1,
    "title" => "Blog post 1",
    "content" => "Lorem ipsum...",
    "tags" => 
        array(
            "tag1", 
            "tag2", 
            "tag3"
        ),
    "posted" => "1-1-2010 11:11:11"
);

Answer 1

您需要使用 GROUP BY 和 GROUP_CONCAT 。我猜标签名称字段称为name

SELECT posts.*, GROUP_CONCAT(tags.name) as tagnames FROM posts
INNER JOIN post_tags ON posts.id = post_tags.post_id
INNER JOIN tags ON tags.id = post_tags.tag_id
GROUP BY posts.id

然后，当您获得结果时，您可以explode(",", $t->tagnames)获取标签数组。