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

Question

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

Answer 1

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