Posted 04 April 2006 - 11:52 PM
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.
Posted 05 April 2006 - 07:04 AM
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.....
Posted 05 April 2006 - 12:41 PM
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.
Posted 05 April 2006 - 04:53 PM
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
Posted 05 April 2006 - 04:57 PM
WHERE product_id IN (3,7,14,18)is identical to this:
WHERE product_id=3 OR product_id=7 OR product_id=14 OR product_id=18
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users