RE: [PHP] Looking for a little MySQL help as well

> -----Original Message----- > From: Jim Lucas [mailto:lists@cmsws.com] > Sent: Monday, July 12, 2010 3:51 PM > To: Phillip Baker > Cc: PHP General List > Subject: Re: [PHP] Looking for a little MySQL help as well > > Jim Lucas wrote: > > Phillip Baker wrote: > >> Thanks Jim, > >> > >> This outputs 2 results. > >> Is there a way to get the one result set per product regardless of > >> the number of categories associated with the product, yet displaying > >> all the categories associated with said prodcut? > > > > Run this: > > > > SELECT > > p.Product_Name, > > GROUP_CONCAT(c.Category_Name SEPARATOR ', ') AS Category_Names > FROM > > p2c_map AS pc > > INNER JOIN > > products AS p ON p.Product_ID = pc.Product_ID INNER JOIN > > categories AS c ON FIND_IN_SET(c.Category_ID, pc.Category_ID) > 0 > > GROUP BY > > pc.Product_ID > > > > When I run this in phpMyAdmin it gives me a blob result, but when you > > run it from the CLI it will return the correct thing. > > Forgot to give create where credit is due. I found an example of this method > here and munged it to make it work with the example table structure and > data examples. > > http://www.tek-tips.com/viewthread.cfm?qid=1541787&page=15 > > And I looked here for usage of MySQLs GROUP_CONCAT() > > http://dev.mysql.com/doc/refman/5.1/en/group-by- > functions.html#function_group-concat > > Enjoy, > > Jim > <snip> Interesting links, Jim. Thanks. Phillip, just an FYI, GROUP_CONCAT() is non ANSI-SQL compliant. If you intend to migrate to another DBMS in the near future, you'll have to find another way to make it work as this built-in function is MySQL only. Regards, Tommy