Handle decimal numbers in mysqli
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 videos on Youtube
Saturnix
Full-stack web developer currently working with django. Mainly using Python for data analysis in finance (trading and securities pricing). Have worked for a company specialized in c# development for asp.net-mvc-4 enterprise applications as a front-end developer, switching from jquery to more convenient frameworks such as knockout and angular which allowed us to build reusable and highly customizable DOM components via oop in javascript. I enjoy programming interactive 2D and 3D games/animations using javascript, three.js, box2d, unity3d and the html5-canvas.
Updated on June 06, 2020Comments
-
Saturnix almost 4 years
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 bybind_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
-
Bill Karwin almost 11 yearsI don't recommend using
double
orfloat
if you need exact scaled numerics. See dev.mysql.com/doc/refman/5.6/en/problems-with-float.html -
Hugo Delsing almost 11 yearsIn this question and answer mysql uses
decimal
. its only the bind param type that is set to double. And itsthe only option from the types given. -
Dane Caswell over 7 yearsYou could just use 's' String. Provided the data type you are using in php to store the value (Decimal) has a __toString method (if not create one) then the PHP with convert the value to string before its passed to the MySQL engine and the engine will perform its own type casts to process the appropriate result for the field type. Can be useful for types like decimal that require more precision than the integer or even double can provide.
-
Hugo Delsing over 7 yearsSure, it would work, but the whole point of binding types is to make sure no other data is provided. Because if you set the bind type to
string
I can also set the value to "I'm not a number and I will break your query" and the code would accept it. Then MySql would throw the error (under normal circumstances, it could be hidden) and you would have no idea why your program is failing.