Calculating a Grand Total - always one step behind

jimbo2k

Hello,

I'm having some trouble coding my PHP administration application. I want to be able to log customer orders, and payments made by our customers, in a browser-based admin app.

A key part of this is the ability to calculate the grand total of their order, and compare this against payments made, to see if they still owe money.

Rather than use an SQL query every time I want to calculate a grand total, I want to save the grand total in the Orders table alongside the order ID, date etc...

Let's simplify and say I have two tables:

--Orders--
Order ID (PK)
Cust ID (FK)
Date

Grand Total

--Line Items--
Line ID (PK)
Order ID (FK)
Model

Price

I insert line items with a simple HTML form.

In a seperate form, I manually set the Price of each line item with an SQL UPDATE command. Also in this form is a hidden field, dynamically filled by an SQL query which calculates the grand total of all line items. On form submission, the line item receives its Price, and the form also updates the Grand Total in the Orders table using the hidden field.

The problem: When I set the price, the Grand Total is calculated and set before the Price field is updated. This means it's always one step behind. If I send another UPDATE command without changing anything, of course this corrects the problem. But it's hardly foolproof.

Hope this makes sense! How can I ensure that Grand Total is always accurate, without having to submit forms twice?

laserlight

Just to confirm: this form is restricted to privileged users only, right? If not, you have a security loophole right there where you allow the price and grand total to be provided via the form, even if only in a hidden field.

jimbo2k wrote:
How can I ensure that Grand Total is always accurate, without having to submit forms twice?

I think that using a database trigger may be your best option. That way, you do not have to worry about updating the column in PHP, and the consistency of the grand totals would be guaranteed.

Presumably, this trigger (or triggers) would fire after every insert/delete operation on the line items table, as well as after a price field is updated in the line table. The trigger would cause the corresponding grand total field in the orders table to be updated.

jimbo2k

Thanks very much for your quick reply 🙂

I remember reading about triggers a while ago, probably trying to solve this very problem, but I have not actually used a trigger before. I'll have a look on Google for some info.

In the meantime, I can assure you that the page will only be made available to administrators! The customers will, in due course, be presented with a page displaying their current orders and payment status. I would also like to integrate with an online payment system. But opening my app to the public will need a security overhaul anyway I imagine.

The scenario you describe - is this known as 'injection'? Let's say that I open my current app to our customers. They log in, and are presented with a page of their orders and payment status. Assuming I do not provide them with a form of any kind (the order totals are displayed from the mySQL database, but are not editable), is it still possible for them to change the values?

I read that you should use an include file, which enables the source code to reference the database fields instead of using their literal value. Am I on the right track?

laserlight

jimbo2k wrote:
The scenario you describe - is this known as 'injection'?

It could be described as "injection", though not SQL injection but a kind of "data injection".

jimbo2k wrote:
Let's say that I open my current app to our customers. They log in, and are presented with a page of their orders and payment status. Assuming I do not provide them with a form of any kind (the order totals are displayed from the mySQL database, but are not editable), is it still possible for them to change the values?

No, but if the script that displays this information is vulnerable to SQL injection, then it may be possible for them to change these values (or indeed, do anything else that the database user can do).

jimbo2k wrote:
I read that you should use an include file, which enables the source code to reference the database fields instead of using their literal value. Am I on the right track?

I am not sure what you mean. You should use an include file outside of the document root to store the database user credentials instead of hard coding them into the script.

jimbo2k

OK I've researched my triggers, but I'm having some trouble getting the trigger in place. Think it might just be a syntax thing. I'm using mySQL.

Here are my tables:

-- itembasket --

lineid (PK)
ordid(FK)
model

price

-- orders --

ordid (PK)
custid (FK)
date

grandtotal

I have a session variable called sesOrdId. This identifies the ordid that I am applying a payment to.

I want to update the orders.grandtotal field. I need to set a trigger which:

(1) Checks for an update in itembasket.price
(2) Adds up all 'price's in itembasket that are associated with the sesOrdId
(3) Applies this amount to orders.grandtotal

I have looked at the code on the mySQL site, but I can't work out how to build the session variable into it, or how to direct the sum to another table.

Any ideas much appreciated - thanks again so much for your help.

laserlight

jimbo2k wrote:
I have looked at the code on the mySQL site, but I can't work out how to build the session variable into it, or how to direct the sum to another table.

There should be no need to involve PHP at all. The trigger(s) should fire when particular SQL statements are executed, regardless of what script executed them.