Looking for a little MySQL help as well
in [General]
|
From: Phillip Baker on 12 Jul 2010 13:53 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
From: Jim Lucas on 12 Jul 2010 14:21 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?
From: Phillip Baker on 12 Jul 2010 14:36 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? 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? >
From: "Tommy Pham" on 12 Jul 2010 15:37 > -----Original Message----- > From: Phillip Baker [mailto:phillip(a)freewolf.net] > Sent: Monday, July 12, 2010 11:36 AM > To: Jim Lucas > Cc: PHP General List > Subject: Re: [PHP] Looking for a little MySQL help as well > > 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? > > Blessed Be > > Phillip > <snip> Phillip, What you're asking for requires the use of (IIRC) 'cursors'. I don't know if MySQL's meaning/usage of 'cursors' is the same as MSSQL. Either way, you'll need to write some serious (read pain in the a**) Stored Procedure (SP). You're better off implementing that in PHP loop containing 'if' since you're not a DBA ;). Regards, Tommy
From: Phillip Baker on 12 Jul 2010 16:32 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 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 1:37 PM, Tommy Pham <tommyhp2(a)gmail.com> wrote: > > -----Original Message----- > > From: Phillip Baker [mailto:phillip(a)freewolf.net] > > Sent: Monday, July 12, 2010 11:36 AM > > To: Jim Lucas > > Cc: PHP General List > > Subject: Re: [PHP] Looking for a little MySQL help as well > > > > 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? > > > > Blessed Be > > > > Phillip > > > <snip> > > Phillip, > > What you're asking for requires the use of (IIRC) 'cursors'. I don't know > if MySQL's meaning/usage of 'cursors' is the same as MSSQL. Either way, > you'll need to write some serious (read pain in the a**) Stored Procedure > (SP). You're better off implementing that in PHP loop containing 'if' > since > you're not a DBA ;). > > Regards, > Tommy > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
|
Next
|
Last
Pages: 1 2 Prev: help with sql statement Next: [PHP] Validate if the field of a form is empty |