I have a fairly simple question... I have a 'users' table that holds member info and a 'usergroup' table that assigns a specific usergroup(s) (like advertiser, best avatar, etc) with a specific user. For instance, most users do not have a record in the 'usergroup' table, but those who are members of a usergroup, have a record in the'usergroup' table. Sometimes they have more than one, if they are a member of more than one usergroup.
I have a forum-like application that outputs posts made by users and I want to also retrieve each users usergroup information (if they have some) with this query. The problem I am running into is that it is only returning one usergroup record if more than one usergroup record is present for a user. So if a user has say 5 usergroup records in the 'usergroup' table, I would like to have all 5 records within the data set so I can use it in my application. By using the LEFT JOINS below, it is only returning one of the records.
Is there a way to return all the records in the 'usergroup' table that belong to the specific user the while loop is on (mysql_fetch) in some sort of array or something? Thanks for your help!
PHP Code:
SELECT `u`.*, `ug`.*, `p`.* FROM `users` AS `u` LEFT JOIN `usergroup` AS `ug` ON (`u`.`usergroupid`=`ug`.`usergroupid`) LEFT JOIN `posts` AS `p` ON (`u`.`userid`=`p`.`userid`);