Nintendo Wii forums Forum for writers phpBB mods Essays on copyright Dating advice Narnia forums

Doing MySQL's GROUP_CONCAT with PHP code

I was working on a client's project yesterday, and came across a sad discovery – their server uses MySQL 4.0.  I only noticed because I was trying to use the GROUP_CONCAT feature, which it turns out is only in MySQL 4.1 and above.  Bummed, I set about blowing 2 hours recreating the feature in PHP.  Having done that, I thought I'd save my fellow geeks some time.  Here's a quick tutorial, with code.

What is GROUP_CONCAT for?

It's great for grouping and listing out data on reports.  As an example, say you have a database that logs each file a member downloads.  Assuming that each file is logged as a new record in your database, you end up with loads of records for each member.  So you want to generate a report that shows the member's name on the left, and all the files they've downloaded in a list on the right.  Normally, I do this by coupling the GROUP BY feature with GROUP_CONCAT.

Example?

Sure.  Consider this tiny database table called downloads:

username file
paul movie.wmv
paul files.zip
andy files.zip

...Now assume that you want your report to look like this:

paul movie.wmv, files.zip
andy files.zip

OK?  You can get that with this query:

SELECT username, GROUP_CONCAT(file SEPARATOR ', ') FROM downloads GROUP BY username

The GROUP BY tells MySQL to only show one row per username.  That usually means it will only show one file, too.  But GROUP_CONCAT comes to the rescue, directing MySQL to concatenate all that member's files into one long list (using a comma and a space between each file name).  Suddenly we have the report data in the format we wanted.  So now that we know the basics, let's see how to generate the same report using PHP code.

How can PHP mimick GROUP_CONCAT?

First, let's start with the sad little SQL query that only contains MySQL 4.0 features:

SELECT username, file FROM downloads

That's just going to give us a straight dump of all the records.  We're going to load the results of that into a PHP array.  It uses the standard PHP stuff for executing the query and looping through the results.  Here is that code:

$names = array();
$query = 'SELECT username, file FROM downloads';
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
if ($num_rows) {
  while ($row = mysql_fetch_array($result)) {
    array_push($names[$row['username']], $row['file']);
  }
}

What just happened there?  The first 6 lines should be obvious, if you've done any database work in PHP.  But that array_push on line 7 is doing some good stuff for us.  It's creating a nested array – $names now has paul and andy as sub-arrays, paul has 2 files, andy has 1.  If you were to list it out, it would look like this now:

$names['paul'][0] == 'movie.wmv';
$names['paul'][1] == 'files.zip';
$names['andy'][0] == 'files.zip';

OK?  Having done that, the only thing left to do is loop through the $names array, and print the files for our report.  But wait, you say.  We already used while to loop through the records on line 6.  Why not just print the files for the report there?  Well, yeah, but that would list out each file on a new line.  We're going to use foreach to run a second loop, and implode to concatenate all the files.  Here is the code for that:

$html = "<table>\n";
foreach ($names as $name => $files) {
  $html .= "<tr>\n";
  $html .= '<td>' . $name . "</td>\n";
  $html .= '<td>' . implode(', ', $files) . "</td>\n";
  $html .= "</tr>\n";
}
$html .= "</table>\n";

Nothing spectacular – foreach just loops through the array, and separates each record into the name and the files.  And implode takes the array of files and turns it into a comma-separated list.  Voila, we have our report.

What about duplicate files showing up in the listing?

There is one enhancement we should discuss.  MySQL's GROUP_CONCAT can use DISTINCT as a de-duping feature.  You would need that if your database logged a person downloading the same file multiple times, but you only wanted to show the file once per member.  Turns out we can add array_unique to our code to get the same feature.  So our line using implode can be changed to this:

  $html .= '<td>' . implode(', ', array_unique($files)) . "</td>\n";

That's pretty much it. If you read this far, you've probably cobbled together good working code based upon what I wrote. For the lazy, I've created a text file that contains the full code, plus some extras I do to keep things nice. Thanks for reading.

Navigate

« Shooter, reviewed | Main | Aaaand I'm in the market for jobs! »

Nav by tag: mysql
Main
Nav by tag: php
Main | Greasemonkey: PHP multi-byte string warnings »
Nav by tag: tech
« phpBB RSS Mods | Main | Graphics for Geeks »
Bookmark It! Technorati del.icio.us Netvouz DZone ThisNext Wists blinkbits BlinkList blogmarks blogtercimlap Blue Dot Bumpzee co.mments connotea DotNetKicks Fark feedmelinks Fleck Furl Gwar Haohao Hemidemi IndiaGram IndianPad Internetmedia kick.ie LinkaGoGo Linkter Ma.gnolia MyShare Netscape NewsVine PlugIM PopCurrent ppnow RawSugar Reddit Shadows Simpy Slashdot Smarking Spurl Webride YahooMyWeb
TrackBack

TrackBack URL for this entry:
http://www.outshine.com/cgi-bin/outshine/trackback.cgi/12

Comments (6)
dong quai writes:

I am new to MySQL and have a lot things need to learn. Your post has solved one problem for me. Thank you.


mysql writes:

Hello there, There's no doubt that your web site could possibly be having internet browser compatibility issues. Whenever I take a look at your web site in Safari, it looks fine however, when opening in Internet Explorer, it's
got some overlapping issues. I simply wanted to give you a quick heads
up! Besides that, excellent website!


mysql writes:

Hello there, There's no doubt that your web site could possibly be having internet browser compatibility issues. Whenever I take a look at your web site in Safari, it looks fine however, when opening in Internet Explorer, it's got some overlapping
issues. I simply wanted to give you a quick heads up! Besides that,
excellent website!


eyelash serum writes:

I'm nοt ѕure exactly ωhy but thіs blog іs loading incredibly
slow for mе. ӏs аnyone elѕe haѵing this pгoblem оr is it a problеm on my end?

I'll check bаck later oon and seе iff thе pгoblem still exists.


We're a group of volunteers and starting a new scheme in our
community. Your web site offered us with valuable info to
work on. You've done an impressive job and our whole community will be grateful to you.


Others have spoken about this same subject, but this post was extremely
informative!


Post a comment

Verification (needed to reduce spam):