MySQL Forums
Forum List  »  Newbie

Calculating Delta and inserting into permanent table
Posted by: Phil None
Date: December 18, 2014 12:45PM

Hello all. My first post as I am new to mysql (and sql in general). I am creating a database table (tblGas) to track my fuel usage with a column for my odometer reading (structured as colInd, colPdate, colCost, colGallons, colOdo, colOdo2, colDelta).

All inserts are handled through PHP and I only insert colPdate, colCost, colGallons, and colOdo. ColInd is an auto-increment so no need for that to be inserted by the php code and I'd like colOdo2 and colDelta to be automatically calculated.

I have figured out how to create a temporary table and calculate the difference using the following query.

SELECT t1.colInd AS ind1, t2.colInd AS ind2, t1.colOdo AS odo1, t2.colOdo AS odo2, t2.colOdo - t1.colOdo AS delta
FROM tblGas AS t1, tblGas AS t2
WHERE t1.colInd+1 = t2.colInd
ORDER BY t1.colInd

I have a few questions now that I have the delta calculation working.

Is it possible to insert 't2.colOdo' and 'delta' back into the tblGas as colOdo2 and colDelta?

I really am not familiar with sql (or php for that matter) as I'm trying to teach myself. Will I need to somehow integrate this into my PHP insert script so the delta is calculated each time I add a new row or is it possible to just have this run in MySQL each time I add a row?

If anything is unclear (as I'm not sure I am describing this as best I can) or if you need more information please let me know. Thank you in advance!

Options: ReplyQuote


Subject
Written By
Posted
Calculating Delta and inserting into permanent table
December 18, 2014 12:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.