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: Peter Lind on 1 May 2010 04:59 On 1 May 2010 10:13, Karl DeSaulniers <karl(a)designdrumm.com> wrote: > 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'"; Close but no cigar. $q = "SELECT OptID FROM ".PRODUCT_OPTIONS." WHERE ProdID = '$ProdID' AND OptGrpID='$OptGrpID'"; I am assuming that you have escaped $ProdID and $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. You're looking at a many-to-many table - a row should be unique given all three IDs. Only those three IDs together should form a unique row. > I am trying to single out a product option. > The option has a group ID and a product ID assigned to it. > I'm starting to wonder about your data model. Should options always be in groups? I'm guessing that you need one of three things: 1. Split up the product options table. A product can have some individual options and some group options - these have nothing to do with each other and you need to be able to set them without regard for each other. 2. Remove the productOptionID from the product options table. A product only has option groups, no individual options. 3. Remove the groupOptionID from the product options table. A product only has individual options, regardless of the option group these options belong to. 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 1 May 2010 05:00 Thank you Peter... again.. :) Which would be the source field and which the target field? Karl On May 1, 2010, at 3:22 AM, Peter Lind wrote: > 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> Karl DeSaulniers Design Drumm http://designdrumm.com
From: Peter Lind on 1 May 2010 05:12 On 1 May 2010 11:00, Karl DeSaulniers <karl(a)designdrumm.com> wrote: > Thank you Peter... again.. :) > Which would be the source field and which the target field? You place the constraint on the table that has the foreign key. Using your example: Table 1 = products * id = primary key Table 2 = productoptions * product_id = foreign key On table 2 you would place a constraint like this (in SQL): CONSTRAINT FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE This will delete any rows in table 2 if a matching row in table 1 is deleted (i.e. if you delete a product, all rows in table 2 matching options to products will be deleted as well, if they reference the deleted product). If instead you want to make sure that no product can be deleted while it still has options attached, use this: CONSTRAINT FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE NO ACTION I don't really know how this translates into your "source" and "target" but I would guess source means the primary key on table 1 and target means the foreign key on table 2 ... though, thinking about it, it would make at least as much sense the other way round. Best see if you can find some documentation to translate those terms into something SQL-centric 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 1 May 2010 05:16 GOLD! Thank you. Karl On May 1, 2010, at 4:12 AM, Peter Lind wrote: > On 1 May 2010 11:00, Karl DeSaulniers <karl(a)designdrumm.com> wrote: >> Thank you Peter... again.. :) >> Which would be the source field and which the target field? > > You place the constraint on the table that has the foreign key. Using > your example: > > Table 1 = products > * id = primary key > > Table 2 = productoptions > * product_id = foreign key > > On table 2 you would place a constraint like this (in SQL): > CONSTRAINT FOREIGN KEY (product_id) REFERENCES products (id) ON > DELETE CASCADE > > This will delete any rows in table 2 if a matching row in table 1 is > deleted (i.e. if you delete a product, all rows in table 2 matching > options to products will be deleted as well, if they reference the > deleted product). > > If instead you want to make sure that no product can be deleted while > it still has options attached, use this: > > CONSTRAINT FOREIGN KEY (product_id) REFERENCES products (id) ON > DELETE NO ACTION > > I don't really know how this translates into your "source" and > "target" but I would guess source means the primary key on table 1 and > target means the foreign key on table 2 ... though, thinking about it, > it would make at least as much sense the other way round. Best see if > you can find some documentation to translate those terms into > something SQL-centric > > 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> Karl DeSaulniers Design Drumm http://designdrumm.com
From: Karl DeSaulniers on 1 May 2010 05:46
@Peter To be honest, this is my first one. So the margin for error is probably there, I just cant see it. BUT, I did get this database file pre-made from WebAssist. Its their E-Commerce database file they gave out. (Sorry if I am repeating myself) So the structure was set up by another and I am adopting it so I can learn how this is done. I was wondering the same as you when I saw the options and options group tables. I figured there was a structure there I could not understand at the time, but continued on knowing I would come across the answer somehow. You are probably right in the notion that the multiple tables are not needed, but I am wondering then why they were included in the template if they are not. Again, an answer I knew I would come across sooner or later. Just looks like later then sooner. :) I do appreciate the help, it has helped me narrow a few things down. I'll let you know if I am successful. Best, On May 1, 2010, at 3:59 AM, Peter Lind wrote: > I'm starting to wonder about your data model. Karl DeSaulniers Design Drumm http://designdrumm.com |