3

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..

coder101
  • 1,601
  • 2
  • 21
  • 41

1 Answers1

3

The best option here is to accept that VARCHAR is not the correct data type. Your plan of action is simple.

  1. Add new column priceNew DECIMAL(12,2)
  2. Migrate data from price into priceNew
  3. Once you've confirmed data is correct in priceNew, delete price and rename priceNew to price
  4. Remove all the CAST(price AS DECIMAL) hackjobs and start dealing with the number as a number
  5. Update anything that SELECT/UPDATE/INSERT's to price to make sure it deals with the correct new data type

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.

Simon at The Access Group
  • 6,932
  • 22
  • 45
  • I had that in mind as PLAN B. Actually, there are a lot of other fields which should have been INTEGER type but has been given VARCHAR by the earlier developers. So, i was thinking creating another mirror table with correct datatypes, write a PHP script to read data from the wrong table and insert into the correct one. Do you think it is the right approach? that way, i won't even have to ad new columns with DECIMAL(12,2) – coder101 Apr 19 '13 at 13:21
  • This needs to be done, it really does. It needs to be done once, get the datatypes correct. Numerics in general should be INT (or TINYINT/SMALLINT/MEDIUMINT). Currency should generally be DECIMAL(x,2) where x = often around 10-12. NEVER NEVER NEVER put currency in a FLOAT() if you value precision as eventually you'll start losing pennies. In your select your key issue is you are expecting a string (price) to behave as a numeric and it simply doesn't. You could `WHERE CAST(price AS DECIMAL) <= 500` etc. BUT that would cause table scans on every query. Better to just fix it properly asap. – Simon at The Access Group Apr 19 '13 at 13:24
  • 1
    thanks, simon, i guess i need to do that. I better charge my client for this :) – coder101 Apr 19 '13 at 13:30