selecting products based on price range and sale price range

[sxtrail]

Hi there

I was hoping for a quick solution to this problem.....

I'm allowing users of my site to input a price range to filter the product list. Sounds simple but I need to compare the values against either 'prod_price' or 'product_saleprice' but only compare against 'prod_saleprice' if the value is greather than 0. I don't know how or if I can do some sort of if statement within my where clause?

At the minute my query looks like this:

SELECT
    p.prod_id,
    p.prod_name,
    p.prod_price,
    p.prod_saleprice,
    b.brand_name,
    pp.thumbnail
FROM products_to_categories AS ptc
    INNER JOIN
        products AS p
        ON p.prod_id = ptc.prod_id
		AND p.prod_price >= 80
		AND p.prod_price <= 100
    LEFT JOIN
        brands AS b
        ON b.brand_id = p.brand_id
    LEFT JOIN
        products_photos AS pp
        ON pp.photo_id = p.default_photo_id
WHERE
    ptc.category_id = 25
ORDER BY p.prod_price ASC
LIMIT 0, 9

This produces incorrect results because a product would be shown if it has a regular price of lets say £85 but a sale price of £60. So how can I adapt it to use 'prod_saleprice' if a value exists or just use 'prod_price' if 'prod_saleprice' is 0?

Many thanks

[oddz]

SELECT    
     p.prod_id
     ,p.prod_name
     ,p.prod_price
     ,p.prod_saleprice
     ,b.brand_name
     ,pp.thumbnail
  FROM
     products p
 INNER 
  JOIN
     products_to_categories ptc
    ON
     p2c.category_id = 23
  AND
     p.prod_id = ptc.prod_id
  LEFT
  JOIN
     brands b
    ON
     p.brand_id = b.brand_id
  LEFT
  JOIN
     products_photos pp
    ON
     p.prod_id = pp.default_photo_id
 WHERE
      (p.prod_saleprice > 0 AND (p.prod_saleprice BETWEEN 80 AND 100))
      OR
      (p.prod_saleprice <> 0 AND (p.prod_price BETWEEN 80 AND 100))
 ORDER
    BY
     p.prod_price ASC

[sxtrail]

Hi Oddz,

Thanks for the reply. Could you possibly explain how that works as I can't get it to work?

Perhaps this line:

(p.prod_saleprice <> 0 AND (p.prod_price BETWEEN 80 AND 100))

should be:

(p.prod_saleprice = 0 AND (p.prod_price BETWEEN 80 AND 100))

?

Many thanks

[oddz]

Yeah, I believe that would be correct for your circumstances.

Oh… and p2c needs to be ptc. I normally write 2 over t for look up tables.

[sxtrail]

Hello again,

I've been doing a bit more reading into this as I had a feeling I could add some better conditional statements to the WHERE clause. Anyway, I came up with this:

SELECT
    p.prod_id,
    p.prod_name,
    p.prod_price,
    p.prod_saleprice,
    b.brand_name,
    pp.thumbnail
FROM products_to_categories AS ptc
INNER JOIN
    products AS p
    ON p.prod_id = ptc.prod_id
    AND
    (
        CASE WHEN p.prod_saleprice > 0
        THEN p.prod_saleprice BETWEEN 2000 AND 2500
        ELSE p.prod_price BETWEEN 2000 AND 2500
        END
    )
LEFT JOIN
    brands AS b
    ON b.brand_id = p.brand_id
LEFT JOIN
    products_photos AS pp
    ON pp.photo_id = p.default_photo_id
WHERE
    ptc.category_id = 25
ORDER BY p.prod_price ASC
LIMIT 0, 9

This seems to work well

It's possible that only a max or min price could be specified, so all I need to do now is adapt it for if a max or min price isn't specified.

Many thanks.

[r937]

i would change this --

CASE WHEN p.prod_saleprice > 0
     THEN p.prod_saleprice BETWEEN 2000 AND 2500
     ELSE p.prod_price BETWEEN 2000 AND 2500
 END

to this --

CASE WHEN p.prod_saleprice > 0
     THEN p.prod_saleprice 
     ELSE p.prod_price 
 END BETWEEN 2000 AND 2500

furthermore, i'm guessing you use 0 to represent "no sale price"

i would use NULL here instead, so that the CASE expression can be simplified to --

COALESCE(p.prod_saleprice,p.prod_price)
  BETWEEN 2000 AND 2500

[sxtrail]

Hi Rudy,

Ah yes, that's much cleaner. I was going to try and make the query deal with situations where a min or max price isn't passed to the query. Eg. If only a min price is passed then we check for products that have a price or sale price above that value. But I think this is probably easier and quicker to do with PHP and generate the relevant query accordingly

Works a treat! Although I'm thinking BETWEEN might not be suitable as the range should probably be inclusive of the min and max values.

Thanks again!

[r937]

Although I'm thinking BETWEEN might not be suitable as the range should probably be inclusive of the min and max values.

i don't know where this min and max business came into the picture, but if this is just your terminology for the lower and upper endpoints of a BETWEEN range, let me reassure you that BETWEEN ~does~ include its endpoints

[sxtrail]

Are you sure? As it certainly doesn't appear to work like that. If I run this:

SELECT
    p.prod_id,
    p.prod_name,
    p.prod_price,
    p.prod_saleprice,
    b.brand_name,
    pp.thumbnail
FROM products_to_categories AS ptc
INNER JOIN
    products AS p
    ON p.prod_id = ptc.prod_id
    AND
    (
    CASE WHEN p.prod_saleprice > 0
        THEN p.prod_saleprice 
        ELSE p.prod_price 
        END
        BETWEEN 799.98 AND 1099.99
    )
LEFT JOIN
    brands AS b
    ON b.brand_id = p.brand_id
LEFT JOIN
    products_photos AS pp
    ON pp.photo_id = p.default_photo_id
WHERE
    ptc.category_id = 25
ORDER BY p.prod_price ASC
LIMIT 0, 9

....then I get the products that are priced at 799.99 and 1099.99. If the lower value of the BETWEEN is changed to 799.99 then I don't get the products price at 799.99. So only the upper endpoint appeats to work

Thanks

[r937]

let me guess... datatype of your price columns is FLOAT?

[sxtrail]

Yep Is that a bad thing?

[r937]

Yep Is that a bad thing?

it certainly is a bad thing if 799.99 doesn't equal 799.99

check da manual, it's quite explicit -- FLOAT is an approximate datatype

try it with DECIMAL, e.g. DECIMAL(9,2) or something

[oddz]

The thing about placing the conditionals iin the ON clause is that most likely it will result in much slower query. You'll need to run an explain though to confirm that. Rather then a simple equality check the conditional will be ran for every single row. Not the most efficient way of doing things. You should also index those columns. Otherwise, your looking at a full table scan plus the conditionals for every single row.