如何格式化为货币[重复]

Question

This question already has an answer here:

• mysql select int as currency or convert int to currency format? 1 answer
• Print Currency Number Format in PHP 6 answers

Having a lot of problems, I am using SUM on a field in my table and it works but only with one decimal place, I need it to be in currency format, I have tried the below code and others but nothing

$query = "SELECT purchase concat('$', sum(purchase), 2) from dtable"; 

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

// Print out result
while($row = mysql_fetch_array($result)){
    echo "Total ". $row['SUM(purchase)'];
    echo "<br />";
}

</div>

Answer 1

First off, please don't use the mysql* functions as they are deprecated. Move to the mysqli* functions. See the PHP Docs for more details.

How about this:

SELECT CONCAT('$', ROUND(SUM(`purchase`), 2)) AS `result` 
FROM `dtable`

CONCAT just concatenates fields together so, in your case, you would end up with $###2. You need a function (like ROUND) to format the number as you want. ROUND take the number of desired decimal places as the first argument. More on ROUND (and other MySQL math functions) here: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_round

You could also do this all in PHP using number_format.

Change

$query = "SELECT purchase concat('$', sum(purchase), 2) from dtable";
....     
echo "Total ". $row['SUM(purchase)'];

to

$query = "SELECT SUM(`purchase`) AS `sumOfPurchase` FROM `dtable`"; 
....
echo 'Total $' . number_format($row['sumOfPurchase'], 2);