Skip to content
CodeWorth
CodeWorth

Code worths when shared

Primary Navigation Menu
Menu
  • BLOG
    • DataBase
    • Programming
    • Mobile
    • Multimedia
    • OS Tips
    • Others
    • Web Development
  • Products
    • Utilities
    • Games
    • JsGenLib
      • PlugIns
      • Core Functions
      • Helper & ShortHands
  • About

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

On August 10, 2010
In DataBase
Tagged MySql
With 0 Comments

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.

2010-08-10

Subscribe

Enter your email address to receive notifications about new posts via email.

Join 634 other subscribers

Google

Designed using Chromatic WordPress Theme. Powered by WordPress.