Page 1 of 1

calculated fields in mysql

Posted: Wed Apr 11, 2007 6:56 am
by php4user2007
Hi,

I'm trying to create some basic customer account functionality (not enough to warrant an open-source shopping cart solution) and I was wondering if MySQL can have automtically calculated fields.

For instance for each customer account, if I have a few fields with the cost of the customer's purchased items and a few fields with payments the customer made, can I have a field in MySQL that will always contain the balance between the two stored in the database. That way whenever I want to echo or display the balance to the customer, I don't need to extract and then sum up and subtract the indidividual items in my php code.

MS Access easily allows for such functionality, so I assume MySQL has a similar feature.??

thanks

p

Posted: Wed Apr 11, 2007 7:14 am
by Begby
For a table to be normalized, all the values in a table should only depend on the key and not on a non-key field. So having a running total that is based on the sum of other values in that table is a violation, not to mention a maintenance headache.

The solution is to use SQL aggregate functions like

Code: Select all

SELECT SUM(i.itemPrice)
FROM tblCust c 
LEFT JOIN tblItems i
ON c.custID = i.custID
GROUP BY c.custID
You could also create a view or a stored procedure. I believe the method you describe in access uses aggregate functions in a query as above.

Posted: Wed Apr 11, 2007 7:18 am
by mikeq
it is bad practice to store calculated values in an operational database (this is different from data warehouses/data marts).

You dont need to do the summing/adding etc in PHP, do it as part of your query

for example

Code: Select all

SELECT customerid, SUM(items_purchased)-SUM(payments_made) AS balance FROM mytable GROUP BY customerid