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

In some cases, we have to form a string out of multiple records in the database. For example 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 extra weight on the 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 webserver) has to loop for 150 times. This becomes a big burden for the server. It also can cause overhit on the server causing a delay in responding to the other users.

Following 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 a 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.