Prevent Mysql from Converting Numbers

Hi,
This is my database structure.

CREATE TABLE IF NOT EXISTS condo (
id int(6) NOT NULL auto_increment,
date date NOT NULL default ‘0000-00-00’,
location varchar(100) NOT NULL,
property_type varchar(100) NOT NULL,
tenure varchar(50) NOT NULL,
status varchar(50) NOT NULL,
sale_rent varchar(50) NOT NULL,
size decimal(6,2) default ‘0.00’,
price decimal(8,2) default ‘0.00’,
price_psf decimal(8,2) default ‘0.00’,
rooms varchar(5) NOT NULL,
baths varchar(5) NOT NULL,
facilities varchar(150) NOT NULL,
renovations varchar(150) NOT NULL,
furnishings varchar(150) NOT NULL,
phone_1 varchar(20) NOT NULL,
advertiser varchar(50) NOT NULL,
e_num varchar(20) NOT NULL,
unique varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique (unique)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30390 ;

My problem lies with the size, price, price_psf fields. I want to perform some calculations on these fields. However, when I import numbers into these fields, mysql converts it into a different format. The data is being imported from a tab delimited text file. For example:-
Original Raw Data Before Import
(size)90017 (price)30,000.00 (price_psf)0.33

Data After Import
(size)9999.99 (price)30.00 (price_psf)0.33

I have no idea how 90017 got converted to 9999.99. I suppose that 30,000.00 got converted to 30.00 due to the comma.

Therefore, how do I modify the table structure of to prevent these conversions? How do I tell mysql to ignore the commas in these fields when importing raw data?

Thanks for your advice.

[quote=“transfield, post:1, topic:105753”]I have no idea how 90017 got converted to 9999.99.[/quote]that’s really easy to understand

DECIMAL(6,2) means a total of 6 digits, of which 2 are to the right of the decimal

90017 is way too big to fit in there

1 Like

Sorry, I don’t understand your explanation. How do I make 90017 fit in? Thanks.

[quote=“transfield, post:3, topic:105753, full:true”] How do I make 90017 fit in? Thanks.
[/quote]
by changing the column so that it’s big enough to hold it

In size decimal(6,2) means that you should store 6 places(scale) with 2 to the right of the decimal and 4 to the left of the decimal.

For example :
values should be like this 1234.56

Note:

  • your value 90017 is invalid for decimal(6,2) position. So, it is showing the maximum value 9999.99.

Thanks a lot for your explanation vembutech. I appreciate it. Have a good weekend :slight_smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.