Need to calaculate new number from old number and put in to different row

[silo23]

I'm very new to this and all the answers and all the searches I've read (which are many) are beyond me, I'm hoping someone can help. I'm more a designer than developer.

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 member_id, miles_total, miles_monthly and date.

What I need to do is this, the member 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.

This is where I've got;

INSERT INTO mileage (id, miles_total, miles_monthly, Date)
SET miles_total = miles_total - previous miles_total (not sure about this bit) AND put in to miles_monthly (or this bit)
WHERE member_id = '2'

Thanks
Si

[r937]

you'll need to do it in two steps

first step retrieves the member's previous miles_total...

SELECT miles_total
  FROM mileage 
 WHERE member_id = 2
ORDER
    BY `date` DESC LIMIT 1

second step inserts the new row, using the value obtained from the previous step

[silo23]

Thanks for the quick response.
I have to admit I don't know how to take the value from the first step or how to run 2 querys.

[silo23]

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.

SELECT miles_total
FROM mileage
WHERE member_id = 2
ORDER
BY `date` DESC LIMIT 1

[Cups]

Do you mean the miles_total is the odometer reading from the vehicle?

ie

mileage
======
id | miles_total | miles_monthly | date
=============================
33 | 10000 | 500  | 2012-04-01
33 | 11000 | 1000 | 2012-05-01

and if at the end of June user 33 entered "12200" and "June 2012" into your form then you would generate the next row

33 | 12200 | 1200 | 2012-06-01

Is that your scenario?

[silo23]

Yes, that's it, that's what I've been trying to explain.

[itmitică]

As part of the validation process, for something against things like the user entering a smaller total of miles for the new month, you should use the query you selected in the #1 and get the value in the webpage, in a visible place: "You have 11000 total miles so far. Please enter the new total:".

As such, you would have this:

INSERT (id input (total_miles_new - total_miles_old) ) into total_miles field and current date into date field

[silo23]

Thanks, very useful.

[Cups]

Great that you got some help there, but to go back to your original posting -- we have your scenario -- thanks for clarifying -- but what exactly is your question then?

Is it "What is the sql I need in order to create a graph showing the last 12 months mileage?" OR is do you need help with the insert statement to get to :

33 | 12200 | 1200 | 2012-06-01

[silo23]

Great that you got some help there, but to go back to your original posting -- we have your scenario -- thanks for clarifying -- but what exactly is your question then?

Is it "What is the sql I need in order to create a graph showing the last 12 months mileage?" OR is do you need help with the insert statement to get to :

33 | 12200 | 1200 | 2012-06-01

Yes, that's what I need to do. Thanks.

[kduv]

If you wanted to offload the "total miles" calculation to the database instead of doing it in PHP and running multiple queries, you could change your insert statement to something like this:

INSERT INTO `MILEAGE` (`id`, `miles_total`, `miles_monthly`) VALUES ($id, (SELECT SUM(miles_monthly) FROM `mileage` `member_id` = $id));

But if you're going to go that route, I would just remove the miles_total field all together. If you want to get the total mileage, just use the SUM function to add each months mileage when you're retrieving the information for whatever report you're pulling.

[silo23]

Hi kduv, I'm very new to all this but what I'm trying to do is get the user to input their reading from their odometer each month and the code does the math and works out how many miles they did that month by taking off the previous months reading.

odometer reading - previous reading = monthly use
12,000 - 11,000 = 1,000

Hope that makes it clearer. Thanks.

[silo23]

Hi kduv, I'm very new to all this but what I'm trying to do is get the user to input their reading from their odometer each month and the code does the math and works out how many miles they did that month by taking off the previous months reading.

odometer reading - previous reading = monthly use
12,000 - 11,000 = 1,000

Hope that makes it clearer. Thanks.

And need to save the monthly use back to database.

[kduv]

OK, say your table looks like this:

user_id         monthly_mileage         date
12                201.43                2012-01-01 00:00:00
12                150.98                2012-02-01 00:00:00
12                701.55                2012-03-01 00:00:00
12                54.76                 2012-04-01 00:00:00
12                899.99                2012-05-01 00:00:00

You could add another column to track the running totals, but IMO that would be pointless, and more maintenance in the long run -- especially since you already have all the data there to get what you need.

So with the above mileage table, you could run a query like this:

SET @total := 0;
 
SELECT `user_id`, `monthly_mileage`, 
        (@total := @total + `monthly_mileage`) AS `total_mileage`,
        DATE
FROM `mileage`
WHERE `user_id` = 12
ORDER BY `date` ASC;

And your results would look like this:

| USER_ID | MONTHLY_MILEAGE | TOTAL_MILEAGE |                DATE |
-------------------------------------------------------------------
|      12 |          201.43 |        201.43 | 2012-01-01 00:00:00 |
|      12 |          150.98 |        352.41 | 2012-02-01 00:00:00 |
|      12 |          701.55 |       1053.96 | 2012-03-01 00:00:00 |
|      12 |           54.76 |       1108.72 | 2012-04-01 00:00:00 |
|      12 |          899.99 |       2008.71 | 2012-05-01 00:00:00 |

Now with SQL like that, you can get the running total for any date range VIA the WHERE and the GROUP BY clauses. All this without having to make your table "overly" complex, or having to offload the data logic to PHP.

What are databases for, if not for storing, managing, and retrieving custom datasets in an easier way?

[r937]

kduv, i think you missed the point

the member does not input the monthly total -- the member inputs the odometer reading

one table called mileage with 4 fields member_id, miles_total, miles_monthly and date

only the odometer reading is input by the member each month, and the app is supposed to calculate the monthly total from that minus the previous month's odometer reading

odometer reading - previous reading = monthly use
12,000 - 11,000 = 1,000

what he's having trouble with is retrieving the previous month's reading, and using that to compute the monthly total when inserting the new row for the current month

i would have already showed him how to do that in the original thread (which started out in the database forum and was subsequently moved here to the php forum) except i don't do php

[kduv]

You're right. I did miss the point. I thought it was the other way around. I still think the best way is to do it in the DB instead of offloading it to PHP. If you have MySQL 5+ you can set up a trigger.

Say your table is:

CREATE TABLE `mileage` (
  `user_id` int(11) unsigned DEFAULT NULL,
  `odometer` decimal(8,2) unsigned DEFAULT NULL,
  `monthly_mileage` decimal(8,2) unsigned NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `user_id` (`user_id`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then you can create a trigger that updates the mileage for that month based on the current odometer - the last odometer. Then when you insert a new record, all you have to do is insert user_id, and odometer, and the database will take care of the rest. Then when you go to select records in the table, all the monthly_mileage fields will already be populated.

DELIMITER //
CREATE TRIGGER before_insert_on_mileage BEFORE INSERT ON `mileage` FOR EACH ROW
BEGIN
	SET @last_odometer := (SELECT IFNULL((SELECT `odometer` FROM `mileage` WHERE `user_id` = NEW.`user_id` ORDER BY `date` DESC LIMIT 1), NEW.`odometer`));
	SET NEW.`monthly_mileage` = NEW.`odometer` - @last_odometer;	
END//
DELIMITER ;

If you don't have MySQL 5+, you can do it in PHP, but you don't need to store the monthly data in the DB if you're going to go the PHP route.

<?php

// CODE TO GET RECORDS FROM DATABASE GOES HERE

$rows = '';// array of rows from database results

$i = 0;
foreach ($rows as $row => $columns)
{
    // Set the last odometer reading, or the current one if this is the first
    $last_odometer = ($i == 0) ? $columns['odometer'] : $rows[$row - 1]['odometer'];
    $month_total = $columns['odometer'] - $last_odometer;
    echo "{$columns['date']}: {$month_total} Miles This Month. {$columns['odometer']} Odometer Reading As Of This Month.<br />\t";
    $i++;
}


?>

That's a very basic example of how you "could" do it in PHP. If you needed help with something more specific, let me know.

[silo23]

Hi
Unfortunately my hosting company won't allow me to run triggers.
So I'm trying the PHP route, I've set up a form to input the user_id, the odometer reading and the date, on Submit it loads a page with your PHP code on it, I've added the db connection,

<?php

// CODE TO GET RECORDS FROM DATABASE GOES HERE
$con = mysql_connect("localhost", "username", "password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$db_selected = mysql_select_db("db_name",$con);
$sql = "SELECT `user_id`, `odometer`, `monthly_mileage`, `date` FROM `mileage`";
$result = mysql_query($sql,$con);

But nothing appears on the page and the db doesn't get updated. I've assumed I needed to add something to this line,

 $rows = '';// array of rows from database results

as nothing seemed to happen, so tried this,

$row = '$query->last_row()';
and this,
$row = '$query->row(5)';

Not sure what to do now.

[r937]

like i said earlier, i don't do php, but as far as the sql is concerned, you have this --

$sql = "SELECT `user_id`, `odometer`, `monthly_mileage`, `date` FROM `mileage`";

this returns the entire table, when all you really want is the latest entry for a specific member, as i indicated in post #2

[silo23]

So this should work,

SELECT MAX( `odometer` ) AS  total_miles
FROM mileage
WHERE `user_id`= 1 DESC LIMIT 1

[kduv]

In theory: sure. Give me a minute to look at things.

[silo23]

Hi kduv, for some reason this was throwing up an error,

 foreach ($rows as $row => $columns)

So I commented it out and the monthly_mileage field updated, Hooray!
But the code

 0 Miles This Month. Odometer Reading As Of This Month.

returned zero, which may not be a problem as I just need to get the field in the db updated so I can then output to a chart.

[r937]

So this should work,

SELECT MAX( `odometer` ) AS  total_miles
FROM mileage
WHERE `user_id`= 1 DESC LIMIT 1

well, no, because your table doesn't have a column called odometer

MAX() is guaranteed to return only one value, so LIMIT 1 is redundant

and of course MAX is wrong if the odometer ever gets reset -- the query i posted returns the latest reading based on date

[silo23]

I've been using kduv's db in post 16, so do have a column called odometer, been trying everything to get something to work, I'll adapt it later, do agree LIMIT 1 is redundant, not going to let anyone reset their odometer. ; )

[Cups]

Most modern cars include a trip meter (trip odometer). Unlike the odometer, a trip meter is reset at any point in a journey, making it possible to record the distance traveled in any particular journey or part of a journey.

You cannot legally reset an odometer - only a trip odometer (or tripmeter) so lets stop that idea right there.

Good idea to call your table column the thing that it actually represents - same will hold true of all your variables in your script too.

What is the actual output you want your php/mysql script to show and what is the problem you now face?

[r937]

You cannot legally reset an odometer - only a trip odometer (or tripmeter) so lets stop that idea right there.

but let's ~not~ throw out the idea that the member might enter an odometer reading into the web form that happens to have a typo and results in a negative monthly miles calculation