homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

[MySQL] GROUP CONCAT or second query?

5+ Year Member

Msg#: 4124347 posted 1:53 pm on Apr 29, 2010 (gmt 0)

I have a table which contains the contents of several item lists. Each row has a field `listid` and a field `itemname` and field `itemamount`. Several items will have the same listid, making them items of the same list.

Now, I want to search in all lists for a set of items and return the matches, beginning with the list that has the highest amount of matches. I do this by doing a "SELECT `listid`, SUM(`itemamount`) hits FROM items WHERE ... GROUP BY `listid` ORDER BY hits DESC". So far, so good.

But now I want to also get the list of hits for every list. The easiest way to do this would be to take the results from the query, and then doing another select which fetches all the data. The other option I found would be to use GROUP_CONCAT in the first query and then manually split the results again in my PHP code to obtain the list of items. So something like this: "SELECT `listid`, SUM(`itemamount`) hits, GROUP_CONCAT(`itemname`) FROM items WHERE ... GROUP BY `listid` ORDER BY hits DESC"

What would be the better solution? Is it more efficient to do it with GROUP_CONCAT and then do some post-parsing to get the actual data back? Or should I rather do it the other way round for clarity?


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved