SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard jumpthru's Avatar
    Join Date
    Apr 2000
    Location
    Los Angeles, California
    Posts
    1,008
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please help...very complicated query.

    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...

    Code:
     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...

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    CT, USA
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Promote Your Site For Free:
    Go Radic Banner Exchange

  3. #3
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jumpthru
    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.

    Quote Originally Posted by jumpthru
    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...

    Code:
     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).

    Quote Originally Posted by jumpthru
    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:

    Code:
    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.
    Last edited by StephenBauer; May 26, 2004 at 10:16.

  4. #4
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Check out the "IN" SQL clause. (Not sure if mySQL supports it offhand...and no time to look that up.)

    Quote Originally Posted by 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
    Last edited by StephenBauer; May 26, 2004 at 10:15.

  5. #5
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had some time at lunch to kill...

    A select statement:

    Code:
    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:

    Code:
    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.
    Last edited by StephenBauer; May 26, 2004 at 10:15.

  6. #6
    SitePoint Wizard jumpthru's Avatar
    Join Date
    Apr 2000
    Location
    Los Angeles, California
    Posts
    1,008
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

    PHP Code:
    // 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....!!

  7. #7
    SitePoint Wizard jumpthru's Avatar
    Join Date
    Apr 2000
    Location
    Los Angeles, California
    Posts
    1,008
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just did a test, and with everything selected on the page, there is a total of 4005 queries made...

    Hmm...

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stephen, if you don't mind me asking, why the outer joins instead of inner joins?

  9. #9
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by null
    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).
    Last edited by StephenBauer; May 26, 2004 at 10:15.

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StephenBauer
    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.

  11. #11
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by null
    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!
    Last edited by StephenBauer; May 26, 2004 at 10:14.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •