CODEWITHSUNDEEP
phpmysqlsql-server
Jay-oh
Jay-oh

Reputation: 456

VARCHAR SQL contains numbers how to get 2 decimals after comma

I'm having some trouble getting the price to show correct on my website. Currently i have a row VerkoopPP40 which is a VARCHAR input. In this row there is a price e.g. 89,5 or just 9. When I try to get these values it does some unexpected things.

**** Update ****

I've 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 place right now. It's something I guess... Anyone got an idea to move the comma and remove a 0.

**** End Update ****

And echo-ed it like this: 

<td><p>vanaf " . formatNumber($number, [
    'numOfDecimals' => 2,
    'decimalSeparator' => ',',
    'thousandSeparator' => '&nbsp;'
], ',.') . " p.p. <small>Excl btw</small></p></td>

If I just echo the VerkoopPP40 row it returns: €89,5 or €9.

So I googled around some and found this:

$var = $row["VerkoopPP40"];
$var = ltrim($var, '0');
$foo = $var;
$prijzen = number_format($foo, 2, ',', '');

This turns the . into a ,. But also returns €9,00 for the row that has 9 in it. But the strange thing is the row that has 89.5 in it 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 just €9,00 and €89,50 respectively.

I tried the following codes as well:

SELECT ROUND(VerkoopPP40,2) AS RoundedPrice

As database query. That didn't work.

$prijzen = CAST($prijzen as decimal(2,2));

Also didn't work. Any more ideas?

Upvotes: 2

Views: 813

Answers (2)

Gert
Gert

Reputation: 370

you should use number_format but in the right way let me explain it to you you tried this 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(the input number, decimal places, 'the divider between whole numbers and decimals', '')

Upvotes: 1

OldPadawan
OldPadawan

Reputation: 1251

Don't know if this will help you, but found in the comments of the PHP doc : "To prevent the rounding that occurs when next digit after last significant decimal is 5 (mentioned by several people)..." read more

$num1 = "89,5";
$num2 = str_replace(',', '.', $num1);

$price = number_format($num2, 2, '.', '');

echo"[ $price ]";

Upvotes: 1

Related Questions