Reputation: 1605
I have been given an e-commerce project to fix some errors on by my client. The earlier dumb developers had given the price field VARCHAR datatype instead of so very obvious INTEGER in the products table, and so the next set of developers in order for the search according to price range to work used CAST(PRICE AS DECIMAL)
.
Now, i am the third one to work upon this and have found that it this CAST AS DECIMAL thing isn't strangely working only for the price range 0-500 but works for all otherr like 500-1000, 1000-2000 and so on.
The query is like:
The query so far is :
SELECT * FROM tbl_product where status=1 and subcat_id='128' and
price>'0' and price<='500' ORDER BY CAST(price AS DECIMAL(12,2)) ASC
This is somehow searching products with the price of 1000 and upwards.. Please help..
Upvotes: 3
Views: 8737
Reputation: 7025
The best option here is to accept that VARCHAR is not the correct data type. Your plan of action is simple.
This will give you far less headaches in the long run and will be more reliable / optimal. It will also allow MySQL to properly treat it as numeric instead of a string and so keys / values that select ranges based on that column will become more efficient.
Upvotes: 3