Group By

[MrXHellboy]

I have a simple query:

SELECT name, skill FROM members as a LEFT JOIN skills as b ON a.id = b.member_id

 

For instance, we have 1 name with 4 skills. Within a loop it will result in 4 times the name, followed with the skill.

 

But i want to have 1 name with all of the skills. Not 4 times the name with each time a other skill. Group By name results in 1 single name with 1 single skill while he has 4 skills.

 

Someone has a solution ?

[The Little Guy]

try this:

 

SELECT m.name, s.skill FROM skills s LEFT JOIN members m ON(s.member_id = m.id)

[MrXHellboy]

Unfortunately it didnt work out..

 

My member table has 3 fields -- id, name, nickname

My Skills table has has 3 fields -- id, member_id , skill

 

Maybe this is helpfull ?

[The Little Guy]

Your first post is a little unclear to me.

[MrXHellboy]

The post above here will describe my tables... This is the result now:

Laurens Skills: Football

Max Skills: Football

Laurens Skills: Football

Max Skills: Swimming

Max Skills: Football

Laurens Skills: Swimming

Laurens Skills: Football

Laurens Skills: Football

Laurens Skills: Swimming

 

As you can see (dont pay attention to the skills), just a test, it will result in name -> skill, done with the following code:

$Query = "SELECT name, skill FROM members as a INNER JOIN skills as b ON a.id = b.member_id";

$result = mysql_query($Query)
                    or die ('Something went wrong!');
                    
                    
while ($row = mysql_fetch_object($result))
{
    echo $row->name.' Skills: '. $row->skill.'<br />';
}

 

I want to have:

Laurens: -> All the skills

Max: -> All the skills

 

Not name and skill, time after time

[The Little Guy]

you don't want to do that, you want get all the results order them by member, then loop through them with php for each member

[MrXHellboy]

How would you suggest something like that ?

[The Little Guy]

Untested:

 

$sql = mysql_query("SELECT m.id, m.name, s.skill FROM skills s LEFT JOIN members m ON(s.member_id = m.id)");
$skills = array();
while($row = mysql_fetch_assoc($sql)){
    $skills[$row['id']][$row['name']][] = $row['skill'];
}

foreach($skills as $name => $skill){
    echo $name . implode(', '$skill[$name]);
}