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