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
calculated fields in mysql
Moderator: General Moderators
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
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.
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
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
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