[crighton]

Hi,

I'm in the thick of developing my first MySQL DB backed site and don't fully understand integer types and how they should be referred to in MySQL.

Prices on the site are currently all being rounded up (or down) but retaining two decimal points, eg. 12,00 or 123,00 when they should be 12,50 or 123,75 etc. (Note: comma is the decimal marker required.)

The field/s in question are currently set to Float(10,2). I've looked through the MySQL documentation but don't fully understand the differences between the integer types.

Can anyone shed any light?

Thanks in advance,

Gary Crighton

[Paul]

Are you sure it's not something in the app that's rounding? If you're using PHP, you may need to compile the bcmath options in.

The column types that should work are Decimal, Double and Float.

[crighton]

Hi Paul,

I've been looking into this a little more but can still not identify where the error is occurring. I'm sure this is due to my inexperience with data types and numerals etc.

What I have noticed is this, the SQL data file is provided from the client with the prices inserted correctly, in this case in the format 123,50 etc. Once they are run through MySQL those numbers are rounded up/down and shown as 123,00 etc.

I've included sample code / data structure below in case there is some major booboo there.

Locally I'm using MySQLfront and did try to change the data type from float(10,2) to decimal(10,2) but this produced an error.

Someone has suggested that the problem lies with the format of the prices at import level - ie. 123,50 should be imported as 123.50 (hence it is ignoring everything after the comma), but if this is the numeral system here (Spain) obviously I can't ask the client to provide their prices in another format. If this is in fact the case is there some sort of workaround?

Any ideas?

Thanks in advance and all help / pointers appreciated!

Gary

PS. My First DB. I'll write the book from hell about it afterwards!

I'm using the following pieces of code -

Table structure:

CREATE TABLE productos (
Pedido varchar(7),
Titulo varchar(100),
ID int(4) unsigned,
Precio_Basico float(10,2),
Cuota_A float(10,2),
Cuota_B float(10,2),
Cuota_C float(10,2),
Year int(6) unsigned,
Formato_Obra varchar(60),
Formato_Papel varchar(60),
Tipo_Papel int(2) unsigned,
Tecnica varchar(100),
Descripcion_es text,
Descripcion_en text
) TYPE=MyISAM;

Sample row:

INSERT INTO productos VALUES("10272","El criticón","306","89,55","85,07","80,6","71,64","", "","","","","","");

To display the prices:

Spanish format <?PHP echo number_format($row["Cuota_A"], 2, ',', '.'); ?>
UK / US format <?PHP echo $row["Cuota_A"] ?>