I want to store occasional decimal values in my MySQL database and display them in my PHP application. Let me explain, what do I mean by occasional decimal values. The numbers are whole numbers at most of the time like an integer. For example, 160 or 170 etc.. But sometimes I need to save values like 98.6.
First I used DECIMAL(4,1) datatype in the database and I found that I alway get numbers like this: 160.0, 170.0 and 98.6 etc.. So, I changed it to FLOAT. On my local environment, I see the numbers as 160, 170, 98.6 etc. on both the PHP application and phpMyAdmin. But on my production environment, I see those numbers like 160, 170, 98.6 etc. on phpMyAdmin. But on PHP application, I see the numbers like 160, 170, 98.599998474121 etc..
Now I need to decide whether switch back to decimal or keep using float. And either way, I need to find a solution to convert the numbers to the format like this: 160, 170 and 98.6 etc..
What do you think is better for this purpose: decimal or float? What is the best way to convert the numbers to whole numbers if the fractional part of the number is zero (while using decimal)? What is the best way to convert float values like 98.599998474121 to 98.6?
Converting the numbers to whole numbers if the fractional part of the number is zero:
<?php
if (fmod($number, 1) == 0) {
$number = intval($number)
}
Is there any better way?
Understand that float is not a precise decimal number but a value that's close to it. The way it's stored is very efficient at the cost of not being exact. Without knowing your exact needs, one possible solution is to store the numbers as floats and round them to one decimal.
// the casts are there just for the demonstration, you probably don't need them
$number = "170";
echo round((float)$number, 1); // gives 170
$number = "98.6";
echo round((float)$number, 1); // gives 98.6
Floats represent integers the way you want them, and they lose precision only when you store certain decimals.