Please help...very complicated query.

[jumpthru]

I have 100 produts in 25 subcategories in 10 categories. They are connected via "connecter tables"...anyone know the real name of those?? For example...subcategory_product has too colums linking subcategory IDS with product IDS. This way products can be in multiple subcategories, and subcategories can be in multiple cateogries.

My goal is to select any number of category and/or subcateogires and/or products and them update all the products that fall under the cateogories selected and the subcategories selected or are explicitly selected.

So far I have...

 SELECT DISTINCT products.* FROM categories, 
category_subcategory, subcategories, subcategory_product, products WHERE (categories.category_id=$category_id AND 
categories.category_id=category_subcategory.category_id AND 
category_subcategory.subcategory_id=subcategory_product.subcategory_id AND subcategory_product.product_id=products.product_id) OR 
(subcategories.subcategory_id=$subcategory_id AND subcategories.subcategory_id=subcategory_product.subcategory_id AND 
subcategory_product.product_id=products.product_id) OR 
(products.product_id=$product_id)

This produces a result table with over 300,000 entries (usually causing php to timeout)...because it is cross joining the tables, taking the number of rows in categories*category_subcategory*subcategories*subcategory_product*products and making a 300,000 row table with every possible combination before applying the WHERE clause and editing out what I want...

There must be a better way to do this??

The goal is to give it a list of cateogries, subcategories, and products, and return all products falling under those criterias.



ONRE MORE THING...

Is there any way to UPDATE products SET product_price=2 WHERE product_id=(AN ARRAY)?

I have an array of the product_ids that I want to update, but am currently looping through them using a foreach loop and updating them seperatly, creating numerous quieries to the database...

[GoRadic.Com]

ok i dont understand the first question, but the second question i can answer. Yes you can like this.

UPDATE products SET product_price=2 WHERE product_id=firstitem OR product_id=seconditem OR product_id=thirditem OR product_id=fourthitem.....

Im pretty sure that will work

[StephenBauer]

I have 100 produts in 25 subcategories in 10 categories. They are connected via "connecter tables"...anyone know the real name of those??

Some people call them cross-ref (xref) tables, intersection tables, link tables, etc.

For example...subcategory_product has too colums linking subcategory IDS with product IDS. This way products can be in multiple subcategories, and subcategories can be in multiple cateogries.

My goal is to select any number of category and/or subcateogires and/or products and them update all the products that fall under the cateogories selected and the subcategories selected or are explicitly selected.

So far I have...

 SELECT DISTINCT products.* FROM categories, 
category_subcategory, subcategories, subcategory_product, products WHERE (categories.category_id=$category_id AND 
categories.category_id=category_subcategory.category_id AND 
category_subcategory.subcategory_id=subcategory_product.subcategory_id AND subcategory_product.product_id=products.product_id) OR 
(subcategories.subcategory_id=$subcategory_id AND subcategories.subcategory_id=subcategory_product.subcategory_id AND 
subcategory_product.product_id=products.product_id) OR 
(products.product_id=$product_id)

This produces a result table with over 300,000 entries (usually causing php to timeout)...because it is cross joining the tables, taking the number of rows in categories*category_subcategory*subcategories*subcategory_product*products and making a 300,000 row table with every possible combination before applying the WHERE clause and editing out what I want...

There must be a better way to do this??

The goal is to give it a list of cateogries, subcategories, and products, and return all products falling under those criterias.

Do not have time to get into this right now...will try to look at it tonight (currently at work).

ONRE MORE THING...

Is there any way to UPDATE products SET product_price=2 WHERE product_id=[b](AN ARRAY)?

I have an array of the product_ids that I want to update, but am currently looping through them using a foreach loop and updating them seperatly, creating numerous quieries to the database...

Yeah, use the "IN" clause, such as:

UPDATE
  products
SET
  product_price=2
WHERE
  product_id IN ('QX324', 'QY553', 'YY342')

-or-

UPDATE
  products
SET
  product_price=2
WHERE
  product_id IN (314234, 123434, 123412, 883921)

-or even-

UPDATE
  products
SET
  product_price = 2
WHERE
  product_id IN (SELECT product_id FROM products LEFT OUTER JOIN blah-blah-blah...)

but in this instance, the SELECT subquery (the one in the parenthesis) would be a complex query of sorts (typically lots of joins and such) otherwise you could just use a normal WHERE clause without the subquery/IN combo.

[StephenBauer]

Check out the "IN" SQL clause. (Not sure if mySQL supports it offhand...and no time to look that up.)

ok i dont understand the first question, but the second question i can answer. Yes you can like this.

UPDATE products SET product_price=2 WHERE product_id=firstitem OR product_id=seconditem OR product_id=thirditem OR product_id=fourthitem.....

Im pretty sure that will work

[StephenBauer]

I had some time at lunch to kill...

A select statement:

select
	c.CategoryID,
	c.CategoryName,
	s.SubCategoryID,
	s.SubCategoryName,
	p.ProductID,
	p.ProductName
from
	[Catgories] c
left outer join
	[Category_SubCategory] cs
on
	c.CategoryID = cs.CategoryID
left outer join
	[SubCategories] s
on
	cs.SubCategoryID = s.SubCategoryID
left outer join
	[SubCategory_Product] sp
on
	s.SubCategoryID = sp.SubCategoryID
left outer join
	[Products] p
on
	sp.ProductID = p.ProductID
where
	c.CategoryID = 2203
order by
	c.CategoryID,
	s.SubCategoryID,
	p.ProductID

This would select all products within a given CategoryID (in this case 2203). You can of course alter the columns that are returned to suite your needs. This should return a smaller result set too (since you are not doing cross joins). In the WHERE clause, you can expand it to include references to SubCategoryID and/or ProductID and even the use of the "IN" clause if you want to do crazy selects across multiple xxxxxxxxxID ranges at the different levels (i.e. CategoryID, SubCategoryID, and ProductID).

You can then use the above code, slightly modified, as a subquery in your UPDATE clause:

UPDATE
	Products
SET
	ProductMarkup = 1.20
WHERE
	ProductID IN (	select
				p.ProductID
			from
				[Catgories] c
			left outer join
				[Category_SubCategory] cs
			on
				c.CategoryID = cs.CategoryID
			left outer join
				[SubCategories] s
			on
				cs.SubCategoryID = s.SubCategoryID
			left outer join
				[SubCategory_Product] sp
			on
				s.SubCategoryID = sp.SubCategoryID
			left outer join
				[Products] p
			on
				sp.ProductID = p.ProductID
			where
				c.CategoryID = 2203
			order by
				c.CategoryID,
				s.SubCategoryID,
				p.ProductID
			)

Again, you can then alter the WHERE clause in the subquery to your heart's delight. Although that UPDATE example could be re-coded without the subquery and possibly with an increase in efficiency. However, sometimes the subquery method is easier to deal with and definately reuse at a later time (i.e. just plug-n-play a new subquery in that returns just the ProductID).

This type of layout can be used for security as well but with a branch for "roles" support.

[jumpthru]

Thank you to everyone for taking the time to reply. I wish I was good at SQL casue I know the code I finally came up wtih can be optimised but its really hard to get help (nothing to do with your guys lack of skills) when you dont have access to all the code and databases. Here is what I ended up with.

(and remember, each array edit_adjust_subcategories_criteria, edit_adjust_categories_criteria, edit_adjust_products_criteria, edit_adjust_colors_criteria gets looped through and can have up to 100 memebers)

// Make adjusts
if ($yes=="Yes") {
        // Retrieve products to adjust
        if (isset($edit_adjust_products_criteria)) {
                foreach ($edit_adjust_products_criteria as $value) {
                        $edit_adjust_products_list[]=$value;
                }
        }
        if (isset($edit_adjust_categories_criteria)) {
                foreach ($edit_adjust_categories_criteria as $value) {
                        $result=mysql_query("SELECT DISTINCT product_id FROM category_subcategory, subcategory_product 
                        WHERE category_subcategory.category_id=$value AND category_subcategory.subcategory_id=subcategory_product.subcategory_id");
                        while($row=mysql_fetch_array($result)) {
                                $product_id=$row['product_id'];
                                $edit_adjust_products_list[]=$product_id;
                        }
                }
        }
        if (isset($edit_adjust_subcategories_criteria)) {
                foreach ($edit_adjust_subcategories_criteria as $value) {
                        $result=mysql_query("SELECT DISTINCT product_id FROM subcategory_product WHERE subcategory_id=$value");
                        while($row=mysql_fetch_array($result)) {
                                $product_id=$row['product_id'];
                                $edit_adjust_products_list[]=$product_id;
                        }
                }
        }
        if (isset($edit_adjust_colors_criteria)) {
                foreach ($edit_adjust_colors_criteria as $value) {
                        $result=mysql_query("SELECT DISTINCT product_id FROM 
product_color WHERE color_id=$value");
                        while($row=mysql_fetch_array($result)) {
                                $product_id=$row['product_id'];
                                $edit_adjust_products_list[]=$product_id;
                        }
                }
        }
        // Remove duplicate products
        $edit_adjust_products_list=array_unique($edit_adjust_products_list);
        // Adjust products
        if (isset($edit_adjust_products_list)) {
                foreach ($edit_adjust_products_list as $value) {
                        // Adjust prices
                        $result=mysql_query("UPDATE prices SET price_price=price_price*" . (1+($edit_adjust_price/100)) . " WHERE price_product=$value");
                        // Adjust personalization prices
                        $result=mysql_query("UPDATE personalization_prices SET personalization_price=personalization_price*" . 
                        (1+($edit_adjust_personalization_price/100)) . " WHERE 
personalization_product=$value");
                        echo (mysql_error());
                        // Insert or remove subcategories
                        if (isset($edit_adjust_subcategories)) {
                                if ($edit_adjust_subcategories_choice==0) {
                                        foreach ($edit_adjust_subcategories as $value2) {
                                                $result=mysql_query("INSERT subcategory_product SET subcategory_id=$value2, product_id=$value");
                                        }
                                }
                                else if ($edit_adjust_subcategories_choice==1) {
                                        foreach ($edit_adjust_subcategories as $value2) {
                                                $result=mysql_query("DELETE FROM subcategory_product WHERE subcategory_id=$value2 AND product_id=$value");
                                        }
                                }
                        }
                        // Insert or remove colors
                        if (isset($edit_adjust_colors)) {
                                if ($edit_adjust_colors_choice==0) {
                                        foreach ($edit_adjust_colors as $value2) {
                                                $result=mysql_query("INSERT product_color SET color_id=$value2, product_id=$value");
                                        }
                                }
                                else if ($edit_adjust_colors_choice==1) {
                                        foreach ($edit_adjust_colors as $value2) {
                                                $result=mysql_query("DELETE FROM product_color WHERE color_id=$value2 AND product_id=$value");
                                        }
                                }
                        }
                }
        }
        $message.="The mass adjusts were made.<br>";
} 

PS, I still want to know why when you make a post and type the first letter, the second one is usally on a new line...very annoying...
PSS Arhge!!! Now the php tags are breaking from my code....!!

[jumpthru]

I just did a test, and with everything selected on the page, there is a total of 4005 queries made...

Hmm...

[null]

Stephen, if you don't mind me asking, why the outer joins instead of inner joins?

[StephenBauer]

Stephen, if you don't mind me asking, why the outer joins instead of inner joins?

I work with a lot of hierarchical data so sort of a force of habit. It usually gives me a more solid view of what is going on when I am using 'testing'/'finding' queries. The code above was actually a snippet from some testing queries I had for something with a similar set of relationships (only I search-and-replaced the column names).

Also, at times, it may be nice to include catagories that do not have sub-categories or sub-categories that do not have products (which the left outer joins include).

[null]

Also, at times, it may be nice to include catagories that do not have sub-categories or sub-categories that do not have products (which the left outer joins include).

That's what I though, but I guess we have a slight contrast. If the user doesn't need 'em, I would've just went w/inner joins...not sure what the data looks like but from his posts, the ultimate goal is to get to the product level, and update the price there. I just didn't come across that he needed categories or sub cat's that didn't have product info, thus inner join instead of outer...wouldn't it also be faster and produce a smaller result set to carry up front? No dispute, you justified your code, was just curious why you opted for the outer join. Thx man.

[StephenBauer]

That's what I though, but I guess we have a slight contrast. If the user doesn't need 'em, I would've just went w/inner joins...not sure what the data looks like but from his posts, the ultimate goal is to get to the product level, and update the price there. I just didn't come across that he needed categories or sub cat's that didn't have product info, thus inner join instead of outer...wouldn't it also be faster and produce a smaller result set to carry up front? No dispute, you justified your code, was just curious why you opted for the outer join. Thx man.

Without re-reading his posts (which were a bit confusing), it sounded like he did want to update a product price (in his other thread) but in this thread it sounded like he wanted to update various categories, subcats, and/or products. What popped into my head was possible data at the category and subcat level (like client/site indicators or such to restrict their inclusion). Maybe my take on it was wrong judging by his other thread.

You are right about the smaller result set and increased performance if going with inner joins!

Edit: I just re-read this thread...he did mention "product updating"...oh heck!