The difference of 2 rows???

Ivains

Hello Everyone,
I have a problem that I have been trying to figure out for over a month now and I would like to know if someone can get me in the right direction.

What I am doing is every month I input the vehicle monthly mileage. I have it set up to display the date of input and the mileage. I would like it to display the difference of the present input and the prevoius. Example: On 01 Jul 02 mileage was 500 and on 01 Aug 02 the mileage was 600, then the difference displayed would be 100.

How can I go about doing this???

Here's my code as it is now:

$sql = "
SELECT *
FROM mileage, lsv
WHERE mileage.vid = \"$vid\"
AND mileage.vid = lsv.vid
ORDER BY mileage.stop DESC
LIMIT 6
";

$result = @($sql,$connection) or die("Couldn't execute query.");

$the_mileage = "

<table width=375 cellpadding=2 cellspacing=2 border=0>
<tr>
<td bgcolor=#800000 width=125>Date:</td>
<td bgcolor=#800000 width=125>Odometer Reading:</td>
<td bgcolor=#800000 width=125>Monthly Mileage:</td>
</tr>
";

while ($row = mysql_fetch_array($result)) {

$id = $row['id'];
$vid = $row['vid'];
$stopdate = $row['stopdate'];
$date=date('d M Y',strtotime($stopdate));
$stop = $row['stop'];
$reg = $row['reg'];
$sn = $row['sn'];
$org = $row['org'];
$vco_n = $row['vco_n'];
$vco_e = $row['vco_e'];

$the_mileage .= "<tr>

<td bgcolor=#808080 width=125>$date</td>
<td bgcolor=#808080 width=125>$stop</td>
<td bgcolor=#808080 width=125></td>
";

}

$the_mileage .= "</tr></table>";

Any help would be gratefully appreciated.
:o) Ivan

weekender

right. if you want to compare the data for two dates, lets call them $present and $past (i.e. most recent and older).

use the getdate function http://www.php.net/getdate to split the date into an array containing the different parts of the date.

$dateparts = getdate($present);
$day = $dateparts['mday'];
$month = $dateparts['month'];
$year = $dateparts['year'];

and then manipulate $day, $month, $year to give the date of the $past record you want to retrieve. i.e. if $present is 11th March 1981 and you want a date a month before, then use the above code, then

$month = $month - 1;

you will need more calculation than that to handle dates such as 9th January (where the year changes). when you have the date you want, you can recombine it into a unix timestamp using mktime http://www.php.net/mktime

$past = mktime ('0','0','0','$month','$day','$year'
//nb the first three parameters represent hours, minutes and seconds, but we are not that fussy

then do another select query on the database ie

"SELECT mileage FROM tablename WHERE date='$past'"

hope this helps. i'm kinda new at this, and haven't checked it, so it may be buggy.

adam