Welcome to WebmasterWorld Guest from 18.207.136.184

Forum Moderators: open

Message Too Old, No Replies

Help / Easier way to do this?

Help / Easier way to do this?

     
9:28 am on Sep 5, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 3, 2003
posts:48
votes: 0


Hi guys,

For the sake of simplicity, lets say I have three tables:


-------
Product
-------
id
name
price

--------------
ProductOption
--------------
id
name

------------------
ProductOptionValue
-------------------
id
productOptionId
productId
value

The productOptionValue table references both the ProductOption table (via productOptionId) and the Product table (via productId).

Now, when I render this to a page, I do something like this:

(note: this is pseudo HTML mark-up!)


<!-- Product Details -->
Options:
<select name="color"> <select name="size"> [Go]

Where the two selects allow users to select their prefernces before hitting Go.

What I need to then do is select all productId's that match these two parameters (color and size). The color and size are stored in the "name" field of the productOption table and the values are in the productOptionValue table. The reason I have done it this way (custom fields) is so that they can be arbitrary (e.g color/size/weight/finish/whatever).

I may also have an arbitrary amount of "custom" fields. In this example, I'm just using two (color and size).

The problem is, when I do a query like this:


SELECT productId FROM ProductOptionValue WHERE value = "Blue" AND value = "Large"

its obviously not going to work. I could join 1 or more of the "productOptionValue" tables together - but I think there is a better way out there and I'm just too tired to realise!

I appreciate your help!

1:04 pm on Sept 5, 2006 (gmt 0)

New User

10+ Year Member

joined:Feb 2, 2005
posts:34
votes: 0


Hi,

On MySQL it would look something like this:


SELECT Product.id,
Product.name,
Product.price
FROM Product INNER JOIN (
SELECT ProductOptionValue.productId,
MAX(IF(ProductOption.name = "size", ProductOptionValue.value, NULL)) AS Product_Size,
MAX(IF(ProductOption.name = "color", ProductOptionValue.value, NULL)) AS Product_Color
FROM ProductOption INNER JOIN ProductOptionValue ON ProductOption.id = ProductOptionValue.productOptionId
GROUP BY ProductOptionValue.productId
)tmp ON Product.id = tmp.productId
WHERE tmp.Product_Size = "large"
AND tmp.Product_Color = "blue"
1:52 pm on Sept 5, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 27, 2003
posts:1648
votes: 2



SELECT *
FROM ProductOptionValue AS pov, ProductOption AS po, Product AS p
WHERE pov.productOptionId = po.id
AND (pov.value = "Blue"
OR pov.value = "Large")
AND p.productId = p.id

seems to do the trick (untested, obviously)
But I don't think your db scheme is sufficient - how does it link together an item being red with an item being large?
(Caveat: midnight here, I should go to bed. Clients in the morning...)
8:34 am on Sept 6, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 3, 2003
posts:48
votes: 0



But I don't think your db scheme is sufficient - how does it link together an item being red with an item being large?

By using productId

Thanks for your help guys. I'm using mySQL 4.1 so can't do nested selects. I managed to sort it out in the end.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members