grabbing from multiple tables
in [Databases]
|
Prev: php mysql comparing two rows in two columns for username and passwort
Next: How to use PHP to fill in existing pdf document
From: Karl DeSaulniers on 30 Apr 2010 14:49 No, please confuse me. I need to know this stuff. @Peter thanks for that introduction to foreign keys. Since my productoptions table is based off of items in products, optionGroups and options, would I use foreign keys for this? @DZvonko Thanks for trying to protect me, but I am a big boy. :) Do you have an example of how this JOIN works? Since my productoptions table is based off of items in products, optionGroups and options, would I use JOIN for this? And how? I am looking for the quickest and easiest obviously, but not against learning the longer and harder. I just really wanted to know how to use the foreign key in a real situation. If JOIN is a more viable solution, I'm all ears. Any examples or tutorials someone can send me? Thanks, Karl On Apr 30, 2010, at 6:41 AM, DZvonko Nikolov wrote: > Hi, > > don't confuse the guy. Just use JOIN clause and you will be fine. > Check for the right syntax and don't complicate more. He said he > is quite new, so discuss about foreign keys will only confuse him. > > Use JOIN and pure SQL and you will be fine. > > Cheers > DZvonko > > --- On Fri, 4/30/10, Peter Lind <peter.e.lind(a)gmail.com> wrote: > > From: Peter Lind <peter.e.lind(a)gmail.com> > Subject: Re: [PHP-DB] grabbing from multiple tables > To: "Karl DeSaulniers" <karl(a)designdrumm.com> > Cc: "php-db(a)lists.php.net" <php-db(a)lists.php.net> > Date: Friday, April 30, 2010, 12:31 PM > > On 30 April 2010 12:26, Karl DeSaulniers <karl(a)designdrumm.com> wrote: > > Thanks Peter. > > So what is the logic behind foreign keys? Why use them? > > Constraints. When using, for example, the InnoDB engine in MySQL, you > can set foreign key fields on tables. These ensure that your record > will always be bound to a proper record in the connected table - so, > for instance, you won't find yourself in the situation that you have > deleted a record from table1 but table2 still references the table1 > record. Also, they're very useful for tying models together > automatically, as you can deduce relationships between models by > foreign keys, for instance (this is simplified but covers a lot of > cases). > > Regards > Peter > > -- > <hype> > WWW: http://plphp.dk / http://plind.dk > LinkedIn: http://www.linkedin.com/in/plind > Flickr: http://www.flickr.com/photos/fake51 > BeWelcome: Fake51 > Couchsurfing: Fake51 > </hype> > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Karl DeSaulniers Design Drumm http://designdrumm.com
From: Karl DeSaulniers on 30 Apr 2010 16:27 @David, Thanks for your reply. The idea was based off the WebAssist E-Commerce Database file they gave out a while ago. So, I did not set up the database file necessarily, just adopted and modified. This is how they had it set up. Not that I have to keep that structure necessarily, but I did design around it. This is my first complete back-end project. Please excuse my noobness. :) Basically, I have a product that gets read from the product table and from that I want it to read the product options table to retrieve the options for that product. The options and option groups table are set up so that I can modify options that span across multiple products without going into each product. The values inside the product options table will be based on values from the options and option groups tables. So this is where my head is not grasping. How to link the info. I already have it all set up this way and would like to see it work. Later down the road, I may streamline everything, but I am in a situation where I need to get something up, so reinventing the wheel is not what I want to do. I hope I am not doing that. :) Thanks for your help. Karl On Apr 30, 2010, at 2:59 PM, David Murphy wrote: > > > -----Original Message----- > From: Karl DeSaulniers [mailto:karl(a)designdrumm.com] > Sent: Friday, April 30, 2010 1:50 PM > To: php-db(a)lists.php.net > Subject: Re: [PHP-DB] grabbing from multiple tables > > No, please confuse me. I need to know this stuff. > > @Peter thanks for that introduction to foreign keys. Since my > productoptions table is based off of items in products, optionGroups > and options, would I use foreign keys for this? > > @DZvonko Thanks for trying to protect me, but I am a big boy. :) Do > you have an example of how this JOIN works? Since my productoptions > table is based off of items in products, optionGroups and options, > would I use JOIN for this? And how? > > I am looking for the quickest and easiest obviously, but not against > learning the longer and harder. > I just really wanted to know how to use the foreign key in a real > situation. > If JOIN is a more viable solution, I'm all ears. > > Any examples or tutorials someone can send me? > > Thanks, > > Karl > > > On Apr 30, 2010, at 6:41 AM, DZvonko Nikolov wrote: > >> Hi, >> >> don't confuse the guy. Just use JOIN clause and you will be fine. >> Check for the right syntax and don't complicate more. He said he >> is quite new, so discuss about foreign keys will only confuse him. >> >> Use JOIN and pure SQL and you will be fine. >> >> Cheers >> DZvonko >> >> --- On Fri, 4/30/10, Peter Lind <peter.e.lind(a)gmail.com> wrote: >> >> From: Peter Lind <peter.e.lind(a)gmail.com> >> Subject: Re: [PHP-DB] grabbing from multiple tables >> To: "Karl DeSaulniers" <karl(a)designdrumm.com> >> Cc: "php-db(a)lists.php.net" <php-db(a)lists.php.net> >> Date: Friday, April 30, 2010, 12:31 PM >> >> On 30 April 2010 12:26, Karl DeSaulniers <karl(a)designdrumm.com> >> wrote: >>> Thanks Peter. >>> So what is the logic behind foreign keys? Why use them? >> >> Constraints. When using, for example, the InnoDB engine in MySQL, you >> can set foreign key fields on tables. These ensure that your record >> will always be bound to a proper record in the connected table - so, >> for instance, you won't find yourself in the situation that you have >> deleted a record from table1 but table2 still references the table1 >> record. Also, they're very useful for tying models together >> automatically, as you can deduce relationships between models by >> foreign keys, for instance (this is simplified but covers a lot of >> cases). >> >> Regards >> Peter >> >> -- >> <hype> >> WWW: http://plphp.dk / http://plind.dk >> LinkedIn: http://www.linkedin.com/in/plind >> Flickr: http://www.flickr.com/photos/fake51 >> BeWelcome: Fake51 >> Couchsurfing: Fake51 >> </hype> >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > > Karl DeSaulniers > Design Drumm > http://designdrumm.com > > > Karl I am finding it hard to grasp why you are doing things this > way why > not have > > > A products table with enums for size/color? Then on the edit > page, you > can read the product tables schema to get the enum options and > explode > them in PHP... > > > > Aka > > $tData=$db->QuerryToArray("desc products"); > $tSizes = explode(",",$tData['Sizes']); > $tColors = explode(",",$tData['Colors']); > $objSmarty->assign("AvailableColors",$tColors); > $objSmarty->assign("AvailableSizes",$tSizes); > .... > > Then in the post system... > > $ProductSettings=$db-QueryToArray("select * from products where > id='{$this->CurrentProductID}'"); > $NewProductSettings=$this->GetProductEditInputs(); > > Foreach ($NewProductSettings as $Setting=>$Value) > If($ProdcutSettings[$Setting] !== $Value) > $tUpdates[$Setting]=$Value; > Return > ($db->UpdateFromArray("products",$tUpdates,"id='{$this- > >CurrentProductID}'") > )? TRUE : FALSE; > > Since the table would be using an ENUM or SET the column size is > very > small but also very granular. > > > I think breaking the tables apart is actually more complicated than > its > worth for your needs. > Karl DeSaulniers Design Drumm http://designdrumm.com
From: Karl DeSaulniers on 30 Apr 2010 16:54 To elaborate a little more, In essence, the options and the option groups will be added to the database by an admin, the products will be added by admins and employees alike. When an employee goes to add a product, I want them to be able to choose from a dropdown or a table with names and radio buttons or checkboxes to select the options for that product. Selecting the option will corolate their option groups when selected. The productID, optionID and optionGroupID will be stored in the productoptions table when the product gets submitted. That simple. Or I think that simple.. :) Karl On Apr 30, 2010, at 2:59 PM, David Murphy wrote: > > > -----Original Message----- > From: Karl DeSaulniers [mailto:karl(a)designdrumm.com] > Sent: Friday, April 30, 2010 1:50 PM > To: php-db(a)lists.php.net > Subject: Re: [PHP-DB] grabbing from multiple tables > > No, please confuse me. I need to know this stuff. > > @Peter thanks for that introduction to foreign keys. Since my > productoptions table is based off of items in products, optionGroups > and options, would I use foreign keys for this? > > @DZvonko Thanks for trying to protect me, but I am a big boy. :) Do > you have an example of how this JOIN works? Since my productoptions > table is based off of items in products, optionGroups and options, > would I use JOIN for this? And how? > > I am looking for the quickest and easiest obviously, but not against > learning the longer and harder. > I just really wanted to know how to use the foreign key in a real > situation. > If JOIN is a more viable solution, I'm all ears. > > Any examples or tutorials someone can send me? > > Thanks, > > Karl > > > On Apr 30, 2010, at 6:41 AM, DZvonko Nikolov wrote: > >> Hi, >> >> don't confuse the guy. Just use JOIN clause and you will be fine. >> Check for the right syntax and don't complicate more. He said he >> is quite new, so discuss about foreign keys will only confuse him. >> >> Use JOIN and pure SQL and you will be fine. >> >> Cheers >> DZvonko >> >> --- On Fri, 4/30/10, Peter Lind <peter.e.lind(a)gmail.com> wrote: >> >> From: Peter Lind <peter.e.lind(a)gmail.com> >> Subject: Re: [PHP-DB] grabbing from multiple tables >> To: "Karl DeSaulniers" <karl(a)designdrumm.com> >> Cc: "php-db(a)lists.php.net" <php-db(a)lists.php.net> >> Date: Friday, April 30, 2010, 12:31 PM >> >> On 30 April 2010 12:26, Karl DeSaulniers <karl(a)designdrumm.com> >> wrote: >>> Thanks Peter. >>> So what is the logic behind foreign keys? Why use them? >> >> Constraints. When using, for example, the InnoDB engine in MySQL, you >> can set foreign key fields on tables. These ensure that your record >> will always be bound to a proper record in the connected table - so, >> for instance, you won't find yourself in the situation that you have >> deleted a record from table1 but table2 still references the table1 >> record. Also, they're very useful for tying models together >> automatically, as you can deduce relationships between models by >> foreign keys, for instance (this is simplified but covers a lot of >> cases). >> >> Regards >> Peter >> >> -- >> <hype> >> WWW: http://plphp.dk / http://plind.dk >> LinkedIn: http://www.linkedin.com/in/plind >> Flickr: http://www.flickr.com/photos/fake51 >> BeWelcome: Fake51 >> Couchsurfing: Fake51 >> </hype> >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > > Karl DeSaulniers > Design Drumm > http://designdrumm.com > > > Karl I am finding it hard to grasp why you are doing things this > way why > not have > > > A products table with enums for size/color? Then on the edit > page, you > can read the product tables schema to get the enum options and > explode > them in PHP... > > > > Aka > > $tData=$db->QuerryToArray("desc products"); > $tSizes = explode(",",$tData['Sizes']); > $tColors = explode(",",$tData['Colors']); > $objSmarty->assign("AvailableColors",$tColors); > $objSmarty->assign("AvailableSizes",$tSizes); > .... > > Then in the post system... > > $ProductSettings=$db-QueryToArray("select * from products where > id='{$this->CurrentProductID}'"); > $NewProductSettings=$this->GetProductEditInputs(); > > Foreach ($NewProductSettings as $Setting=>$Value) > If($ProdcutSettings[$Setting] !== $Value) > $tUpdates[$Setting]=$Value; > Return > ($db->UpdateFromArray("products",$tUpdates,"id='{$this- > >CurrentProductID}'") > )? TRUE : FALSE; > > Since the table would be using an ENUM or SET the column size is > very > small but also very granular. > > > I think breaking the tables apart is actually more complicated than > its > worth for your needs. > Karl DeSaulniers Design Drumm http://designdrumm.com
From: Karl DeSaulniers on 1 May 2010 04:13 What is the SQL query I can use to get an item that has two IDs? Something to the effect of... $q = "SELECT OptID = '$OptID' FROM ".PRODUCT_OPTIONS." WHERE ProdID = '$ProdID' AND OptGrpID='$OptGrpID'"; Is this correct or am I missing something? A single group ID can have multiple option IDs set to it. A single product ID can have multiple group IDs set to it. I am trying to single out a product option. The option has a group ID and a product ID assigned to it. TIA Karl On Apr 30, 2010, at 3:54 PM, Karl DeSaulniers wrote: > To elaborate a little more, > > In essence, the options and the option groups will be added to the > database by an admin, > the products will be added by admins and employees alike. > > When an employee goes to add a product, I want them to be able to > choose from a dropdown > or a table with names and radio buttons or checkboxes to select the > options for that product. > Selecting the option will corolate their option groups when selected. > > The productID, optionID and optionGroupID will be stored in the > productoptions table when the product gets submitted. > > That simple. Or I think that simple.. :) > > Karl > > On Apr 30, 2010, at 2:59 PM, David Murphy wrote: > >> >> >> -----Original Message----- >> From: Karl DeSaulniers [mailto:karl(a)designdrumm.com] >> Sent: Friday, April 30, 2010 1:50 PM >> To: php-db(a)lists.php.net >> Subject: Re: [PHP-DB] grabbing from multiple tables >> >> No, please confuse me. I need to know this stuff. >> >> @Peter thanks for that introduction to foreign keys. Since my >> productoptions table is based off of items in products, optionGroups >> and options, would I use foreign keys for this? >> >> @DZvonko Thanks for trying to protect me, but I am a big boy. :) Do >> you have an example of how this JOIN works? Since my productoptions >> table is based off of items in products, optionGroups and options, >> would I use JOIN for this? And how? >> >> I am looking for the quickest and easiest obviously, but not against >> learning the longer and harder. >> I just really wanted to know how to use the foreign key in a real >> situation. >> If JOIN is a more viable solution, I'm all ears. >> >> Any examples or tutorials someone can send me? >> >> Thanks, >> >> Karl >> >> >> On Apr 30, 2010, at 6:41 AM, DZvonko Nikolov wrote: >> >>> Hi, >>> >>> don't confuse the guy. Just use JOIN clause and you will be fine. >>> Check for the right syntax and don't complicate more. He said he >>> is quite new, so discuss about foreign keys will only confuse him. >>> >>> Use JOIN and pure SQL and you will be fine. >>> >>> Cheers >>> DZvonko >>> >>> --- On Fri, 4/30/10, Peter Lind <peter.e.lind(a)gmail.com> wrote: >>> >>> From: Peter Lind <peter.e.lind(a)gmail.com> >>> Subject: Re: [PHP-DB] grabbing from multiple tables >>> To: "Karl DeSaulniers" <karl(a)designdrumm.com> >>> Cc: "php-db(a)lists.php.net" <php-db(a)lists.php.net> >>> Date: Friday, April 30, 2010, 12:31 PM >>> >>> On 30 April 2010 12:26, Karl DeSaulniers <karl(a)designdrumm.com> >>> wrote: >>>> Thanks Peter. >>>> So what is the logic behind foreign keys? Why use them? >>> >>> Constraints. When using, for example, the InnoDB engine in MySQL, >>> you >>> can set foreign key fields on tables. These ensure that your record >>> will always be bound to a proper record in the connected table - so, >>> for instance, you won't find yourself in the situation that you have >>> deleted a record from table1 but table2 still references the table1 >>> record. Also, they're very useful for tying models together >>> automatically, as you can deduce relationships between models by >>> foreign keys, for instance (this is simplified but covers a lot of >>> cases). >>> >>> Regards >>> Peter >>> >>> -- >>> <hype> >>> WWW: http://plphp.dk / http://plind.dk >>> LinkedIn: http://www.linkedin.com/in/plind >>> Flickr: http://www.flickr.com/photos/fake51 >>> BeWelcome: Fake51 >>> Couchsurfing: Fake51 >>> </hype> >>> >>> -- >>> PHP Database Mailing List (http://www.php.net/) >>> To unsubscribe, visit: http://www.php.net/unsub.php >>> >>> >> >> Karl DeSaulniers >> Design Drumm >> http://designdrumm.com >> >> >> Karl I am finding it hard to grasp why you are doing things this >> way why >> not have >> >> >> A products table with enums for size/color? Then on the edit >> page, you >> can read the product tables schema to get the enum options and >> explode >> them in PHP... >> >> >> >> Aka >> >> $tData=$db->QuerryToArray("desc products"); >> $tSizes = explode(",",$tData['Sizes']); >> $tColors = explode(",",$tData['Colors']); >> $objSmarty->assign("AvailableColors",$tColors); >> $objSmarty->assign("AvailableSizes",$tSizes); >> .... >> >> Then in the post system... >> >> $ProductSettings=$db-QueryToArray("select * from products where >> id='{$this->CurrentProductID}'"); >> $NewProductSettings=$this->GetProductEditInputs(); >> >> Foreach ($NewProductSettings as $Setting=>$Value) >> If($ProdcutSettings[$Setting] !== $Value) >> $tUpdates[$Setting]=$Value; >> Return >> ($db->UpdateFromArray("products",$tUpdates,"id='{$this- >> >CurrentProductID}'") >> )? TRUE : FALSE; >> >> Since the table would be using an ENUM or SET the column size is >> very >> small but also very granular. >> >> >> I think breaking the tables apart is actually more complicated >> than its >> worth for your needs. >> > > Karl DeSaulniers > Design Drumm > http://designdrumm.com > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > Karl DeSaulniers Design Drumm http://designdrumm.com
From: Peter Lind on 1 May 2010 04:22
On 30 April 2010 20:49, Karl DeSaulniers <karl(a)designdrumm.com> wrote: > No, please confuse me. I need to know this stuff. > > @Peter thanks for that introduction to foreign keys. Since my productoptions > table is based off of items in products, optionGroups and options, would I > use foreign keys for this? If I read you correct, your productoptions table is basically a lookup table and as such all your fields should be foreign keys. Because, each field is a reference to another table - and it's vital to data consistency that they cannot point to a row in a table that doesn't exist. -- <hype> WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind Flickr: http://www.flickr.com/photos/fake51 BeWelcome: Fake51 Couchsurfing: Fake51 </hype> |