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 06:02 Hello All, I have a product database. In that database there are several tables dealing with individual products. I am wanting to set up an editProduct and a productInfo page. For the bulk info, I am fine. For the product options, I need some guidance. I have 4 tables dealing with the options. Product table = main product table where productID is set Product Options table = table to store product option info. Where productID, optionID and OptionGroupID are stored together per ProductID. Option Groups table = general option groups EG: Size, Color. Where OptionGroupID and OptionGroupName is set. Options table = general options EG: Large, Medium, Small, Red, Blue, etc. Where OptionID and OptionName is set per OptionGroupID. Is there a way for me to call all these tables when adding a product? edit product? view product? I am new to MySQL queries, so I am not sure if I need a special query string to access them or if I need to set foreign keys and just call on one table, because for e.g., the product options table, the values are based off of values from other tables. What would be the best way about accessing these tables and their info in a streamline manner. Short-hand if you will, if that is possible. TIA, Best, Karl DeSaulniers Design Drumm http://designdrumm.com
From: Peter Lind on 30 Apr 2010 06:09 On 30 April 2010 12:02, Karl DeSaulniers <karl(a)designdrumm.com> wrote: > Hello All, > I have a product database. In that database there are several tables dealing > with individual products. > I am wanting to set up an editProduct and a productInfo page. For the bulk > info, I am fine. > For the product options, I need some guidance. > > I have 4 tables dealing with the options. > > Product table = main product table where productID is set > Product Options table = table to store product option info. Where productID, > optionID and OptionGroupID are stored together per ProductID. > > Option Groups table = general option groups EG: Size, Color. Where > OptionGroupID and OptionGroupName is set. > Options table = general options EG: Large, Medium, Small, Red, Blue, etc. > Where OptionID and OptionName is set per OptionGroupID. > > > Is there a way for me to call all these tables when adding a product? edit > product? view product? MySQL will only allow you to insert into one table at a time. You can however update several tables at the same time - see http://dev.mysql.com/doc/refman/5.0/en/update.html You can select values form multiple tables with the SELECT syntax: SELECT table1.blah, table2.blah FROM table1, table2 WHERE table1.id = table2.foreign_key; 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>
From: Karl DeSaulniers on 30 Apr 2010 06:26 Thanks Peter. So what is the logic behind foreign keys? Why use them? Thx. Karl Sent from losPhone On Apr 30, 2010, at 5:09 AM, Peter Lind <peter.e.lind(a)gmail.com> wrote: > On 30 April 2010 12:02, Karl DeSaulniers <karl(a)designdrumm.com> wrote: >> Hello All, >> I have a product database. In that database there are several >> tables dealing >> with individual products. >> I am wanting to set up an editProduct and a productInfo page. For >> the bulk >> info, I am fine. >> For the product options, I need some guidance. >> >> I have 4 tables dealing with the options. >> >> Product table = main product table where productID is set >> Product Options table = table to store product option info. Where >> productID, >> optionID and OptionGroupID are stored together per ProductID. >> >> Option Groups table = general option groups EG: Size, Color. Where >> OptionGroupID and OptionGroupName is set. >> Options table = general options EG: Large, Medium, Small, Red, >> Blue, etc. >> Where OptionID and OptionName is set per OptionGroupID. >> >> >> Is there a way for me to call all these tables when adding a >> product? edit >> product? view product? > > MySQL will only allow you to insert into one table at a time. You can > however update several tables at the same time - see > http://dev.mysql.com/doc/refman/5.0/en/update.html > > You can select values form multiple tables with the SELECT syntax: > SELECT table1.blah, table2.blah FROM table1, table2 WHERE table1.id = > table2.foreign_key; > > 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 >
From: Peter Lind on 30 Apr 2010 06:31 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>
From: Peter Lind on 30 Apr 2010 07:52
On 30 April 2010 13:41, DZvonko Nikolov <dzvonko(a)yahoo.com> wrote: > > Hi, > > don't confuse the guy. Don't talk down to the man. He asked questions and got usable answers including links to where he could find more info. -- <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> |