any mysql or sql ppl here who can help?

[LuckyTaxi]

Let's say I have the following tables.

tbl_products contain all our products (items we're selling, warranty info and shipping info). Each "item" has a sku and an id number, which is unique).

tbl_pd_price contains the pricing for shipping and warranty info FOR EACH item. The price of the item itself is in tbl_products.

tbl_products
---------------
pd_id | pd_sku | pd_name | pd_status

tbl_pd_price
---------------
pd_id | child_id | child_price

Ok, so let's say "Widget A" is $5.00 and it has two shipping options (Curbside - $0.00 and In-Room - $25.00) and warranty of 1 year ($0.00). The two tables would look like this.

tbl_products
---------------
pd_id | pd_sku | pd_name | pd_status | pd_price
------------------------------------------------------
1 | widget-a | Widget A | available | 5.00
2 | curbside | curbside delivery | available | NULL
3 | inroom | In-Room Delivery | available | NULL
4 | turnkey | Turnkey Delivery | available | NULL
5 | war-100 | Warranty 1 Yr | available | NULL
6 | war-200 | warranty 2 yr | available | NULL

tbl_pd_price
------------------------------
pd_id | child_id | child_price
------------------------------
1 | 2 | 0.00
1 | 3 | 25.00
1 | 5 | 0.00

The issue I'm having is populating the checkboxes on the backend where someone would modify the item in question.

For example for shipping option, we would show all shipping options that is available with checkboxes and input boxes. Depending on what is in tbl_pd_price, it would automatically be checked off with the associated value.

[x] Curbside 0.00
[x] In-Room 25.00
[] Turnkey 0.00

 

[KB]

Do I have this right? You are looking for the SQL statement to list the associated products linked with the main product?
Try:
SELECT pid_id, pd_sku, pd_name, pd_price from tbl_products INNER JOIN tbl_pd_price ON tbl_products.pd_id = tbl_pd_price.child_id
WHERE tbl_pd_price = 1

then in your application logic you would have to generate the html for the checkboxes.

A thought - aren't Curbside and In-home delivery mutually exclusive? Its either delivered in the house or to the curb, not both. You may need to add a field to point this out.

 

[troytime]

Originally posted by: KB
Do I have this right? You are looking for the SQL statement to list the associated products linked with the main product?
Try:
SELECT pid_id, pd_sku, pd_name, pd_price from tbl_products INNER JOIN tbl_pd_price ON tbl_products.pd_id = tbl_pd_price.child_id
WHERE tbl_pd_price = 1

then in your application logic you would have to generate the html for the checkboxes.

A thought - aren't Curbside and In-home delivery mutually exclusive? Its either delivered in the house or to the curb, not both. You may need to add a field to point this out.

WHERE tbl_pd_price = 1

i'm no guru, at sql, but that looks odd
will that function?

 

[KB]

Originally posted by: troytime

Originally posted by: KB
Do I have this right? You are looking for the SQL statement to list the associated products linked with the main product?
Try:
SELECT pid_id, pd_sku, pd_name, pd_price from tbl_products INNER JOIN tbl_pd_price ON tbl_products.pd_id = tbl_pd_price.child_id
WHERE tbl_pd_price = 1

then in your application logic you would have to generate the html for the checkboxes.

A thought - aren't Curbside and In-home delivery mutually exclusive? Its either delivered in the house or to the curb, not both. You may need to add a field to point this out.

WHERE tbl_pd_price = 1

i'm no guru, at sql, but that looks odd
will that function?

No it would not...

WHERE tbl_pd_price.pd_id = 1