SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  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 16: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 10: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%.


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
  •