Prev: help with sql statement
Next: [PHP] Validate if the field of a form is empty
From: "Tommy Pham" on 12 Jul 2010 17:07
From: Phillip Baker [mailto:phillip(a)freewolf.net]
Sent: Monday, July 12, 2010 1:33 PM
To: Tommy Pham
Cc: PHP General List
Subject: Re: [PHP] Looking for a little MySQL help as well

Thanks Tommy,

Damn.
I was hoping to avoid that and get all the information in one query rather
than running a ton of queries. :-(
But might be why I am having trouble finding an answer for this on the net.

Blessed Be

Phillip



Phillip,

I think you misunderstood. Your query is still 1 query. You just loop
through the results and compare certain field for changes using 'if' to get
the desired effect for your sample tables:

Table 1
Product_id | Product_Name
1 | Product A
2 | Product B
3 | Product C

Table 2
Category_id | Category_Name
1 | Admin
2 | Marketing
3 | Support
4 | IT


Table 3
Product_id | Category_id
1 | 1
1 | 3
2 | 2
3 | 3
3 | 4

Result would look like
Product A, Admin, Support
Product B, Marketing
Product C, Support, IT



Here's the pseudo code:

$last_product_id = null;
$current_product_id = null;
Loop $result_set if not end of $result_set
$current_product_id = get("Product_id") from $result_set;
If ($last_product_id != $current_product_id)
{
$last_product_id = $current_product_id;
$product_name = get("Product_Name") from $result_set;
$category_name = get("Category_Name") from $result_set;
}
Else
{
$category_name .= ', '. get("Category_Name") from $result_set;
}

// do as you will with $product_name & $category_name

Next loop iteration

IMO, this is way faster to implement than trying to figure out all that SP
stuff since we're not DBAs ;) . As for overall performance, I don't know if
this is faster than SP. You'll just have to analyze your data size and what
kind of traffic you're getting vs. hardware(s) you have to see if it's worth
the time to learn how to write SP. Just an FYI, once you get into complex
situations, SP have higher security and generally have better performance
than just standard queries.

Regards,
Tommy

From: Jim Lucas on 12 Jul 2010 18:22
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.

>
> Blessed Be
>
> Phillip
>
> If you try to protect idiots from themselves, even if you succeed, you just
> wind up filling the world with idiots.
> - - Doug Casey
>
>
> On Mon, Jul 12, 2010 at 12:21 PM, Jim Lucas <lists(a)cmsws.com> wrote:
>
>> Phillip Baker wrote:
>>> Hey All,
>>>
>>> I am looking for a little MySQL Query help as well.
>>> I am not getting any response form the MySQl Email list to my query.
>>> And knowing there are some SQL wizards on this list I thought I would as
>> for
>>> help as well.
>>>
>>> I have one table with products that many have one or more categories.
>>> I am using an index table and am having trouble getting a proper result
>> set.
>>> Table 1
>>> Product_id | Product_Name
>>> 1 | Product A
>>> 2 | Product B
>>> 3 | Product C
>>>
>>> Table 2
>>> Category_id | Category_Name
>>> 1 | Admin
>>> 2 | Marketing
>>> 3 | Support
>>> 4 | IT
>>>
>>>
>>> Table 3
>>> Product_id | Category_id
>>> 1 | 1
>>> 1 | 3
>>> 2 | 2
>>> 3 | 3
>>> 3 | 4
>>>
>>> Result would look like
>>> Product A, Admin, Support
>>> Product B, Marketing
>>> Product C, Support, IT
>>>
>>> I believe this is a one to many using an index table?
>>> I appreciate any help.
>>> Thanks.
>>>
>>> Blessed Be
>>>
>>> Phillip
>>>
>>> If you try to protect idiots from themselves, even if you succeed, you
>> just
>>> wind up filling the world with idiots.
>>> - - Doug Casey
>>>
>>
>> SELECT
>> products.Product_Name,
>> categories.Category_Name
>> FROM
>> products,
>> categories,
>> p2c_map
>> WHERE
>> products.Product_ID = p2c_map.Product_ID
>> AND
>> categories.Category_ID = p2c_map.Category_ID
>>
>> Gives the results that you are looking for. Once you get the data, you
>> must
>> concat things your self, but it is everything that you are looking for.
>>
>> To search for Categories of a given product, you would add this to the
>> WHERE section
>>
>> AND
>> products.Product_Name = 'Product A'
>>
>> of, if you were looking for all the products in a given category, you would
>> add this
>>
>> AND
>> categories.Category_Name = 'Category 1'
>>
>>
>> The following is the table structure that I am using with mock data.
>>
>> CREATE TABLE IF NOT EXISTS `categories` (
>> `Category_ID` int(11) NOT NULL auto_increment,
>> `Category_Name` varchar(16) collate latin1_bin NOT NULL,
>> PRIMARY KEY (`Category_ID`)
>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=5
>> ;
>>
>> INSERT INTO `categories` (`Category_ID`, `Category_Name`) VALUES
>> (1, 'Category 1'),(2, 'Category 2'),(3, 'Category 3'),(4, 'Category 4');
>>
>> CREATE TABLE IF NOT EXISTS `p2c_map` (
>> `Product_id` int(11) NOT NULL,
>> `Category_ID` int(11) NOT NULL,
>> PRIMARY KEY (`Product_id`,`Category_ID`)
>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
>>
>> INSERT INTO `p2c_map` (`Product_id`, `Category_ID`) VALUES
>> (1, 1),(1, 2),(1, 4),(2, 2),(2, 3),(3, 4),(4, 1),(4, 4);
>>
>> CREATE TABLE IF NOT EXISTS `products` (
>> `Product_ID` int(11) NOT NULL auto_increment,
>> `Product_Name` varchar(16) collate latin1_bin NOT NULL,
>> PRIMARY KEY (`Product_ID`)
>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=5
>> ;
>>
>> INSERT INTO `products` (`Product_ID`, `Product_Name`) VALUES
>> (1, 'Product A'),(2, 'Product B'),(3, 'Product C'),(4, 'Product D');
>>
>> --
>> Jim Lucas
>>
>> A: Maybe because some people are too annoyed by top-posting.
>> Q: Why do I not get an answer to my question(s)?
>> A: Because it messes up the order in which people normally read text.
>> Q: Why is top-posting such a bad thing?
>>
>


--
Jim Lucas

A: Maybe because some people are too annoyed by top-posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
From: Jim Lucas on 12 Jul 2010 18:50
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

>
>> Blessed Be
>>
>> Phillip
>>
>> If you try to protect idiots from themselves, even if you succeed, you just
>> wind up filling the world with idiots.
>> - - Doug Casey
>>
>>
>> On Mon, Jul 12, 2010 at 12:21 PM, Jim Lucas <lists(a)cmsws.com> wrote:
>>
>>> Phillip Baker wrote:
>>>> Hey All,
>>>>
>>>> I am looking for a little MySQL Query help as well.
>>>> I am not getting any response form the MySQl Email list to my query.
>>>> And knowing there are some SQL wizards on this list I thought I would as
>>> for
>>>> help as well.
>>>>
>>>> I have one table with products that many have one or more categories.
>>>> I am using an index table and am having trouble getting a proper result
>>> set.
>>>> Table 1
>>>> Product_id | Product_Name
>>>> 1 | Product A
>>>> 2 | Product B
>>>> 3 | Product C
>>>>
>>>> Table 2
>>>> Category_id | Category_Name
>>>> 1 | Admin
>>>> 2 | Marketing
>>>> 3 | Support
>>>> 4 | IT
>>>>
>>>>
>>>> Table 3
>>>> Product_id | Category_id
>>>> 1 | 1
>>>> 1 | 3
>>>> 2 | 2
>>>> 3 | 3
>>>> 3 | 4
>>>>
>>>> Result would look like
>>>> Product A, Admin, Support
>>>> Product B, Marketing
>>>> Product C, Support, IT
>>>>
>>>> I believe this is a one to many using an index table?
>>>> I appreciate any help.
>>>> Thanks.
>>>>
>>>> Blessed Be
>>>>
>>>> Phillip
>>>>
>>>> If you try to protect idiots from themselves, even if you succeed, you
>>> just
>>>> wind up filling the world with idiots.
>>>> - - Doug Casey
>>>>
>>> SELECT
>>> products.Product_Name,
>>> categories.Category_Name
>>> FROM
>>> products,
>>> categories,
>>> p2c_map
>>> WHERE
>>> products.Product_ID = p2c_map.Product_ID
>>> AND
>>> categories.Category_ID = p2c_map.Category_ID
>>>
>>> Gives the results that you are looking for. Once you get the data, you
>>> must
>>> concat things your self, but it is everything that you are looking for.
>>>
>>> To search for Categories of a given product, you would add this to the
>>> WHERE section
>>>
>>> AND
>>> products.Product_Name = 'Product A'
>>>
>>> of, if you were looking for all the products in a given category, you would
>>> add this
>>>
>>> AND
>>> categories.Category_Name = 'Category 1'
>>>
>>>
>>> The following is the table structure that I am using with mock data.
>>>
>>> CREATE TABLE IF NOT EXISTS `categories` (
>>> `Category_ID` int(11) NOT NULL auto_increment,
>>> `Category_Name` varchar(16) collate latin1_bin NOT NULL,
>>> PRIMARY KEY (`Category_ID`)
>>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=5
>>> ;
>>>
>>> INSERT INTO `categories` (`Category_ID`, `Category_Name`) VALUES
>>> (1, 'Category 1'),(2, 'Category 2'),(3, 'Category 3'),(4, 'Category 4');
>>>
>>> CREATE TABLE IF NOT EXISTS `p2c_map` (
>>> `Product_id` int(11) NOT NULL,
>>> `Category_ID` int(11) NOT NULL,
>>> PRIMARY KEY (`Product_id`,`Category_ID`)
>>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
>>>
>>> INSERT INTO `p2c_map` (`Product_id`, `Category_ID`) VALUES
>>> (1, 1),(1, 2),(1, 4),(2, 2),(2, 3),(3, 4),(4, 1),(4, 4);
>>>
>>> CREATE TABLE IF NOT EXISTS `products` (
>>> `Product_ID` int(11) NOT NULL auto_increment,
>>> `Product_Name` varchar(16) collate latin1_bin NOT NULL,
>>> PRIMARY KEY (`Product_ID`)
>>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=5
>>> ;
>>>
>>> INSERT INTO `products` (`Product_ID`, `Product_Name`) VALUES
>>> (1, 'Product A'),(2, 'Product B'),(3, 'Product C'),(4, 'Product D');
>>>
>>> --
>>> Jim Lucas
>>>
>>> A: Maybe because some people are too annoyed by top-posting.
>>> Q: Why do I not get an answer to my question(s)?
>>> A: Because it messes up the order in which people normally read text.
>>> Q: Why is top-posting such a bad thing?
>>>
>
>


--
Jim Lucas

A: Maybe because some people are too annoyed by top-posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
From: "Tommy Pham" on 12 Jul 2010 19:20
> -----Original Message-----
> From: Jim Lucas [mailto:lists(a)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

First  |  Prev  | 
Pages: 1 2
Prev: help with sql statement
Next: [PHP] Validate if the field of a form is empty