VARCHAR SQL contains numbers, how to get 2 decimal places
I'm having trouble getting the price to display correctly on my website. I currently have a string VerkoopPP40
that is the input VARCHAR
. This line has a price, for example. 89,5
or simply 9
. When I try to get these values, it does some unexpected things.
**** Update ****
I just tried this code:
<?php
function formatNumber($number, $format=[], $oldDecimalSeparator=",.·'", $multiplier=1)
{
if ($format) {
$format += ['numOfDecimals' => 0, 'decimalSeparator' => '.', 'thousandSeparator' => '']; # Default format
# Find decimal separator
# The decimal separator is the one that is the last and does not occur more than once
if ($letters = str_replace(' ', '', $number)) { # Replace spaces
if ($letters = preg_replace('/^-/', '', $letters)) { # Remove minus
if ($letters = preg_replace('/[0-9]/', '', $letters)) { # Get all non digits
$lastletter = substr($letters, -1); # Returns last char
if (substr_count($letters, $lastletter) == 1) {
if (strpos($oldDecimalSeparator, $lastletter) !== false)
$oldDecimalSep = $lastletter;
else
return $number;
}
}
}
}
$number = preg_replace('/[^0-9-]/', '', $number); # Remove all non digits except 'minus'
if ($oldDecimalSep)
$number = str_replace($oldDecimalSep, '.', $number); # Format to float
if ($multiplier != 1)
$number = $number * $multiplier;
# Convert float to new format
$number = number_format($number,
$format['numOfDecimals'],
$format['decimalSeparator'],
$format['thousandSeparator']
);
}
return $number;
}
This returns: 9.00 and 895,00
so the comma is in a different location right now. This is what I think ... Someone had an idea to move the comma and remove the 0.
**** End Update ****
And he said it like this:
<td><p>vanaf " . formatNumber($number, [
'numOfDecimals' => 2,
'decimalSeparator' => ',',
'thousandSeparator' => ' '
], ',.') . " p.p. <small>Excl btw</small></p></td>
If I just the echo
string VerkoopPP40
returns: € 89.5 or € 9.
So, I searched some of them and found this:
$var = $row["VerkoopPP40"];
$var = ltrim($var, '0');
$foo = $var;
$prijzen = number_format($foo, 2, ',', '');
This turns .
into ,
. But it also returns € 9.00 for the row with 9
in it. But the odd thing is that the string it does 89.5
now just returns € 89.00. So somewhere in the code it rounds the numbers down.
Does anyone know how to get the price to show only € 9.00 and € 89.50 respectively.
I have also tried the following codes:
SELECT ROUND(VerkoopPP40,2) AS RoundedPrice
Like a database query. It didn't work.
$prijzen = CAST($prijzen as decimal(2,2));
And it didn't work. Any ideas?
source to share
Don't know if this will help you, but found in the comments of the PHP doc: "To prevent rounding that occurs when the next digit after the last significant decimal place is 5 (mentioned by several people) ..." read more
$num1 = "89,5";
$num2 = str_replace(',', '.', $num1);
$price = number_format($num2, 2, '.', '');
echo"[ $price ]";
source to share
you have to use number_format but in the correct order let me explain it to you have you tried it with 89.5
$prijzen = number_format($foo, 2, ',', '');
but this is written for 89.5 not for 89.5
//this will work for you
$var = $row["VerkoopPP40"];
echo 'raw output from database is :'.$var;
$var = $var / 10;
echo 'after this step the number is :'.$var;
$var = number_format($var, 2, '.', '');
echo 'after this step the number is :'.$var;
number_format (input number, decimal places, 'separator between integers and decimal places', '')
source to share