-1

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 />";
}
Sam M
  • 4,136
  • 4
  • 29
  • 42
tsharpe
  • 1
  • 1
  • Take a look at the `money_format` function: http://php.net/manual/en/function.money-format.php – Brian Jul 15 '18 at 21:37

2 Answers2

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);
Jason
  • 15,017
  • 23
  • 85
  • 116
0

You can also use

SELECT CONCAT('$',FORMAT(sum(purchase),2)) as purchase from dtable

will give results like $1,234,567.89

Michael
  • 159
  • 10