Grouping queries

[neller]

Hi guys,

 

Does anybody know how to do the following in MySQL...

 

Lets say I had 2 tables to do a basic join on..

 

table1 (list of names) (user_id, username)

table2 (list of friends for the people in table one, linked by user_id)

 

So I do a basic MySQL join query to get all the names and friends from both tables, but then in the PHP loop I end up with the names from the first table repeated for as many friends results as it finds, for example...

 

username - Alan, friend_id 1

username - Alan, friend_id 2

username - Alan, friend_id 3

 

username - Joe, friend_id 1

username - Joe, friend_id 2

username - Joe, friend_id 3

 

etc etc

 

Idealy I need the loop to print like the following....

 

Username - Alan

friends 1,2,3

 

Username - Joe

friends 1,2,3

 

I have tried GROUP_BY and stuff, however that just limits the result to 1 row per user so only returns the first friend_id.

 

I hope the above makes sense, if anybody can point me in the right direction I would be very grateful,

 

Thanks

[neller]

Not sure if there is a better way but decided to do it on the PHP end with 2 loops.

 

first I looped through the database results and set all an array with the username as the key and the friends ID as a sub array, like so:

 

$users[$user_id][] = $friends_id

 

to everytime it loopts through the databse results, any duplicate names won't matter as they are just used as the 1 key in the main array.

 

then in my second loop, I looped through the $users array

 

foreach($users as $username => $users_friends_array) {
  foreach($users[$username] as $key => $friends_name) {
     echo $friends_name;
  }
}

 

I would not advise using it as its probably not the best piece of coding  , 3 loops just to do such a simple thing, however just thought I would post my result as its working fine, but if anybody has a better way please share, thanks

[fenway]

the better way is to join the tables properly in mysql, and then have the application code sort out the rest.

[neller]

Yeah that was pretty much what I was asking for help with in my original post

 

As I stated in my second reply its probably not a good way to solve my problem and I would not recommend using it, but for now until I learn how to solve it in MySQL I thought I might as well post "my fix" just in case anybody else was totaly stuck for the time being.

 

I'm usually pretty good with both joins and mysql, not sure why im so stuck here, probably missing something quite stupid.

[fenway]

Can you post the join you've written, and we can take it from there/

[neller]

Yeah sorry I did not post my code I usually would and I know you recommend doing so in that "please read" thread (I have read it )

 

The only reason I never is because imo it makes this simple problem look more complex than it is, I just figured the problem I listed in my first post was something simple I was just missing, anyways here is my code...

 

SELECT `w`.`venuecode`, `w`.`workoutdate`, `v`.`venuename`
			FROM `workouts` `w`
			INNER JOIN `venue` `v`
			ON `w`.`venuecode` = `v`.`venuecode`
			ORDER BY `v`.`venuename`, `w`.`workoutdate` DESC

 

in the above code just think of the "workouts" table as my "friends" table and the "venue" as "users" from my first post, I will try to be more precise when posting in the future 

[fenway]

OK -- well, if that join returns the expected result, you're just left with application code to adjust the layout per "section".

[neller]

as in just a better way than I did it in my second post?

[fenway]

as in just a better way than I did it in my second post?

Well, technically this belongs in the php forum, but if you've ordered the result set correctly, they will already be in the desired order.  All you'll need to do is "notice" when the section changes.

 

I can move this topic to the php board if you'd prefer.

[neller]

ahhh I just thought of how to do it after reading your last post, and can do so from the 1 database loop

 

Sorry its now off topic, I posted it in the MySQL section hoping the thing I was asking could be done in mysql, I'm pretty good with mysql but not on the advance level with functions and procedures etc so wasn't sure if it could be done that way.

 

thanks for your help