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
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