Stupid Problem

[Javizy]

I'm making a little shop site and I'm having a hard time adding product options, like size.

I added a ProductOption table which is related to Product, and has fields id, product_id, option_name, price.

Design wise it seems logical to me, but retrieving and converting the data into a php object isn't so straightforward.

Basically I have a getProducts function that creates an array of Product objects. The Product object has an array of Options as an instance variable.

I can't think of an efficient way of querying the data. I need the Product table fields, along with all ProductOptions associated with that product_id (may be 1 or more).

Has anybody implemented a cart with product options before? If this makes sense to anyone I'd much appreciate some insight, because I'm totally missing something here.

[fenway]

This is a logical choice assuming your products have varied and multiple product options, which is often the case. Querying does present a challenge, because you need to match multiple rows to find multiple product options.

I'd bet that wickning1 would suggest using multiple self-joins, so that you're back to a one row-one product option scenario. This of course works exceptionally well (as most of wickning1 suggestions do), but can be difficult to maintain in code if you can have lots of options because of the sheer number of joins required.

What I have often done in the past for code simplicity is simply to query the options table with a IN clause, add a COUNT(), GROUP BY product_uid, and then add a HAVING cnt = <# of options>. It may not be the most efficient, and I doubt it's optimized, but I've never had to use anything else thus far.

Just my $0.02.....

[wickning1]

I think you're ok with two queries here. One to get the product details for all the products you want, another to get all the options for all those products. Then in PHP you go row by row and link them up. Just make sure you ORDER BY product_id and it shouldn't be too hard.

The alternative is to use a nested loop, get one product, then query for its options. This will generate a lot of queries, which could eventually slow you down. But it should be easy to write.

[Javizy]

Thanks for the replies guys. My first idea was to get a list of product_ids and then query each one for the options but obviously that amount of queries is ridiculous.

I think I'll go with wickning's suggestion of getting a list of product_ids and then getting the options in one query with something like WHERE product_id = xx OR product_id = xx, etc.

I dunno why I didn't think of that, I thought it might be possible with one query (probably would be with an ORDB).

Thanks again for the help, after I've done this the shop section is pretty much finished :-D

[wickning1]

You can use IN instead of a lot of ORs. For instance, this:

[code]WHERE product_id IN (3,7,14,18)[/code]
is identical to this:

[code]WHERE product_id=3 OR product_id=7 OR product_id=14 OR product_id=18[/code]

[Javizy]

That's a lot better, thanks.