CODEWITHSUNDEEP
phpmysqldecimal
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25763

handling decimal precision in Mysql or PHP


i have a MySQL table namely estimate_item which contains few columns that holds decimal values with fixed precisions. here is my table structure.

tableName : estimate_item

enter image description here

while retrieving the records using PHP, i do some basic calculations using MySQL's function, the SQL query i use is this.

SELECT 
    COUNT(ei.item_id) as total_item,
    SUM(ei.quantity) as total_quantity,
    SUM(ei.number_of_carton) as total_carton,
    SUM(ei.number_of_carton * ei.cbm_per_carton) as total_cbm,
    SUM(ei.quantity * ei.cost) as total_cost,
    SUM(ei.quantity * ei.rate) as total_rate,
    e.commission_amount,
    SUM(ei.quantity * ei.rate) + e.commission_amount as total_amount
FROM
    ai_estimate e
LEFT JOIN
    ai_estimate_item ei ON ei.estimate_id = e.id
WHERE
    ei.estimate_id = ?

for example the above query returns the result.

+------------+----------------+--------------+-----------+------------+------------+-------------------+--------------+
| total_item | total_quantity | total_carton | total_cbm | total_cost | total_rate | commission_amount | total_amount |
+------------+----------------+--------------+-----------+------------+------------+-------------------+--------------+
|          2 |            120 |          807 | 1122.6440 |     2.7500 |   137.5000 |           1500.00 |    1637.5000 |
+------------+----------------+--------------+-----------+------------+------------+-------------------+--------------+

since the following columns contains 4 precision values total_cbm_total, total_cost, total_rate, total_amount, i want to round up the values of all four columns this way.

a) if value is 1122.6440 then round it to 1122.644

b) if value is 2.7500 then round it to 2.75

c) if value is 137.5000 then round it to 137.50

d) if value is 1200.0000 then round it to 1200.00

i.e i want the values to contain minimum of two precisions, which can go up to 3 or 4 precision depending upon the values.

is there any way i could do this directly in MySQL? or the PHP way?

thanks and regards.

Upvotes: 2

Views: 3598

Answers (3)

Mahn
Mahn

Reputation: 16603

Database side, notice that the number of trailing 0s of the columns cost, rate, and cmb_per_carton are predefined by the table structure, which is in your case 4 0s:

cost DECIMAL 19,4
rate DECIMAL 19,4
cmb_per_carton DECIMAL 19,4

If you do operations with these fields the results are naturally going to have the same trailing 0s.

PHP side here's a simple function I just hacked that will accomplish what you are looking for:

function getDecimalFormatted($value, $minPrecision) {
    $value = (float) $value;
    return ((round((($value - floor($value))*pow(10, 5)))%1000) > 0) ? $value : number_format($value, $minPrecision, '.', '');
}

Usage example:

$value = "1234.71200";
echo getDecimalFormatted($value, 2)."\n<br>";

$value = "1234.70000";
echo getDecimalFormatted($value, 2)."\n<br>";

This outputs:

1234.712
1234.70

Upvotes: 1

Aleks G
Aleks G

Reputation: 57346

One approach would be to use regex to remove all 0 at the end of the number after a decimal place, then format a number with two decimal places and compare two results - return whichever is the longest (string-wise). While this can be done in mysql, it would be much easier in php:

//$input - number to format
//$num - minimum number of decimal places to keep
function reformat($input, $num) {
    $parse = preg_replace('/(\.[1-9]*)0+$/', "$1", $input);
    $withmin = number_format($parse, 2); 
    return strlen($withmin) < strlen($parse) ? $parse : $withmin;
}

Upvotes: 1

LSerni
LSerni

Reputation: 57453

If you do that in MySQL, my guess is that you'd need to overly complicate the query adding a CASE WHEN... END.

So I'd do that in PHP.

function reprecise($value)
{
    $two_digit = number_format($value, 2);
    $three_digit = number_format($value, 3);
    return (float)$two_digit == (float)$three_digit ? $two_digit : $three_digit;
}

This employs a trick - first it calculates the representation with two and three digits, then checks whether these are the same floating number. If they are, then the last digit of the three-digit version must be zero and the two-digit version is used instead.

To extend to 2, 3 or 4 digits, you could do that in a cycle:

function reprecise($value)
{
    $digits = 4;
    for ($rep = number_format($value, $digits--); $digits >= 2; $digits--)
    {
            $new = number_format($value, $digits);
            if ($new != $rep)
                    break;
            $rep = $new;
    }
    return $rep;
}

or, since string functions are very likely to be faster than number_format:

function reprecise($value)
{
    $digits = 9;
    $base   = strrev(number_format($value, $digits));
    for ($i = 0; $i < $digits-2; $i++)
            if ($base[$i] != '0')
                    break;
    return strrev(substr($base, $i));
}

Upvotes: 1

Related Questions