MySql – Select a field from all the rows separated by text

Select a field from all the rows separated by  say some , or a space
Ex: Consider we are working on a blog site where a post shall have many tags for it , we just need to display all of them under post separated by a space.

if we write a php code then its really extra weight on application server

foreach($rs as $r)
{
    echo " ".$r['tag'];
}
// rs - resultset , r - row , qry - select * from tags where post=1

So if there are 150 tags apache(or any web server) has to loop for 150 times

We can do this in mysql itself so that the weight is designated to DB server while application server is busy serving content to other people accessing some other module in the application.

Query is written deriving from basic concept of rownum in mysql. The below would be query

SET @tags:="";
SELECT @tags:=concat(@alltags,p.tag,",") "AllTags" FROM tags p, (SELECT @alltags:="") a ORDER BY @alltags DESC LIMIT 0,1

So ultimately the php code is

echo $rs[0]['AllTags'];

If we want a link for tag ? sure we can , just use concat and append html link tag

SET @tags:="";
SELECT @tags:=concat(@alltags,"<a href=''>",p.tag,"</a>") "AllTags" FROM tags p,(SELECT @alltags:="") a ORDER BY @alltags DESC LIMIT 0,1

We can use this in many places like displaying a site link index