douyi1197
2018-11-02 03:37
浏览 51
已采纳

SQL嵌套为一篇博文选择多个标签

I have 3 tables in a database for the blog i'm working on, posts, users and taxonomy (tags and stuff). my SQL returns and array correctly if I LIMIT 1, but I am not able to get all the tags that go with it.

Is there some sort of nesting I can do to get the tags as a an array so I can loop through them and add them to my post in one query?

The posts I've seen don't have a solution.

Needed result

{postname:"post", author:"this author",content:"stuff here", {tags: "a","b","c"}}

What ive tried:

$stmt = $this->conn->prepare("
 SELECT P.post_title,P.post_featimg, P.post_excerpt, P.post_quote,
 P.post_content, P.post_date, P.post_position, 
 U.user_firstname, U.user_lastname,
 T.taxonomy_tag        
 FROM c_blogposts P
 JOIN c_users U
 ON P.post_author and U.id
 JOIN c_taxonomy T
 ON P.post_id and T.post_id
 WHERE P.post_id = :post_id");
 $stmt->bindParam(':post_id', $id);

It will be easier to work with this fiddle as an example. Thanks

https://www.db-fiddle.com/f/u3JHfUJAZMuRWsudyLpUaS/0

图片转代码服务由CSDN问答提供 功能建议

我在一个数据库中有3个表用于我正在处理的博客,帖子,用户和分类(标签和 东西)。 如果我限制1,我的SQL返回和数组正确,但我无法获得所有标记。

我可以做一些嵌套来将标签作为一个数组,这样我就可以遍历它们并在一个查询中将它们添加到我的帖子中吗?

我见过的帖子没有解决方案。

需要的结果

  {postname:“post  “,作者:”这位作者“,内容:”这里的东西“,{标签:”a“,”b“,”c“}} 
   
 
 

什么 我试过:

  $ stmt = $ this-> conn-> prepare(“
 SELECT P.post_title,P.post_featimg,P.post_excerpt,P.post_quote  ,
 P.post_content,P.post_date,P.post_position,
 U.user_firstname,U.user_lastname,
 T.taxonomy_tag 
 FROM c_blogposts P 
 JOIN c_users U 
 ON P.post_author and U.id  
 JOIN c_taxonomy T 
 ON P.post_id和T.post_id 
 WHERE P.post_id =:post_id“); 
 $ stmt-> bindParam(':post_id',$ id); 
   
 
 

作为一个例子,使用这个小提琴会更容易。 谢谢

https://www.db- fiddle.com/f/u3JHfUJAZMuRWsudyLpUaS/0

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

  • douzhouqin6223 2018-11-02 03:43
    最佳回答

    You need to use group_concat, This would do also you can customize the separator like comma or any thing you desire using separator syntax, the default is comma.

    SELECT P.content, A.author, group_concat(T.tag)
    FROM c_post P
    JOIN c_author A
    ON P.id and A.id
    JOIN c_tags T
    ON T.id and P.id
    WHERE P.id = 1
    group by P.content, A.author
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题