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

Comments

Post a Comment

Popular posts from this blog

c++ - Is it possible to compile a VST on linux? -

for class project i'm attempting write vst plugin backed cuda. current cuda workflow on linux box, i'd prefer compile , link there. according wikipedia , should possible (i couldn't find steinberg documentation relevant linux) can't find makefile or instructions on how build if aren't using xcode or visual studio. i'm vst 3 sdk doesn't support linux. when try compile plugin under linux, error: ./base/source/fatomic.cpp:39:30: fatal error: libkern/osatomic.h: no such file or directory this issue caused following code in "vst3 sdk/base/source/fatomic.cpp" #if mac #include <libkern/osatomic.h> #if mac_os_x_version_min_required > mac_os_x_version_10_4 #define native_atomic_type (volatile int32_t*) #else #define native_atomic_type (int32_t*) #endif #elif windows #include <windows.h> #endif but hope compiling under linux work vst sdk 2.4. reading. jvstwrapper seems run on lin...
Read more

c# - SharpSVN - How to get the previous revision? -

i trying find efficient way previous revision of file text comparison using sharpsvn. using (svnclient c = new svnclient()) { c.authentication.defaultcredentials = new networkcredential( configurationmanager.appsettings.get("svnserviceusername") , configurationmanager.appsettings.get("svnservicepassword") , configurationmanager.appsettings.get("svnservicedomain") ); c.authentication.sslservertrusthandlers += new eventhandler<svnsslservertrusteventargs>(authentication_sslservertrusthandlers); collection<svnfileversioneventargs> fileversioncollection = new collection<svnfileversioneventargs>(); svnrevisionrange range = new svnrevisionrange(0, this.hooks.revision); svnfileversionsargs args = new svnfileversionsargs(); args.retrieveproperties = true; args.range = range; foreach (svnchangeitem item in log.changedpaths) { string path = this.repositorypath + it...
Read more

php cli reading files and how to fix it? -

while using php cli in ubuntu got apache web server cant read file can while using in browser. , error shows nothing , next used code testing purposes : <? $handle = fopen("testfile.txt", "r"); if ($handle) { while (($buffer = fgets($handle, 4096)) !== false) { echo $buffer; } if (!feof($handle)) { echo "error: unexpected fgets() fail\n"; } if(!is_readable($handle) { die("not readable"); } fclose($handle); } ?> how fix ? edit : after removing '@' before fopen got following error fopen(testfile.txt): failed open stream: no such file or directory in /var/www/log/ka.php on line 2 when run script through cli, uses shell's working directory, in opposition file's directory (which apache hands current directory). important you, since fopen call depends on relative path; , relative paths resolved relatively working directory, not script. to have script behave apache, eit...
Read more