php - Get all posts from a user with their category's -

the situation

i'm working on blog-system , i'm stuck @ trying posts category's made particular user.

the query shows first post in database, no madder how many posts user has. also, generated category-output wrong.

database

here sql-commands create 3 required tables.

post

create table post(     headline varchar(100),     date datetime,     content text,     author int unsigned,     public tinyint,     type int,     id serial,     primary key (id), )engine=innodb default charset=utf8 collate=utf8_unicode_ci; 

author id of user created post, public determines if post can read or draft , type determines if it's blog-post (0) or else.

category

create table kategorie(     name varchar(30),     short varchar(200),     id serial,     primary key (name) )engine=innodb default charset=utf8 collate=utf8_unicode_ci; 

post_kategorie

create table post_kategorie(     post_id bigint unsigned,     kategorie_id bigint unsigned,     primary key (post_id, kategorie_id),     foreign key (post_id) references post(id),     foreign key (kategorie_id) references kategorie(id) )engine=innodb default charset=utf8 collate=utf8_unicode_ci; 

the query

this query have, mentioned above, doesn't work want to.

select post.headline, post.date,   concat(   "[", group_concat('{"name":"',kategorie.name,'","id":',kategorie.id,'}'), "]"  ) "categorys" post, kategorie, post_kategorie post.author = 1 and(   post.public = 1   , post.type = 0 )and(   post_kategorie.post_id = post.id   , post_kategorie.kategorie_id = kategorie.id ) 

since 1 post can have multiple category's, query generates json decoded object in php. easies way archive this, thought.

what missing here?

you missing in end:

group post.headline, post.date 

update

also, noticed when post has no categories, not appear. problem performing join via where clause. when that, join behave inner join.

in order have posts always, need left join:

select post.headline, post.date,   concat(   "[", group_concat('{"name":"',kategorie.name,'","id":',kategorie.id,'}'), "]"  ) "categorys" post    left join post_kategorie      on post.id = post_kategorie.post_id   left join kategorie      on post_kategorie.kategorie_id = kategorie.id post.author = 1 , post.public = 1 , post.type = 0 group post.headline, post.date