Column Total!

[bart]

Hi,

I am retrieving information from my MYSQL database and one of the columns is a price field. The data in the database is to the format of £0 or £0.00 (pound sign included).

I am using the while loop to put the data into a HTML table.

How can I now get the total amount from this field at the end of the table?

[dannys]

If you hadn't included the £ sign it would have been a simple query - "SELECT SUM(price) as total FROM catalogue"

I'd suggest rethinking your DB - wouldn't it be much easier to add the £ to the data after you get it out of the db - in the case of multiple currencies you could have an extra field ('currency') in the table to distinguish between them.

[bart]

Can't really, staff enter the amount and will always enter the £. Even if I put a remark asking them not to, which will also cause me problems.

[swallace]

Well, you could use php to remove the £ sign, then build up a running total inside your while statement.

[bart]

Sorry to be a bit thick, but how do I do that? (I'm still kinda new to all this PHP!)

[swallace]

For example:

$query = "SELECT Price FROM MYTable";
$result = mysql_query($query);

$total = 0;

while ($row = mysql_fetch_array($result))
{
$price = str_replace("£", "", $row[Price]);
$total += $price;
}

echo $total;

There are other functions that you could use to take the £ sign off...but that's one of them.

[yelvington]

Bart, swallace is right. You should be examining, validating, and (if necessary) cleaning up the user input BEFORE it is stored in the database. The data table should be numeric. That lets SQL perform math functions. It also makes storage smaller and faster.

When you make your data-input form, include the pound-sterling sign before the input field. That gives users a clue not to type it again. And you can write client-side Javascript that raises an objection if the user tries to insert a non-numeric character.