Overall Program Flow Display data entry form get ORIG station, DEST station, ADULTS (1 or 2), START DATE, NUMBER OF DAYS Set CURRENT DAY = 1; CURRENT DATE = START DATE Loop thru days retrieve fares for CURRENT DATE create URL for current day download fares check for Amtrak generated errors if errors unless 3 day a week or already departed display main screen and quit parse fare page store unique options with price in INPUT table if done (current day = NUMBER OF DAYS) if 3 day a week or already departed and no results found display main screen with error message and quit else display connection/accommodation options and quit else Display progress bar add 1 to CURRENT DAY add 1 day to CURRENT DATE restart clock end of loop Display connection/accommodation Options displays unique combinations of connection and accommodation sorted by connections and accommodations along with checkboxes for each CHECK ALL button checks all options UNCHECK ALL button clears all check marks DISPLAY FARES button displays fares for selected options NEW ITINERARY button displays the main data entry screen Display Fares Displays connection, accommodation, and price for each date of the selected options, sorted by date, connection and accommodation Clicking on the date opens the pertinent Amtrak fare page in a separate window BACK TO CONNECTION OPTIONS button re-displays the options screen NEW ITINERARY button displays the main data entry screen Parse fare page -------------------------------------------------------------------------- Database tables: AMCOUNT: one record ID: 1 (not really needed HIT: NUMBER OF REQUESTS DAYS: average days requested per itinerary PROXY: 1 => use proxy; 0 => don't use proxy (obsolete) WATCHDTCOUNT: applies to fare watch TESTFILE; fare page on local drive used to test parse routine URL: if fails to retrieve fare page, Amtrak URL is saved INPUT: 1 record per connection/accommodation per request ID: automatically generated key TIMESTAMP: used by fare watch FOREIGNKEY: unique ID of request (points to fare watch request if applicable) DATE: request date (MYSQL format YYYY-MM-DD) CONNDESC: connection description; e.g., 4 Southwest Chief - 448 Lake Shore Limited ACCOM: accommodation description; eg., Roomette - BC PRICE: total price for number of adults selected DETAIL: connection details; 1 record per segment ID: automatically generated key TIMESTAMP: used by fare watch (3rd normal form?) FOREIGNKEY: unique ID of request (points to fare watch request if applicable) SEG: segment number; 1, 2, etc. CONDESC: connection description; TRAIN: train number and name TIMES: departure and arrival times separated by hyphen STATIONS: departure station and arrival station separated by > (* implies self transfer is involved) CONSIST: List of train type - accommodation combinations; used to determine accommodation combinations for 2 segment itineraries. TYPE: viewliner, superliner, coach, or bus ACCOM: accommodation description. Roomette, Family, Bedroom, Bus, Coach Common Functions: GENERAL (in ../common/functions.php) 1. dtoc(timestamp) converts timestamp to string mm/dd/yr 2. ctod(char date) converts character date such as mm/dd/yr to timestamp 3. DatePlus (char date,days) add days to char date; returns char date; default days = 1. 4. msg(variable) echo variable concatenated with
5. isin(needle,haystack) returns true if needle is in haystack, else false MYSQL (in ../common/mysqli.php) 1. OPENDB: Parameteers - MYSQL server address, user name, password, default database. Returns mysql connection object. Equivalent to mysqli_connect 2. SQL: Parameter - sql statement. Returns SQL result object. Eexecutes sql statement; equivalent to mysql connection object->query(sql statement)) 3. getROW: Parsmeter - SQL result object. Returns result row array. Equivalent to SQL result object->fetch_array(MYSQLI_BOTH) 4. RECCOUNT: Parameter – none. Returns number of rows affected (no. retrieved if SELECT statement). Equivalent to mysql connection object->affected_rows 5. RETREIVE: Parameters – field list, table list, Primary key, Primary key value. Note misspelling. Equivalent to select VAR_LIST from TABLE_LISTS where PRIMARY KEY=PRIMARY KEY VALUE extract(retrieve(...) makes variables available E.G., extract(retrieve('name, address, phone','customer','Id',5435)) makes $name, $address, $phone for customer with Id=5435 available 5a. RETREIVE: Parameters – field list, table list, criteria. Equivalent to select VAR_LIST from TABLE_LISTS where criteria extract(retrieve(...) makes variables available from first row, if more than one satisfy CRITERIA E.g., extract(retrieve('name, address, phone','customer',”state='IL'”)) makes $name, $address, $phone for customer for the first customer from Illinois available.