I have an online form which collects members mileage written in PHP, I’ve got a simple MySQL database with one table called mileage with 4 fields id, miles_total, miles_monthly and date.
What I need to do is this, the user submits their total miles for one month which is stored in miles_total and the code takes away the previous months total and stores that in miles_monthly, so that each months mileage can be output to a chart.
INSERT (id input total_miles) into total_miles field and current date into date field
SELECT previous months total_miles and take away from current months total_miles and save in miles_monthly
This works in mysql as part of the code.
WHERE member_id = 2
BY `date` DESC LIMIT 1