Category Archives: tagging

MySQL: String concat multiple rows

If you are implementing a tagging system like what you see with WordPress, then you may have a requirement to fetch all the tags of each article in the blog when displaying the blog. As each article has many tags, one option is to first fetch all the articles and for each article fetch the list of tags. However, there is a better way to do this in one single SQL when using MySQL.

MySQL has the function group_concat that allows one to condense a set of rows into one single row by concatenating the list of values.

The SQL will be something like

select article_id,article_title,article_text,(select group_concat(tag) from tags t where t.article_id = a.id) tags from articles a …;

Here, the subselect used for the tags column is what I am talking about. It uses the group_concat to concatenate all the tags together. By default it uses ‘,’ as the separator and I believe that can be altered.

PHP & MySQL Web Development is a book for learning web development with PHP and MySQL.

Advertisements

20 Comments

Filed under MySQL, tagging