Handle decimal numbers in mysqli

php floating-point mysqli floating-accuracy floating-point-precision
Saturnix picture Saturnix · Jun 5, 2013 · Viewed 11.7k times · Source

I have to put the price of some items inside a mysql table. When creating the table I'm using DECIMAL(10,2) as I don't need more than 2 digits after the comma (for example: 123,45 would be accepted as an input but 123,456 would be rounded to 123,45 with PHP).

First question: using DECIMAL(10,2), how do I know how many numbers may be stored before the comma? I know it is not 10, as 10 is just the precision Mysql uses when doing math with those numbers: so where the length of the number itself is specified?

Second question: I'm using PHP to round user input to fit the data type (float with 2 numbers after the comma). How should I use mysqli->bind_param to insert those data? Which of these datatypes (from the documentation) accepted by bind_param should I use (and possibly: why)?

Character   Description
i   corresponding variable has type integer
d   corresponding variable has type double
s   corresponding variable has type string
b   corresponding variable is a blob and will be sent in packets

Answer

Hugo Delsing picture Hugo Delsing · Jun 5, 2013

DECIMAL(10,2) means that 10 is the maximum number of digits to be used. The 2 specifies that there are two on the right side of the decimal. So this means there are 8 digits left on the left side. See the manual

As for the datatype, you should use double as this is for decimal numbers.

Integer has no decimal chars, so it will floor all numbers. String is for text and not numeric values. Blob is for a binary large object, EG: an image

Related questions

PHP String to Float

I am not familiar with PHP at all and had a quick question. I have 2 variables pricePerUnit and InvoicedUnits. Here's the code that is setting these to values: $InvoicedUnits = ((string) $InvoiceLineItem->InvoicedUnits); $pricePerUnit = ((string) $InvoiceLineItem->PricePerUnit); If I …

Read More
Converting a number with comma as decimal point to float

I have a list of prices with a comma for a decimal point and a dot as the thousand separator. Some examples: 12,30 116,10 1.563,14 These come in this format from a third party. I want to convert them to floats and add …

Read More
PHP Convert String into Float/Double

I have list of string (size in bytes), I read those from file. Let say one of the string is 2968789218, but when I convert it to float it become 2.00. This is my code so far : $string = "2968789218"; $float = (float)$string; //$float = …

Read More