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.
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?
Thanks in advance and all help / pointers appreciated!
PS. My First DB. I'll write the book from hell about it afterwards!
I'm using the following pieces of code -
CREATE TABLE productos (
ID int(4) unsigned,
Year int(6) unsigned,
Tipo_Papel int(2) unsigned,
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"] ?>