Re: grabbing from multiple tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxx]
Sent: Friday, April 30, 2010 1:50 PM
To: php-db@xxxxxxxxxxxxx
Subject: Re:  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@xxxxxxxxx> wrote:

From: Peter Lind <peter.e.lind@xxxxxxxxx>
Subject: Re:  grabbing from multiple tables
To: "Karl DeSaulniers" <karl@xxxxxxxxxxxxxxx>
Cc: "php-db@xxxxxxxxxxxxx" <php-db@xxxxxxxxxxxxx>
Date: Friday, April 30, 2010, 12:31 PM

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


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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux