帖子列表里面怎么查出标签

Question

list和tag都是数据库里的数据，想要用mysql这样的查询语句查询出来，得到下面的查询结果，在不用foreach这样的php循环语句下得到结果

list => array(
    array(id => 1, title => '这是title'),
    array(id => 2, title => '这是title2'),
)//list_id和list里的id关联
tag => array(
    array(id => 1, list_id = 1, name => 'tag1'), 
    array(id => 2, list_id = 1, name => 'tag2'), 
    array(id => 3, list_id = 2, name => 'tag3'), 
);

这样的，该怎样查出

select => array(
array(
    id => 1, 
    title => '这是title'，
    tag =>array(
       array( id =1, name => 'tag1'),
        array(id=2,name='tag2')
    ),
),
array(
    id => 2, 
    title => '这是title2'
    tag =>array(
        array( id =3, name => 'tag3')
    ),
),
)

这样的数据？

PS:就像本站的列表是怎么显示的标签的？

Answer 1

SELECT posts.id AS post_id, posts.title AS post_title, GROUP_CONCAT(tags.name AS tag_name)
FROM posts, tag_posts, tags
WHERE tag_posts.post_id = posts.id
AND tag_posts.tag_id = tags.id 
GROUP BY post_title

Answer 2

额，突然明白过来你可能是在问MySQL的查询语句..好囧，好像这个需求能用JOIN搞定？SELECT * FROM `list` LEFT JOIN `tag` ON list.id = tag.list_id，这个试试？

$lists = array();
foreach($list as $item) 
    $lists[ $item['id'] ] = array('id'=>$item['id], 'title'=>$item['title']);

foreach($tag as $item) {
    $lists[ $item['list_id'] ]['tag'][] = array('id'=>$item['id'], 'name'=>$item['name']);

/*为了得到你的问题中的效果，其实这步有点多余。*/
$lists = array_values($lists);