grabbing from multiple tables

--Apple-Mail-4-109313082
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

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


--Apple-Mail-4-109313082--

Re: grabbing from multiple tables

On 30 April 2010 12:02, Karl DeSaulniers 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


--

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

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 wrote:

> On 30 April 2010 12:02, Karl DeSaulniers 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
>
>
> --
>
> 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
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

On 30 April 2010 12:26, Karl DeSaulniers 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

--

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

On 30 April 2010 13:41, DZvonko Nikolov 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.


--

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

--Apple-Mail-1-140916209
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

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 wrote:
>
> From: Peter Lind
> Subject: Re: [PHP-DB] grabbing from multiple tables
> To: "Karl DeSaulniers"
> Cc: "php-db@lists.php.net"
> Date: Friday, April 30, 2010, 12:31 PM
>
> On 30 April 2010 12:26, Karl DeSaulniers 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
>
> --
>
> 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
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--Apple-Mail-1-140916209--

Re: grabbing from multiple tables

@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@designdrumm.com]
> Sent: Friday, April 30, 2010 1:50 PM
> To: php-db@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 wrote:
>>
>> From: Peter Lind
>> Subject: Re: [PHP-DB] grabbing from multiple tables
>> To: "Karl DeSaulniers"
>> Cc: "php-db@lists.php.net"
>> Date: Friday, April 30, 2010, 12:31 PM
>>
>> On 30 April 2010 12:26, Karl DeSaulniers
>> 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
>>
>> --
>>
>> 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
>>
>>
>> --
>> 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

Re: grabbing from multiple tables

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@designdrumm.com]
> Sent: Friday, April 30, 2010 1:50 PM
> To: php-db@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 wrote:
>>
>> From: Peter Lind
>> Subject: Re: [PHP-DB] grabbing from multiple tables
>> To: "Karl DeSaulniers"
>> Cc: "php-db@lists.php.net"
>> Date: Friday, April 30, 2010, 12:31 PM
>>
>> On 30 April 2010 12:26, Karl DeSaulniers
>> 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
>>
>> --
>>
>> 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
>>
>>
>> --
>> 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

Re: grabbing from multiple tables

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@designdrumm.com]
>> Sent: Friday, April 30, 2010 1:50 PM
>> To: php-db@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 wrote:
>>>
>>> From: Peter Lind
>>> Subject: Re: [PHP-DB] grabbing from multiple tables
>>> To: "Karl DeSaulniers"
>>> Cc: "php-db@lists.php.net"
>>> Date: Friday, April 30, 2010, 12:31 PM
>>>
>>> On 30 April 2010 12:26, Karl DeSaulniers
>>> 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
>>>
>>> --
>>>
>>> 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
>>>
>>>
>>> --
>>> 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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

On 30 April 2010 20:49, Karl DeSaulniers 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.

--

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

On 1 May 2010 10:13, Karl DeSaulniers 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

--

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

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 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.
>
> --
>
> 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
>

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

On 1 May 2010 11:00, Karl DeSaulniers 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

--

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

GOLD!
Thank you.

Karl


On May 1, 2010, at 4:12 AM, Peter Lind wrote:

> On 1 May 2010 11:00, Karl DeSaulniers 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
>
> --
>
> 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
>

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: grabbing from multiple tables

@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



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php