"Waybill Number", CookedDate => "Waybill Date", ReportingMarks => "Car Reporting Marks", CarNumber => "Car Number", CarType => "Car Type", SPCarType => "SP Car Type Code", Shipper => "Shipper Name", OriginCity => "Origin City", OriginState => "Origin State", Consignee => "Consignee Name", DestinationCity => "Destination City", DestinationState => "Destination State", CarLoad => "Car Load", STCCNumber => "STCC Number", HazMatClass => "HazMat Class", PreviousLoadIfEmpty => "Previous Load If Empty", Routing => "Routing", ScaleWeightGrossInLbs => "Gross Weight (lbs)", ScaleWeightNetInLbs => "Net Weight (lbs)", WeighedAt => "Weighed At", OtherNotes => "Other Notes" ); // Get the "sort by", "starting with", and "reverse" data from the URL $sortBy = $_GET["sb"]; if ($sortBy == "")$sortBy = "notSorted"; $startingWith = $_GET["sw"]; if ($startingWith == "") $startingWith = 0; $reverse = $_GET["rv"]; if ($reverse == "") $reverse = "n"; // Connect to the Railroading database $connection = mysql_connect("$hostname", "$username", "$password") or die("Can't connect"); $db = mysql_selectdb("Railroading", $connection) or die("Can't select database"); // XXX - Some day we'll let the user decide how many rows to show // at a time. For now it's 50. $rowsPerPage = 50; // Get the rows ordered by the selected field, but ignore rows // where that field is empty if ($sortBy == "notSorted") $query = "SELECT * FROM waybills"; else $query = "SELECT * FROM waybills WHERE $sortBy IS NOT NULL " . "ORDER BY $sortBy"; $result = mysql_query($query) or die("Select failed: " . mysql_error() . "\n"); $resultRows = mysql_num_rows($result); // Figure out which item numbers to get this time and next time $newStartingWith = $startingWith + $rowsPerPage; $endingWith = $newStartingWith - 1; $rowsNextPage = $resultRows - $newStartingWith; if ($rowsNextPage > $rowsPerPage) $rowsNextPage = $rowsPerPage; if ($endingWith >= $resultRows) { $endingWith = $resultRows - 1; $newStartingWith = -1; } // Databases count from 0, people count from 1 $firstRow = $startingWith + 1; $lastRow = $endingWith + 1; // Start the body print << Waybills
EOT; if ($sortBy != "notSorted") print("Sorted by $colName[$sortBy]
\n"); print("Results $firstRow - $lastRow of $resultRows\n"); if (($sortBy != "notSorted") && ($resultRows < 855)) print("
(waybills with blank $colName[$sortBy] not shown)\n"); print <<

EOT; // Offer "back to the top" if we aren't there if (($startingWith != 0) || ($sortBy != "notSorted")) print("<-- Top\n"); // Offer the next page if we aren't at the end if ($newStartingWith != -1) print("   next $rowsNextPage -->\n"); // Print the column headers $bgcolor = "bgcolor=\"#cccccc\""; print <<

EOT; // Print the data for ($i=$startingWith; $i<=$endingWith; $i++) { // while ($waybillRow = mysql_fetch_object($result)) { if (!mysql_data_seek($result, $i)) { print("Can't seek to row $i: " . mysql_error() . "\n"); continue; } if (!($waybillRow = mysql_fetch_object($result))) continue; // Alternate the colors of the rows if ($i%2) $bgcolor = "bgcolor=\"#d3def5\""; else $bgcolor = "bgcolor=\"#dbeaf7\""; // Extract the data and pretty it up some $WaybillNumber = $waybillRow->WaybillNumber; if ($WaybillNumber == NULL) $WaybillNumber = " "; $cookedDate = $waybillRow->CookedDate; if ($cookedDate == NULL) { $displayDate = " "; } else { list($year, $month, $day) = explode("-", $cookedDate); $displayDate = "$month/$day/$year"; } $ReportingMarks = $waybillRow->ReportingMarks; if ($ReportingMarks == NULL) $ReportingMarks = " "; $CarNumber = $waybillRow->CarNumber; if ($CarNumber == NULL) $CarNumber = " "; $CarType = $waybillRow->CarType; if ($CarType == NULL) $CarType = " "; $SPCarType = $waybillRow->SPCarType; if ($SPCarType == NULL) $SPCarType = " "; $Shipper = $waybillRow->Shipper; if ($Shipper == NULL) $Shipper = " "; $OriginCity = $waybillRow->OriginCity; if ($OriginCity == NULL) $OriginCity = " "; $OriginState = $waybillRow->OriginState; if ($OriginState == NULL) $OriginState = " "; $Consignee = $waybillRow->Consignee; if ($Consignee == NULL) $Consignee = " "; $DestinationCity = $waybillRow->DestinationCity; if ($DestinationCity == NULL) $DestinationCity = " "; $DestinationState = $waybillRow->DestinationState; if ($DestinationState == NULL) $DestinationState = " "; $CarLoad = $waybillRow->CarLoad; if ($CarLoad == NULL) $CarLoad = " "; $STCCNumber = $waybillRow->STCCNumber; if ($STCCNumber == NULL) $STCCNumber = " "; $HazMatClass = $waybillRow->HazMatClass; if ($HazMatClass == NULL) $HazMatClass = " "; $PreviousLoadIfEmpty = $waybillRow->PreviousLoadIfEmpty; if ($PreviousLoadIfEmpty == NULL) $PreviousLoadIfEmpty = " "; $Routing = $waybillRow->Routing; if ($Routing == NULL) $Routing = " "; $ScaleWeightGrossInLbs = $waybillRow->ScaleWeightGrossInLbs; if ($ScaleWeightGrossInLbs == NULL) $ScaleWeightGrossInLbs = " "; if ($ScaleWeightGrossInLbs == 0) $ScaleWeightGrossInLbs = " "; $ScaleWeightNetInLbs = $waybillRow->ScaleWeightNetInLbs; if ($ScaleWeightNetInLbs == NULL) $ScaleWeightNetInLbs = " "; if ($ScaleWeightNetInLbs == 0) $ScaleWeightNetInLbs = " "; $WeighedAt = $waybillRow->WeighedAt; if ($WeighedAt == NULL) $WeighedAt = " "; $OtherNotes = $waybillRow->OtherNotes; if ($OtherNotes == NULL) $OtherNotes = " "; // Print the row print << EOT; } mysql_free_result($result); mysql_close($connection); // Finish off the table print("
Click on the header of any column to sort by that column.
Waybill Car Shipper Consignee Load Routing Weight Other Notes
Number Date Reporting Marks Number Type (Generic) Type (SP) Name City State Name City State Contents STCC Number HazMat Class Previous Load (if empty) Gross (lbs) Net (lbs) Weighed At
$WaybillNumber $displayDate $ReportingMarks $CarNumber $CarType $SPCarType $Shipper $OriginCity $OriginState $Consignee $DestinationCity $DestinationState $CarLoad $STCCNumber $HazMatClass $PreviousLoadIfEmpty $Routing $ScaleWeightGrossInLbs $ScaleWeightNetInLbs $WeighedAt $OtherNotes
\n"); print("\n"); print("

\n"); if (($startingWith != 0) || ($sortBy != "notSorted")) print("<-- Top\n"); if ($newStartingWith != -1) print("   next $rowsNextPage -->\n"); ?>

Credits

Chris Butts hand entered 855 actual Southern Pacific waybills from between 1994 and 1996 into Microsoft Excel and posted then in the files area of the Layout Design SIG Yahoo Group. Bob Crispen turned them from that evil, undocumented, proprietary binary format (boo, hiss) into beautiful, open source, plain text SQL (yaaay, wooo), which you can get for yourself here. And here's some sample PHP to get you started on your own web-based database.