PHP: Apply a certain summer value at SQL SELECT



  • There's an Internet store. I'll take from one MySQL table the ID number of a certain category of goods with a category iidi, then I'll take their prices from another table using LEFT JOIN and sort through ORDER by the price field:

    SELECT a.product_id, b.product_price FROM table_product_categories as a WHERE category_id = $category_id
    LEFT JOIN table_product_prices as b ON b.product_id = a.product_id
    ORDER BY b.product_price
    

    At first glance, it's simple, but it's costly. TREATMENT:

    1. Some goods in the category do not have a price, i.e. in the table. table_product_prices There's no corresponding price recording for their. Some prices are equal 0 or a little more than zero. 1 e.g. 0.01♪ In these cases, such goods are not on the page at all.

    2. Field product_price It's the original price. The second problem is that some products have a new price in the field. product_override_price Table table_product_prices (sighs)product_price in such a case, it acts as an old price, and for some products, a discount is shown in the field. product_discount (in the same table). In such cases, sorting ORDER BY b.product_price incorrect as the field of the original price is performed product_price Not prejudiced product_override_priceNo product_discount♪ We need to be sorted at the final price, and it's calculated at the summer and there's no field in the table.

    I don't know.

    To solve problem 1: If the goods have no price, or the price is equal. 0or more 0 less 1 Then you have to take a summer at SQL-Request (if possible) to take the field. product_price value 1

    To solve problem 2: to take into account the new price and the discount. table_product_prices One more field. b.product_override_price and b.product_discount and further if the field is b.product_override_price more than 0 (in default equals 0) then we sort it instead. product_priceand if the value of the field product_discount for some product, too. 0 e.g. 5 (laughs) 5%) then from the original price product_price Let's take the discount. 0.05 (*5%).

    Maybe we need to set up some kind of "time" field for the summer. final_price (if possible) and then, in the case of the first problem, assign a value to it 1and in the second case, calculate the final price and then sort through ORDER BY final_price



  • I think the request should look like this:

    SELECT a.`product_id`,
    (CASE
        WHEN b.`product_override_price` > 0 THEN b.`product_override_price`
        WHEN b.`product_discount` > 0 THEN  b.`product_price` - (b.`product_discount` * b.`product_price` / 100)
        WHEN b.`product_price` < 1 THEN 1
        ELSE b.`product_price`
    END) AS final_price
        FROM `table_product_categories` as a
        LEFT JOIN `table_product_prices` as b ON b.`product_id` = a.`product_id`
        WHERE `category_id` = $category_id
    ORDER BY final_price
    

    UPD: I checked this request on the test database, it works. Reference to the working example: http://sqlfiddle.com/#!9/aebc0dd/19/0




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2