I programmed an ecommerce site where there are categories containing subcategories containing products containing prices.

My client wants to be able to select a category (or more than one) and/or a subcategory (or more than one) and/or a product (or more than one), then have the prices for all products contained within those groups adjusted by a percentage.

What I have now is three multi selects where he chooses products, categories and subcategories, and when its submitted I end up with three separate arrays with product_ids, category_ids, and subcategory_ids.

How do I go about retrieve the product_ids of all products contained within those three groups (given the fact I have the array I just stated) using the least queries?

Currently what I am doing is taking the array of category_ids and running a foreach loop to retrieve all products contained under those categories. Then I append the product_ids to a new array and repeat the process for subcategories and products. I then process this new array for duplicates.

The problem is, if he has 3 subcategories selected, 5 products selected, and 4 categories, that would require 12 separate SELECT queries. There must be a more efficient way of doing this.

Any ideas?

Let me try and give you a visual for this…

Code:
Umbrellas--> Red Umbrellas----> Large Red Umbrellas	
----------------------------->Small Red Umbrellas	
-----------> Blue Umbrellas---> Large Blue Umbrellas	
-----------------------------> Small Blue Umbrellas	
-----------> Green Umbrellas-> Large Green Umbrellas	
----------------------------->Small Green Umbrellas	
 
Shoes--> Red Shoes----> Large Red Shoes 
------------------------>Small Red Shoes 
--------> Blue Shoes----> Large Blue Shoes 
------------------------> Small Blue Shoes 
--------> Green Shoes--> Large Green Shoes 
------------------------>Small Green Shoes


So if the client selects category "Umbrellas" I need to retrieve all products under that category. If he also chooses the subcategory “Blue Shoes” I need all products under that. If he happens to also pick “Large Blue Shoes” I need the extra inclusion stripped out so there are no duplicates.

Assuming I have a list of category_ids, subcategory_ids and product_ids he has chosen how do I go about getting a final list of all the product_ids meeting the criteria?

The database is set up as you would expect with cross reference tables (I forget what they are called again?) linking the tree tables together.

Code:
Products--------->Product_ids
-----------------> Prices
Subcategories----> Subcategory_ids
Categories------->Category_ids
 
Product_Subcategories -> Product_id 
------------------------> Subcategory_id
 
Subcategory_Category--> Subcategory_id
------------------------> Category_id


The end result is to increase the price in the product table by 5%.

Is there anyway to use a single SELECT query to retrieve all products selected without making an outrageous number of queries? Since the end result is an UPDATE, maybe even turn this into a single UPDATE query?

Here is the code for getting the final array of products, and as you can see it requires multiple SELECT queries.

PHP Code:
if (isset($edit_adjustment_products)) {
    foreach (
$edit_adjustment_products as $value) {
     
$edit_adjustment_product_all[]=$value;
    }
}
if (isset(
$edit_adjustment_categories)) {
    foreach (
$edit_adjustment_categories as $value) {
     
$result=mysql_query("SELECT products.product_id FROM products, category_subcategory, subcategory_product WHERE 
category_subcategory.category_id=
$value AND category_subcategory.subcategory_id=subcategory_product.subcategory_id 
AND subcategory_product.product_id=products.product_id"
);
     while(
$row mysql_fetch_array($result)) {
     
$product_id=$row['product_id'];
     
$edit_adjustment_product_all[]=$product_id;
     }
    }
}
if (isset(
$edit_adjustment_subcategories)) {
    foreach (
$edit_adjustment_subcategories as $value) {
     
$result=mysql_query("SELECT products.product_id FROM products, subcategory_product WHERE subcategory_product.subcategory_id=$value 
AND subcategory_product.product_id=products.product_id"
);
     while(
$row mysql_fetch_array($result)) {
     
$product_id=$row['product_id'];
     
$edit_adjustment_product_all[]=$product_id;
     }
    }

Now assuming we have solved the above problem…and I now have this final list of product_ids which is 105 products long, is there any way to update all products that match an id in the array without making 100 queries in a foreach loop?

One more thing…Is there anyway to update a number in a table by a percentage? Can you just say UPDATE products SET product_price=*1.05 FROM products WHERE product_id=$value; Does that work?

Sorry for the long post, but I hope you all understand the question, and thanks in advance for replying,
Nate