SQL Query - Grouping [Solved - GROUP_CONCAT]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
christh
Forum Commoner
Posts: 25
Joined: Sat Jan 16, 2010 5:27 am

SQL Query - Grouping [Solved - GROUP_CONCAT]

Post by christh »

Hi

I hope you can give me some guidance on an sql query I want to write - I can do everything else I mention, just not sure where to start with the query.

If I have a table that looks like this

client | product id ------------------>Joined to a product info table
---------------------------
a@a | 12
b@b | 22
a@a | 19
a@a | 08
c@c | 12
b@b | 03

I want to run a query at the end of the day, sending an email giving a summary on subscribed products but I want to send only one email per client i.e.

a@a would receive a mail on products 12,19,08 rather than 3 emails emails. As the tables are joined, storing the info as below wouldn't help much

client | product id ------------------>Joined to a product info table
---------------------------
a@a | 12,19,08

My first thoughts are to use a SELECT DISTINCT on the email address for the first query and then query the table a second time looping those results to send the email, but that would appear to be quite a long way round to achieve the results - same as if I structure as example 2 and explode the product id (certainly given that one day the table may be quite large).

I though GROUP BY would hold the answer but I can only get it to total up the product id and DISTINCT will only return one email address.

Any ideas?

Thanks

Chris
Last edited by christh on Wed Mar 09, 2011 4:07 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SQL Query - Grouping

Post by VladSun »

Try JOINing the product table first:

Code: Select all

SELECT 
    orders.client,
    GROUP_CONCAT(products.name, SEPARATOR '#') as product_names,
    GROUP_CONCAT(products.description, SEPARATOR '#') as product_descriptions,
    GROUP_CONCAT(products.price, SEPARATOR '#') as product_prices
from 
    orders
INNER JOIN 
    products on products.id = orders.product_id
GROUP BY
    orders.client
Then explode by # :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SQL Query - Grouping

Post by VladSun »

Another way to do it is PHP processing.

SQL query:

Code: Select all

SELECT 
    orders.client,
    products.name,
    products.description,
    products.price
FROM 
    orders
INNER JOIN 
    products ON products.id = orders.product_id
ORDER BY
    orders.client
Then you have to iterate through the result set watching for $row['client'] value changes and collecting the product list. On every $row['client'] change you format and send email, reset the product list and continue looping through the DB result.
There are 10 types of people in this world, those who understand binary and those who don't
christh
Forum Commoner
Posts: 25
Joined: Sat Jan 16, 2010 5:27 am

Re: SQL Query - Grouping

Post by christh »

Hey thanks ViadSun

I have never come across GROUP_CONCAT before which is just what I need.

I suppose it must be well documented, but I'm sure I never saw it when reading up on GROUP BY, ORDER BY.

Anyway thanks again

Chris
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SQL Query - Grouping [Solved - GROUP_CONCAT]

Post by Eran »

It's part of the aggregate functions documentation - http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
Post Reply