php - Do it on one query: What's the best way? -

i'm building php5 application , need list projects in , related information them. that, have 3 tables, named: kc_projects, kc_project_members , kc_users. have id in of them, , have column in each row in kc_project_members linking respective user project registered in.

for now, have query:

select * kc_projects p inner join kc_project_members pm on pm.project_id = p.id inner join kc_users u on u.id = pm.user_id 

but isn't working like, because retrieves results want, not in way want. creates array, , array, , inside array have array containing query results. creates me duplicated result each user, means if have 1 user registered in project, returns me 1 project. if have more, return's me arrays as registered users, , arrays have same project information. him return 1 project, , inside array containing users.

is there anyway this, right way?

thanks,
scorch

i'm assuming arrays mean result sets ...

yes - possible, not you've imagined it, because result sets must have same count of columns, not possible in case have ... 2 project members project id 1 , 5 project members project id 7.

you'll have specify columns want returned - select * won't trick(and it's bad thing anyway, if performance required). , also, users' ids, usernames or whatever columns need referenced users per project have concatenated in string, should parse later php.

here's example:

select p.id, p.project_name, group_concat(u.username separator ',') project_members kc_projects p      join kc_project_members pm on p.id=pm.project_id      join kc_users u on pm.user_id=u.id group p.id;