Fetching using group_concat in PHP/MySql

Question

I was trying to get the list of usernames separated by comma(,) and I used this SQL:

$sql="select group_concat(concat(' ',username,' ')) as username from user";
$var=mysql_query($sql);
while($v=mysql_fetch_assoc($var)){ $variable=$v['username']; }
echo $variable;

Ignore the mysql_* function here.

The above SQL helped me to get the list of all user in the database and echo it but I got a problem when I used LIMIT 3 and/or ORDER BY rank at the end like:

$sql="select group_concat(concat(' ',username,' ')) as username from user ORDER BY rank LIMIT 3";

After I add ORDER BY.... there comes no change to the result.

Is there any way to fix the above code using ORDER BY rank LIMIT 3 at end of SQL? or is there any other method to do it?

Thanks in Advance!

Answer 1

fix

filter your users in subquery before passing to group_concat..

adjusted query

select group_concat(concat(' ',username,' ') ORDER BY rank ) as username 
from
(
select id, username, rank
from user
order by rank
limit 3
) filtered_users
;

output

+-----------------------+
|       username        |
+-----------------------+
| jonny , zelda , maria |
+-----------------------+

sqlfiddle

Answer 2

SELECT GROUP_CONCAT(username )  
FROM user
GROUP BY id;

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

please check this, This will help you.