FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!

Mysql GROUP_CONCAT Magic




A very complex query I used stopped working after upgrading to mysql 5 so I had to rewrite it. To simplify it, it had to return a lot of rows, but for each a few types and users too. Like below, but with a bit more rows:

Code:
key1 date1 (userX userY userZ) (typeX typeY)
key2 date2 (userY) (typeX typeZ)

Both the types and the users were in a different table, together with the key of the row they related to.

Now of course the easiest thing would be to query for the rows and then per row query for the users and for the types. But for 100 rows (let alone 1000), that would relate to ~200 queries - not a good idea.

I used to have 3 queries, one for the rows, one for the users and one for the types, sorted the same way and then merged together with php. Quite a hard script and query (unbelievably long), but it worked and wasn't too slow. At least until mysql 5.

But luckily I found this nice mysql command: GROUP_CONCAT. It can output the results of a query inside a query, together with some formatting of the data. Unfortunately the formatting has to be done inside mysql so php functions can't be used, the same as existing templates.

So the query became: (simplified, of course)

Code:
$sql = "
SELECT row.*, GROUP_CONCAT(DISTINCT '<b>', users.username, '</b>' SEPARATOR '<br>') AS users
FROM row
LEFT JOIN users ON users.id = row.id
WHERE <where statements>
GROUP BY id
";

Of course with also the types, but the same way as the users. I had to add the DISTINCT in the GROUP_CONCAT, because otherwise the users or types were repeated if there were more types or users. It outputs the rows, and in the 'users', a list of bold usernames, each on a separate line (due to the br between them).

There was only one problem left. The GROUP_CONCAT has a very low size limit and cuts the result there, resulting in broken html. The default maximum length is 1024. I used a query to enlarge this (8 times). Of course this might need to be bigger if you have a lot of output.

Code:
$sql = "
SET SESSION group_concat_max_len = 8192
";


And now everything is retrieved in 1 query. It's still pretty long, but way less complex and easy to understand/change (unlike before). And it seems fast. For over thousand rows, the page is loaded in 0.278 seconds, which is good for so much output. Very Happy



30 blog comments below

Was old query used on the memberlist per chance?
Manofgames on Mon Sep 24, 2007 6:48 pm
I'm guessing this for a staff feature which I've recently noticed was working properly again Very Happy
mathiaus on Mon Sep 24, 2007 7:04 pm
@Manofgames, no the memberlist is almost completely standard phpbb code. I removed it due to the link taking space and because it can be used for bad purposes (pm spamming, ...). Besides, the actual page is still working, only the link is currently gone. I'm thinking of making it available again, but not to all members (like more than 50 posts needed). I might also change the page a bit. It's just not a high priority.

@mathiaus, yes it's for the page with all the AWITS. The action page was also repaired, the FROM fields needed to be between parenthesis in order for the query to work (otherwise the LEFT JOIN caused a problem).
Bondings on Mon Sep 24, 2007 7:14 pm
Quote:
I'm thinking of making it available again, but not to all members (like more than 50 posts needed).


Thats a good idea. We need a quote button for the blogs Wink
garionw on Tue Sep 25, 2007 9:55 am
Cool, but how do we put it on our site?
AK47BLAZE on Tue Jul 15, 2008 6:25 am
ya guys im having the same issue, how would i put this on my site????????????????????????????
knight30303 on Fri Jul 18, 2008 10:08 pm
Give this type of infomation in regular inerval
keep it up
very helpful
EshaAngel on Thu Aug 21, 2008 12:13 pm
hi..can anyone tell me what configurations we need to do in php so that it can be connected to mysql for exeuting queries.i only want to know the initial setups that we need to do Rolling Eyes
mr_accounts on Sun Apr 12, 2009 4:39 am
google ... Wink
jealous494 on Tue Apr 21, 2009 9:47 am
Not sure what else to tell you.
eddiereynolds on Tue May 19, 2009 11:33 am
hahaha! cool
clutchdude on Wed May 20, 2009 6:05 pm
can nyone point out the difference between My_SQL and sqlplus..yea yea i knw different interfaces and all...but the basic differences in the implementation???
jais on Tue Jun 09, 2009 11:49 am
Was old query used on the memberlist per chance?
blackdog on Thu Jun 11, 2009 7:09 pm
i had a lot of prob. about "the query is too complex" .. that I have to rewrite the new sql statement.
leondevil on Tue Sep 08, 2009 8:24 am
nice work
shadesoul on Sun Oct 04, 2009 10:28 pm
Nice job. I have a lot to learn!!
raulmj on Tue Dec 01, 2009 2:41 am
Good Job
WS001 on Fri Jun 18, 2010 1:14 am
Have you used the old query the list of members per chance?
lebregon on Wed Oct 27, 2010 4:29 am
Thanks for this i have something to post

Quote:
drop table
thisdaddy on Sat Dec 04, 2010 3:33 am
this looks very useful
i think i could use that in my code too
tazone on Fri Jan 07, 2011 7:36 pm
hahaha soo cool ... Good Gann Wink Wink
hazmiee on Thu Jan 13, 2011 4:02 pm
Great!! I think this is a goood hosting company!!! Razz Laughing
derienp on Sat Jul 09, 2011 12:42 am
Thanks... great helped
siddharthsnghl on Tue May 08, 2012 1:59 pm
Thanks for this post ! But i want to have one hosting free now..so sr i reply may be don't relationship with this topic ^^
backtrack on Mon Jun 04, 2012 6:35 pm
Well we know who to call when we need DB assistance lol
dinner2go on Sat Sep 15, 2012 9:53 pm
how we use this code in our website.
inder1 on Mon Jan 21, 2013 6:48 pm
I also want to know that
monalisha on Tue Feb 12, 2013 5:46 pm
Anyone who can help me to write an example use shell linux to dump all databases? Thanks
loivu on Sun Mar 10, 2013 5:06 pm
how can i put this on my site?
omidz on Mon Mar 17, 2014 6:52 pm
this is great
dannydjjj on Fri Jan 16, 2015 8:57 am



FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.