1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help with an MySQL query that I am sure is simple but I can't wrap my head around it!

Discussion in 'Databases' started by lateral, Jul 16, 2015.

  1. #1
    Hi guys

    I am a newbie to MYSQL and would really appreciate some help with a query that I am sure is simple but I just can't seem to wrap my head around it.


    I am helping a friend with his web shop that is based upon the Prestashop platform.

    There is some functionality within Prestashop that is called "Packs".

    Basically, you create "products" that represent the items that you sell. The "product records" contain various bits of information such as price, description etc.

    You can also create a special type of product called a "pack". This is simply a collection of products linked to a single product record that has been flagged as a "pack product".

    For example, you may create a seperate product record for each of the following toiletry products because you sell them individually:

    Toothpaste - $1.00
    Soap - $2.00
    Nail Brush - $3.00
    Tooth Brush - $4.00


    You may also sell all of the above in a "pack" called "Special Toiletry Package" for a price of $10.00 (the total of the price of each individual product) or at a discount price of $9.00 because customers are buying a number of products.


    In my case, the price of the "Special Toiletry Package" is $10.00 because all of the individual items are already discounted.

    In order to do what I want, I need to first create the individual products (toothpaste, toothbrush etc) and then create a special product record called "Special Toiletry Package" and manually give it a price of $10.00......please note that there is not an option to have Prestashop dynamically create the price of "Special Toiletry Package" based the total of the individual products that make up the pack....and that's the problem.


    If we change the price of any of the individual products in the system, we also need to manually update the price of any or all of the "packs".....this is a really pain and unnecessarily time consuming.

    I want to have a query that I can manually run via the MySQL admin panel or within PHP (which would be even better!) that will look through the product records, identify the products that are defined as "packs" (I already know the name of the table and field etc) and then find the products that make up the pack (I also know the name of the table etc), total them up and then update the "Special Toiletry Package" price.
    SEMrush
    Can anybody please help?

    I have found the two tables that I am sure contain the data. (I exported them from the MYSQL database)

    PS_Product
    PS_Pack

    There are 3 fields in the PS_Product table that I think are important, "id_product","price" and "cache_is_pack". If "cache_is_pack" is set to "1" then this is a "pack" product record. If you then look in the "ps_pack" table, you will see the 2 fields that I think are important, "id_product_pack" and "id_product_item". So, using the example that I previously detailed, this table will have 4 records in it representing the 4 products of the "Special Toiletry Package".

    As the "ps_pack" record only contains the "id" of the pack products and not the price, we need to lookup the price of each product, add them all up and then update the price of "Special Toiletry Package".



    Thanks for taking the time to read this and for any help you can provide.

    Regards
    Greg
     
    lateral, Jul 16, 2015 IP
    SEMrush
  2. mmerlinn

    mmerlinn Notable Member

    Messages:
    2,415
    Likes Received:
    353
    Best Answers:
    6
    Trophy Points:
    290
    #2
    I am somewhat confused here.

    You say that the pack table has 2 important fields, but you do not specifically say what is in those fields. Then you go on to say that the pack table has 4 records in it showing the 4 products in that pack.

    So, do you have 4 separate records where the id_product_pack fields all have "Special Toiletry Package" in them and the id_product_item fields each contain the name of one of the items in the pack? Or do you have 1 record for toiletries that has the id_product_item field containing a list of all of the products in that pack?

    Good database design does require that the price NOT be in the ps_pack record as is the case with you.

    For your purposes I see no reason to know what is in the cache_is_pack field. All it does is tell you whether this item exists in a package. For example, toothpaste could be in a Special Toiletry Package AND a Special Dental Health Package, so for your purposes this field is meaningless.
     
    mmerlinn, Sep 5, 2015 IP