Problems displaying currency values correcly.

[petrosky]

Hi,

I have checked the web and here and can't seem to find my exact problem listed.

I have MYSQL ver 5.0.45 & PHP ver 5.2.4.

I am running a simple snippet of code below to gather data stored as decimal(10,2)

Running the query in MYSQL or phpMyadmin will return only one Sale price, correctly formatted as 12402.58 from my data.
The code below outputs 12403

$sql = "select Sale from translog WHERE Sale > 9000 order by sale desc";

$result = mysql_query($sql);


while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
    printf("Sale: %s", $row[0] . "<br>");
}

mysql_free_result($result);

With the following code the page displays the value as 12402.00.

$sql = "select Sale from translog WHERE Sale > 9000 order by sale desc";

$result = mysql_query($sql);


while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
    printf("Sale: %s", number_format($row[0],2) . "<br>");
}

mysql_free_result($result);

I am a PHP/MYSQL newbie but I need the currency/decimal values to be displayed. I have also tried the following which also rounds up to the nearest dollar.

$sql = "select Sale from translog WHERE Sale > 9000 order by sale desc";

$result = mysql_query($sql);


while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
   $formatted = sprintf("%01.2f", $row[0]);
   echo $formatted . "<br>";
}

mysql_free_result($result);

I am hoping it is something simple I have overlooked.

Thanks for any help you can offer.

Regards,

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[jpadie]

Hi Peter

the issue that you are having is the difference between how strings are handled in different circumstance.

personally i would store the values in your database as floats. the implicit rounding involved in the decimal may muck up your VAT calculations (which typically have to be done to more decimal places).

anyway, to display numbers in a currency format, php provides a handy function called money_format().

something like this should work for you.

$sql = "select Sale from translog WHERE Sale > 9000 order by sale desc";

$result = mysql_query($sql) or die(mysql_error());

setlocale(LC_MONETARY, 'en_GB'); //for GB currency display
$format = 'Sales: %i <br/>';

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
   echo money_format($format, $row[0]);
}

 

[petrosky]

Hi jpadie,

I am sorry to have wasted your time...

I had a config file in the site pointing to an old test database that was converted from MS Access.

PHP was already doing the right thing...I was just pointing it at the wrong data.

I also found that the money_format() function doesn't like windows servers.

Thanks again,

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[jpadie]

never a waste of time. the thread is of value as it may help others with similar issues.

you are right in saying that money_format does not play well with windows (you didn't say you were on a windows platform, I think). you can achieve something similar with number_format and/or printf() however.