Reputation: 25763
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
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
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
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
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