How To Merge Array Data From Database

Question

I have a database table for a user of my website, this table gives each user a user_id.

Using normalization, I have linked the user to a group with a user_group table including user_id and group_id to link a user to a group.

I then have a group table that links a group name to the group_id.

I am trying to output the users on my webpage in a list next to the name of the group, not its id.

I was thinking of using a foreach loop to do this, but the data would need to get into one array? I dont know how I would take the user_id, find out which group _id it it paired with, find out which group name that id was paired with, and then add that to the array with the names and be able to display the group name next to the user's name using foreach.

Thanks for any help.

Answer 1

If i understand you correctly, you want to join you users data to group to show the user details with their group name.

Try out this:

select u.*,g.*,ug.*  from users u 
left join user_group ug on u.user_id=ug.user_id
left join `group` g on ug.group_id=g.group_id;

From above query you may get blank values for some user's, who are not assigned to any group.