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-＆gt; conn-＆gt; 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-＆gt; 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
    评论
    解决 无用
    打赏 举报

报告相同问题？

提交

相关推荐 更多相似问题