mysql accumulating total not working beyond 1,000

Orla

//calculate current total
$total=$price*$quantity;

$sql41="select orderid, sessionid, total from orders where sessionid='$sessionid'";
$result41 = mysql_query($sql41) or die(mysql_error()) if (mysql_num_rows($result41) )
{
$totalamt = mysql_result($result41, 0, 'total');
}

//calculate accumulating total
$totalamt=$totalamt+$total;

//update database
$sql7="update orders set total='$totalamt' where orderid='$orderid'";

I have a problem with the above code. a webpage can be revisited to add products to a basket. The product details are recorded in the orderdetails table whilst the overall total is stored in the orders tables

Everyting works fine whilst the numbers are below 1,000
If it goes over 1,000 the database
does the following:

9758.40 + 74.95 = 83.95

It's adding the 74.95 (the last product) and only the 9 from the first product

total is set as a "double" in the database

Any ideas would be gratefully appreciated,
Thanks,
Orla

laserlight

hmm... I dont quite see how that can be the case, but what if you changed the query to do the work for you?

//current total
$total = $price * $quantity;
$query = "UPDATE orders SET total=total+$total WHERE sessionid='$sessionid'";
mysql_query($query)
	OR die(mysql_error());

Orla

tried that, but once again everything is fine when the number is below 1,000

I have 3 products, 839.44 + 22.80+ 254.60

The screen shows the correct value for the accumulated total

        1116.84

but the database shows

This is frustrating!!

Orla

laserlight

Create a new DB table with the same schema for testing purposes.
Create and post the smallest and simplest script that demonstrates the problem you face, using this new DB table.

Stay away from HTML, including forms, and use various test values directly, at critical points.
Standard debugging methods apply, i.e. print values at critical points to observe changes (or lack thereof).
If there is no problem, then double check your form instead.

Oh, and post what webserver, version of PHP and DBMS you are using, as well as the exact table schema.

Roger_Ramjet

What is the data type of the column? Decimal?

What you have here is a problme with the 'precision' specified for the column. Precision means the number of digits before the decimal point.

Best thing is to read the manula and expand the column to allow for larger numbers..

One thing: if your script is doing flaoting point math then you can expect totals not to match when compared to decimal numbers stored in your databse. Can be a real pain. Recomend that you use the bcmath functions in your scripts for adding up money.

Roger_Ramjet

Re-reading your first post I see that the total column is double.

From the manual

In standard SQL, the REAL and DOUBLE PRECISION types do not accept precision specifications. MySQL supports a variant syntax with two numbers given in parentheses following the type name. The first number represents the display width and the second number specifies the number of digits to be stored and displayed following the decimal point.

Stll boils down to a problem with the precision same as it would be with decimal.

laserlight

What is the data type of the column? Decimal?

"total is set as a "double" in the database"

double should take up to 8 bytes, so it doesnt look like the problem.

Recomend that you use the bcmath functions in your scripts for adding up money.

My opinion is that bcmath and other multiple/arbitrary precision maths libraries would probably be overkill where working with integers will do.

Roger_Ramjet

My opinion is that bcmath and other multiple/arbitrary precision maths libraries would probably be overkill where working with integers will do.

What, multiply prices by 100 and store them as intergers in the db, and then divide by 100 when you want to display them on the page as the real price? Have to do the same when you run your accounts on the stored transactions? What happens when you forget to divide by 100 before passing the total to your payment processing system?

No. Money is money. It is decimal. Use decimal maths functions to calculate totals and use decimal column type to store privces and totals in the db. Anythng else is just a set-up for trouble somewhere down the line.

Anything that involves aproximate numeric data types like Float and Double has not place in anything that invloves money. Money is never approximate - just imagine how you would react if you got an 'aproximate' bill, or your bank sent you an aproximate statement.

laserlight

What, multiply prices by 100 and store them as intergers in the db, and then divide by 100 when you want to display them on the page as the real price?

Yes.

What happens when you forget to divide by 100 before passing the total to your payment processing system?

You debug your script.

No. Money is money. It is decimal.

Hardly. Money is exchanged in integer form; as you noted, it "is never approximate".

I do suppose that on the other hand, one can argue that the overkill involved in using arbitrary precision library for money is offset by the fact that bcmath is likely to be available on the system anyway, and it can indeed save some manual calculation that might get missed out, even if that calculation is faster than with manipulating strings.

Roger_Ramjet

I think we are never going to agree on this. Debugging my script after I've charged someone 100 times the price is just too late. It's not when I first set it up that worries me, it's next year when someone modifies it that this kind of thing will happen. It's an inbuilt booby-trap.

Even worse if I get my VAT wrong, Customs and Excise will just come in and take my business away here in the UK.

Decimal may be 'arbitrary' but it is not aproximate. Arbitrary just means that I can select precision and decimal places instead of haveing them enforced by type. Aproximate means that when I add 40 + 60 I get 99.98797 or some such silly result.

laserlight

Decimal may be 'arbitrary' but it is not aproximate. Arbitrary just means that I can select precision and decimal places instead of haveing them enforced by type.

You're right, but that comes at the price of additional calculation.

I can see where you're coming from - tradeoff a minor loss of efficiency for a cleaner code in maintenance.

Roger_Ramjet

Sorry, Orla, laserlight and I seem to have taken over your thread, sorry.

Did anything we said help you to fix your problem?

Weedpacket

Presumably the decimal types are stored as integers (or some other exact type) internally, hence the precision specification. Incidentally, the precision counts significant digits on both sides of the decimal point. It does mean that operations on them ought to be carried out in the database, to avoid any chance of them picking up rounding errors from being converted to floating point and back.

Incidentally, if the precision is too small for the number you're inserting MySQL inserts some other number of its own choosing without warning you. Glory be!

Roger_Ramjet

Originally posted by laserlight
You're right, but that comes at the price of additional calculation.

I can see where you're coming from - tradeoff a minor loss of efficiency for a cleaner code in maintenance.

Code efficiency in general apps is something I stopped worrying about soon after 32-bit microporccessors cam online. They made MIPS so cheap that it freed us to concentrate on the real problem - programmer productivity.

Code reuse requires code that is transparent, generalised and easily modified: all of which will mean reduced execution efficiency in the specific case.

On the other hand, I see a lot of code posted here that is very clever and requires the fewest keystrokes, but that I know is not optimal for runtime execution. Neither is it generalised nor transparent, and very rarely is it 'structured code'.