CODEWITHSUNDEEP
phpmysql
studioromeo
studioromeo

Reputation: 1548

Sorting a product by price

Say I have a table of products with fields regular_price and sale_price and I want to sort these products by current price, how would you go about it?

A solution would be to check & update a new field price based on if sale_price had a value every time the product is loaded. But that feels really messy & I don't really want to have to duplicate data if I can help it.

Thanks!

EDIT: To add extra complexity I also need to be able to convert this php statement into the query:

if ( $this->sale_price_dates_from   <= $time && 
        $this->sale_price_dates_to >= $time &&
        $this->sale_price)
    return true;

EDIT2: $time refers to the current unix timestamp.

My table structure is: meta_id, post_id, meta_key, meta_value

All fields sale_price, regular_price..dates ect are stored in meta_key with the corresponding value in meta_value.

All dates are stored as unix timestamps

Upvotes: 0

Views: 568

Answers (1)

xdazz
xdazz

Reputation: 160953

ORDER BY IFNULL(sale_price, regular_price)

Edit: Your php statement to the query is like below:

WHERE (meta_key = 'sale_price_dates_from' AND meta_value <= NOW())
AND (meta_key = 'sale_price_dates_to' AND meta_value >= NOW())
AND (meta_key = 'sale_price' AND meta_value IS NOT NULL)
AND meta_id = ?

Upvotes: 2

Related Questions