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 u are working on a blog site.
a post will have many tags for that , u just need to display la of them under post separated by a space.

if u write a php code then it's really bad - rs resultset , r row , qry -  select * from tags where post=1

foreach($rs as $r)
{
    echo " ".$r['tag'];
}

so if there are 150 tags apache has to loop for 150 times

imagine u can do this in mysql itself  -   believe it or not i wrote a query for that (i

took basic concept from rownum in a mysql 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'];


U want a link for tag ? sure u can , just use ' in qr. n ' in html

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


just change href's yar.

U can use this in many places like displaying a site link index
sr. to say this,
I can guarantee u that no one else understands other than u n who know this   -  and if this is going for modification to an experienced guy they will remove ur code and change as they like.

So It's developer's responsibility for low performance of a site.

Wan't to generate a html template for all records sure see this.


Thanks for reading - your comments encourage me, Rama Krishna Chunduri
Share