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.

Use a LEFT JOIN in your SQL xlause.

user5051310
– user5051310

2016-01-03 04:42:03 +00:00
Commented Jan 3, 2016 at 4:42 — user5051310
– user5051310, Commented Jan 3, 2016 at 4:42

Abhishek · Accepted Answer · 2016-01-03 05:13:11Z

2

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.

edited Jan 3, 2016 at 5:13

answered Jan 3, 2016 at 4:59

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Will Marchesi Over a year ago

This would return an array with users and their corresponding group_id's if they are in a group. Is there any way that purely using SQL I could go off of this and also find the group_name from the groups table based on the group id I just added to the array of users? Hope this makes sense...

javaMoca · Accepted Answer · 2016-01-03 06:06:04Z

0

I don't see why a left join is necessary. Should be a regular join unless he wants to display users that don't have groups.

By the way, this is the perfect example of something that should be done in the DB with a select. Pulling everything from these tables and trying to sort through them in java is not a good idea. Maybe I read this post wrong though.

answered Jan 3, 2016 at 6:06

javaMoca

1757 bronze badges

1 Comment

Will Marchesi Over a year ago

I am using php not java

Collectives™ on Stack Overflow

How To Merge Array Data From Database

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related