I have a table called 'stn_routes' that holds the following information (there are other entires but only one 'loc' is shown for simplicity and some columns have also been omitted for this reason, ie. notes)
id loc poi Name Route MPMil MPCh Created Updated
1 1 s First Station 01050 127 32 2010-09-18 00:00:00 2010-09-18 00:00:00
2 1 s Second Station 01075 128 34 2010-09-18 00:00:00 2010-09-18 00:00:00
3 1 p Level Crossing 01100 129 67 2010-09-18 00:00:00 2010-09-18 00:00:00
4 1 s Third Station 01125 130 24 2010-09-18 00:00:00 2010-09-18 00:00:00
I have created a page which uses tabs to show different information about the location, one of these is Routes, where I have created a PHP table using the
following information for the Route Reproduction.
The page is called from a menu, each location is identified by 'pid'
Here is the SQL query that I have used to call the information
$locID = $_GET['pid'];
mysql_select_db($database_connLocation, $connLocation);
$query_rsRoute = "SELECT * FROM stn_routes WHERE loc = $locID ORDER By Route ASC");
$rsRoute = mysql_query($query_rsRoute, $connLocation) or die(mysql_error());
$row_rsRoute = mysql_fetch_assoc($rsRoute);
$totalRows_rsRoute = mysql_num_rows($rsRoute);
Using this code on a table. I can reproduce a simple table
<?php if ($totalRows_rsRoute > 0) { // Show if recordset not empty ?>
<table border="0" cellpadding="0" cellspacing="0" id="RepLocation">
<tr><th width="250">Location</th><th width="80">Miles</th><th width="30">Chains</th><th>Notes</th></tr>
<?php do { ?>
<tr>
<td
<?php if ($row_rsRoute['poi'] == 's') { ?>
class="Stn">
<?php } else { ?>
class="Poi" <?php } ?>
<?php echo $row_rsRoute['Name']; ?></td>
<td><?php echo $row_rsRoute['MPMil']; ?></td>
<td><?php echo $row_rsRoute['MPCh']; ?></td>
<td><?php echo $row_rsRoute['notes']; ?></td>
</tr>
<?php } while ($row_rsRoute = mysql_fetch_assoc($rsRoute)); ?>
</table>
<?php } // Show if recordset not empty ?>
<?php if ($totalRows_rsRoute == 0) { // Show if recordset empty ?>
<div>The route of this location is not yet available, can you supply the information required? If you can, please get in touch using the contact form.</div>
<?php } // Show if recordset empty ?>
This results in this
Location Miles Chains Notes
First Station 127 32
Second Station 128 34
Level Crossing 129 67
Third Station 130 24
From here, this is where I need assistance. Rather than show the Miles and Chains allocated to the location, I want the first row to display '0' and '00' and the subsequent rows to be calculated from that point, like this.
Location Miles Chains Notes
First Station 0 00
Second Station 1 02
Level Crossing 2 35
Third Station 2 72
As the overall locations may be extended in the future in either direction, it it always possible in future that a new station may be added. For example, before First Station a New Station may be built at MPMil 125 MPch 79
A new row would be created in stn_routes
id loc poi Name Route MPMil MPCh Created Updated
100 1 s New Station 01025 126 00 2012-09-18 00:00:00 2012-09-18 00:00:00
Using the same rsRoute query above, this php table would be produced
Location Miles Chains Notes
New Station 125 79
First Station 127 32
Second Station 128 34
Level Crossing 129 67
Third Station 130 24
Once again. I want the first row to be set to '0' '00' and subsequent rows calculated from this first row
Location Miles Chains Notes
New Station 0 00
First Station 1 33
Second Station 2 35
Level Crossing 3 68
Third Station 4 25
To make the calculations a little bit more difficult instead of the MPCh values being between 0 and 99, they are actually between 0 and 79 (80 chains to a mile), this needs to be taken into account when substracting or adding values.
Any advice or direction would be much appreciated!
Phil
