3

I have the following tables:

users

id | name

info

id | info | user_id

(user_id from table info is foreign key => connects to id from table users)

It is possible that the user will have many entries in the info table (same user_id in many rows).

Now I want to fetch data and display it like that:

username:

... info ....

Example:

admin:

aaa

bbb

ccc

someuser:

ddd

eee

fff

So I use LEFT JOIN like that:

SELECT users.name, info.info
FROM users
LEFT JOIN info
ON users.id = info.user_id

But what I get is the following:

admin:

aaa

admin:

bbb

admin:

ccc

How can i display the username only once? I've tried using DISTINCT keyword after SELECT but it didn't help. Meanwhile i solve this problem inside the php code itself, but is there any way to fix this only inside the sql?

heradcvb
  • 33
  • 3
  • 1
    You are trying to query and output a nested structure (user is parent of info) directly from mysql. Relational databases are not really cut cut out for that. Doing the formatting in the front-end code is ok here. – Jacob Aug 04 '11 at 13:44
  • The reason why DISTINCT displays admin 3 times is because admin has 3 infos, the question is which of these infos do you need? or don't you need any of them at all? – ace Aug 04 '11 at 13:46
  • @cularis, your comment is completly false. Doing this in de DB is much much faster and easier than doing it in php. – Johan Aug 04 '11 at 13:49
  • @Johan - yep. That's why I asked. I was sure that there is a simple way to deal with this using only sql which will be more elegant and nicer here than the php way :-) – heradcvb Aug 04 '11 at 13:51
  • @Johan You can still do it in one query, like the one he posted. He just has can't out put every row that contains the same users.name as the one before. He should probably have a look at [N+1](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem) – Jacob Aug 04 '11 at 13:52
  • @cularis, what's wrong with a simple `group_concat` ? – Johan Aug 04 '11 at 13:56
  • @Johan I don't see how group_concat simplifies the backend. You still have to split the info column. Also, other RDBMS might not have a similar function. So for me, it's giving up compatibility for no real gain. – Jacob Aug 04 '11 at 13:56
  • @cularis, On the backend you can use echo the output of the group_concat as is, no processing needed, plus you can implode and explode that data when using `,` as a separator, what could be simpler? – Johan Aug 04 '11 at 14:06
  • @Johan I guess it's a matter of taste. The comments are not the place to discuss this further ;) sorry for typo, I meant front- not backend. – Jacob Aug 04 '11 at 14:07

2 Answers2

4
SELECT users.name, GROUP_CONCAT(info.info SEPARATOR 'whateveryouwant') as info
FROM users 
INNER JOIN info ON (users.id = info.user_id)
GROUP BY users.name

By default group_concat uses a , as separator, but you can change that if needed.

Link:
http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

Johan
  • 74,508
  • 24
  • 191
  • 319
  • 2
    Don't forget that: The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. – RiaD Aug 04 '11 at 13:49
  • 1
    The max setting for max_allowed_package is 1GB: `The protocol limit for max_allowed_packet is 1GB`, see: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet – Johan Aug 04 '11 at 13:54
1
$lastname="";
$res=mysql_query($yourQuery);
while($row=mysql_fetch_assoc($res)){
    if($row['name']!=$lastname){
        $lastname=$row['name'];
        print $lastname.':'; // first time print it
    }
    print $row['info'];
}
RiaD
  • 46,822
  • 11
  • 79
  • 123
  • 1
    I know. As i said, i fixed it inside the php code itself. I did the same. But i wonder if there's more simple way. Just by editing the sql. – heradcvb Aug 04 '11 at 13:47