SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  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)

    A few SQL questions

    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

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking This is going to be LOOOOONG

    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? Yes. The part in blue you left out...
    Code:
    UPDATE  Products
    SET     Product_price = product_price * 1.052
    Where   product_id = $value
    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?

    Yes, you can jumble all that into one single update, BUT before I go to far (there are other things I want to ask you about), let me say this...I understand the need to get the price * 1.05 calc, but your requesting a query to UPDATE the price in the table itself. Going off that, you may as well as...
    1) Just update all the records in the table right off the bat, or
    2) Add a field that contains the price * 1.05 calc in it. (either to the table itself or to the record set)

    #1 doesn't seem like the ideal choice, here's why I said that though...when you perform the UPDATE your requesting, your actually going back and touching the table itself, the price in the product table for that particular product_id will stay @ the *1.05 value...so the original price gets totally discarded...and eventually overtime all the prices will come to their price * 1.05 value. You'd force yourself to have to do another UPDATE in order to...
    1) Get the product back to it's original price, and
    2) To avoid a continuous 5% increment on price. When that product is selected again, the coded UPDATE will exec again, so say the product was selected 4 times in one day, the multiple updates will do --> (price * 1.05) then again, (price *1.05) for the 2nd time it was selected, then again (price *1.05) when selected the 3rd time, see what I'm saying?? I would do #2, or save the calc entirely for the front end. Or if you just want to generate the price * 1.05 calc in the record set, try this...basically the same as above but using a calc to create a field to the record set...
    Code:
    Select  distinct SC.Category_id, 
            SC.SubCategory_id,
            PS.Product_id,
            P.Price [ourPrice],
            P.Price * 1.05 [SalePrice]
    from    Subcategory_Category SC
    join    Product_Subcategories PS on SC.Subcategory_id = PS.Subcategory_id
    join    Product P on PS.Product_id = P.Product_id
    where   SC.Category_id = @Category_id
    and     SC.subCategory_id = @subCategory_id
    and     PS.Product_id = @Product_id
    Last edited by null; Apr 23, 2004 at 17:01. Reason: Edited to reduce the ramble...wrote reply for Server and poster is using MySQL

  3. #3
    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 am not exactly sure I followed since it isn't in mysql, but I will look at it closer when I get home from school later...

    Take a look at this thread, maybe it will explain my question better

    http://www.sitepoint.com/forums/showthread.php?t=165541

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok Nate, sorry for that long ramble then. I went ahead and edited most that stuff out since it's not totally helping since we're thinking 2 diff DBMS's. I did leave info from the UPDATE you asked about though, I still think it would be a better idea to come up w/the Price *1.05 calc by not updating the actual table.

  5. #5
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I posted in your other thread where you expand this to include cross-ref tables. The code at the bottom may help you out here too.

    http://www.sitepoint.com/forums/show...74&postcount=5

    Also, I agree with "null" on not modifying the price of the item in the table. You may want to keep a "BasePrice" or "Cost" field in your "Products" table and then add a "ProductMarkup" field that contains a percentage price markup (such as 1.20 for a 20% markup). You can add other similar fields if you have other pricing schemes as well (or they could theoretically be placed in their own table especially if they are client or client-type oriented - even another cross-ref table scheme here too). Keeping the "Cost" around is good for reporting and record keeping.
    Last edited by StephenBauer; May 26, 2004 at 11:18.

  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)
    Quote Originally Posted by null
    Ok Nate, sorry for that long ramble then. I went ahead and edited most that stuff out since it's not totally helping since we're thinking 2 diff DBMS's. I did leave info from the UPDATE you asked about though, I still think it would be a better idea to come up w/the Price *1.05 calc by not updating the actual table.
    No, no, dont be sorry! Thanks so much for your help, I really appreciate it, I hope I didn't come off rude.

    Anyways, why are you saying to not edit the prices...? The clients cost of meterials just went up, so he needs all prices to be upped 5.2 percent permantly...why would he want to keep the original price which is now irrevelent and will never be need again?

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jumpthru
    No, no, dont be sorry! Thanks so much for your help, I really appreciate it, I hope I didn't come off rude.

    Anyways, why are you saying to not edit the prices...? The clients cost of meterials just went up, so he needs all prices to be upped 5.2 percent permantly...why would he want to keep the original price which is now irrevelent and will never be need again?
    Na buddy...didn't come across as rude @ all...I actually, kinda felt bad for making you read through all that jumble when it wasn't totally applicable, lol.

    You're new info on the UPDATE is key...so yes, given what you just added, you can just go ahead and UPDATE the prices in the Product table. Do all the prices need to be up'd by 5%? If so you can just do an Update on the entire table. I gave you an UPDATE earlier in the thread, you can use it, just take out the WHERE clause and all prices in that table will be up'd by 5.2%.

  8. #8
    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....!!


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
  •