Using GROUP_CONCAT in a single to many JOIN query with MySQL

I’ve had this conundrum before, but I didn’t know how to do it until now. What if you are doing a MySQL query and pulling a bunch of rows. But then each of those rows has more data in another table, which can have multiple rows itself. Normally you’d have to loop through the first query with PHP and run the second query.

Well I figured it out how to do it using GROUP_CONCAT. But first, here is the “old way” of doing what I’m talking about:

[code]$query = “SELECT * FROM `posts`”;
$result = mysql_query($query);
while ($POST = mysql_fetch_array($result)) {
$query2 = “SELECT `tag` FROM `tags` WHERE `PID`='{$POST[“PID”]}'”;
$result2 = mysql_query($query2);
while($TAG = mysql_fetch_array($result2)) {
.. code goes here ..
}
}[/code]

So what ends up happening is for each row in posts, you run that second query. So if there are 100 posts it ends up being 101 queries run each time. Using GROUP_CONCAT you can get the same info, but with only one query:

[code]$query = “SELECT *,
(SELECT GROUP_CONCAT(tag) FROM tags WHERE tags.PID = posts.PID) AS lists
FROM posts”;
$result = mysql_query($query);
while ($POST = mysql_fetch_array($result)) {
… code goes here …
}[/code]

Essentially GROUP_CONCAT is running that extra query in the background, grabbing all the tags, and combining them into one string separated by commas. So you’d get all the tags without having to run additional queries.

Why care? Well for one, speed and efficiency. Which is my favorite thing. By having only one query goto the database, the database can do the heavy lifting while it’s got everything going. Each query has an overhead and bandwidth, so anytime you can combine queries is good!