Displaying decimals in MySQL

[Anon]

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

[[deleted]]

What query/code are you using to display the prices?

[Anon]

Hi Vincent,

For the Spanish version of the site the following code is used:

<?PHP echo number_format($row["Cuota_A"], 2, ',', '.'); ?>

For the English version the default is used:

<?PHP echo $row["Cuota_A"] ?>

Both languages are rounding up / down the numbers.

Cheers,

Gary

[[deleted]]

In that case, are you sure that the numbers are inserted as floats, and that they are not rounded before they go into the database?

And are you sure the database has a FLOAT type and not an INTEGER type?

[Anon]

Hi Vincent,

What the client has passed to me is an SQL file based on my table structure. If I view the SQL data file in Homesite the prices are shown as follows (the zero is missing after the 6, but the other prices are shown correctly):

"85,07","80,6","71,64"

What I've noticed is this - when I import this data into my local copy of MySQL and then export it to upload the prices are then incorrect, so it appears to be an issue with my MySQL settings.

Any ideas?

Thanks,

Gary

[Anon]

Hi,

Quick addition to the above. I just tested this by uploading the original client file (with correct prices) to the ISP and the same problem still occurs.

So it appears that this is related to the float(10,2) tag. I did try to change that field type to test other options but MySQL gave repeated errors.

Gary

[[deleted]]

are you sure the database has a float type?

any error/warnings during the import?

[[deleted]]

How do you import the data?

[Anon]

Vincent,

There are no errors while importing the SQL file at the ISP (or here on my local copy for that matter).

Why would a Float type not exist? Is this something that can be disabled?

Gary

[Anon]

Hi Vincent,

Thanks for your help here.

On my local copy of the site / DB the data is imported with MySQLfront using the query option. At the ISP version they have a very crude form interface to allow for file import. Results are the same in both cases.

The actual file being included is a standard SQL file table structure outlined as follows:

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;

And a sample row as follows:

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

Gary

[[deleted]]

Ah feck... should have known...

MySQL wants 85.05, not 85,05

That means you probably have to modify the data to replace the comma with a dot somehow, I suggest importing the values as a varchar into a temporary table, then using an update to replace the commas, then copy the data from the temporary table to the real table.

[Anon]

Vincent,

Oh f**k!!! So anyone creating tables who lives in a country that uses the 123,50 number format has to go through all that to display prices correctly in MySQL?

I think I may cry...!

But listen - thanks a whole lot for your help on this one, much appreciated.

Gary

[[deleted]]

There may be tricks to change the default format that MySQL understands, you may want to search the manual/net for more info.