simple script for creating visual time tables based on data in gtfs format

timetable.php

<?php

// database credentials
define('DB_HOST', '');
define('DB_USER', '');
define('DB_PASW', '');
define('DB_DATA', '');
define('DB_PREF', '');

// several constants
define('TAG_SYSTEM', 'S');
define('TAG_DATABASE', 'D');

// input parameters
$ref_stop_list 		= isset($_POST['ref_stop_list']) ? explode(';', $_POST['ref_stop_list']) : array();
$ref_trip_id 		= isset($_POST['ref_trip_id']) ? $_POST['ref_trip_id'] : 'DNK-Murgtal-s19-M1';
$ref_route_id		= isset($_POST['ref_route_id']) ? $_POST['ref_route_id'] : 'DNK-Murgtal';
$ref_service_id		= isset($_POST['ref_service_id']) ? $_POST['ref_service_id'] : '';

$opt_table_scheme 	= isset($_POST['opt_table_scheme']) ? $_POST['opt_table_scheme'] : 0;
$opt_display_add_log	= isset($_POST['opt_display_add_log']);

if(empty($ref_trip_id) && count($ref_stop_list) < 1) {
	die("1000");
}

if(empty($ref_route_id) && empty($ref_service_id)) {
	die("1001");
}

if($opt_table_scheme < 0 || $opt_table_scheme > 2) {
	die("2000");
}

// needed utils
$mysql = new mysqli(DB_HOST, DB_USER, DB_PASW, DB_DATA);

$table_stops 			= DB_PREF . 'gtfs_stops';
$table_trips			= DB_PREF . 'gtfs_trips';
$table_stop_times		= DB_PREF . 'gtfs_stop_times';
$table_calendar			= DB_PREF . 'gtfs_calendar';
$table_calendar_dates	= DB_PREF . 'gtfs_calendar_dates';

$alias_services 	= array('', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');

// buffers
$buf_log		= array();
$buf_stops 		= array();
$buf_trips		= array();
$buf_services	= array();

// load stop information
if(!empty($ref_trip_id)) {
	add_log(TAG_SYSTEM, "fetching stop information from trip $ref_trip_id");
	
	$stop_sql = "SELECT `s`.`stop_id`, `s`.`stop_name` FROM `$table_stop_times` `t` LEFT JOIN `$table_stops` `s` ON `t`.`stop_id` = `s`.`stop_id` WHERE `t`.`trip_id` = '$ref_trip_id' ORDER BY `t`.`stop_sequence`";
	$stop_result = $mysql->query($stop_sql);
	add_log(TAG_DATABASE, $stop_sql);
	
	while(($o = $stop_result->fetch_assoc()) != null) {
		array_push($buf_stops, $o);
	}
} else {
	add_log(TAG_SYSTEM, "fetching stop information about (" . count($ref_stop_list) . ") stops");
	
	foreach($ref_stop_list as $ref_stop_id) {
		$stop_sql = "SELECT `s`.`stop_id`, `s`.`stop_name` FROM `$table_stops` `s` WHERE `s`.`stop_id` = '$ref_stop_id';";
		$stop_result = $mysql->query($stop_sql);
		add_log(TAG_DATABASE, $stop_sql);
		
		while(($o = $stop_result->fetch_assoc()) != null) {
			array_push($buf_stops, $o);
		}
	}
}

// load trip information
$trips_sql = null;
if(!empty($ref_service_id)) {
	add_log(TAG_SYSTEM, "fetching trip information for service $ref_service_id");
	$trips_sql = "SELECT DISTINCT `t`.`trip_id`, `t`.`trip_short_name`, `t`.`direction_id` AS `trip_direction`, `t`.`service_id` FROM `$table_trips` `t` LEFT JOIN `$table_stop_times` `s` ON `s`.`trip_id` = `t`.`trip_id` WHERE `t`.`service_id` = '$ref_service_id' ORDER BY `s`.`stop_sequence`, `s`.`departure_time`;";
} else {
	add_log(TAG_SYSTEM, "fetching trip information for route $ref_route_id");
	$trips_sql = "SELECT DISTINCT `t`.`trip_id`, `t`.`trip_short_name`, `t`.`direction_id` AS `trip_direction`, `t`.`service_id` FROM `$table_trips` `t` LEFT JOIN `$table_stop_times` `s` ON `s`.`trip_id` = `t`.`trip_id` WHERE `t`.`route_id` = '$ref_route_id' ORDER BY `s`.`stop_sequence`, `s`.`departure_time`;";
	
	$services_sql = "SELECT DISTINCT `t`.`service_id` FROM `$table_trips` `t` WHERE `t`.`route_id` = '$ref_route_id';";
	$services_result = $mysql->query($services_sql);
	add_log(TAG_DATABASE, $services_sql);
	
	while(($o = $services_result->fetch_assoc()) != null) {
		$alias = array_shift($alias_services);
		array_push($buf_services, array('service_id' => $o['service_id'], 'alias' => $alias));
	}
}

// load service information
if(count($buf_services) > 0) {
	for($s = 0; $s < count($buf_services); $s++) {
		$service_period_text 	= "";
		$service_exception_text = "";
	
		$service_period_sql = "SELECT * FROM `$table_calendar` WHERE `service_id` = '" . $buf_services[$s]['service_id'] . "'";
		$service_period_result = $mysql->query($service_period_sql);
		add_log(TAG_DATABASE, $service_period_sql);
		
		if($service_period_result->num_rows > 0) {
			$service_period = $service_period_result->fetch_assoc();
			$service_days 	= array();
			
			if($service_period['monday'] == '1') { array_push($service_days, "Mo"); }
			if($service_period['tuesday'] == '1') { array_push($service_days, "Di"); }
			if($service_period['wednesday'] == '1') { array_push($service_days, "Mi"); }
			if($service_period['thursday'] == '1') { array_push($service_days, "Do"); }
			if($service_period['friday'] == '1') { array_push($service_days, "Fr"); }
			if($service_period['saturday'] == '1') { array_push($service_days, "Sa"); }
			if($service_period['sunday'] == '1') { array_push($service_days, "So"); }
			
			$service_period_text = implode(', ', $service_days);
			
			$service_period_begin = date('d.m.Y', strtotime($service_period['start_date']));
			$service_period_end = date('d.m.Y', strtotime($service_period['end_date']));
			
			$service_period_text .= " von $service_period_begin bis $service_period_end";
		}
		
		$service_exception_sql = "SELECT * FROM `$table_calendar_dates` WHERE `service_id` = '" . $buf_services[$s]['service_id'] . "'";
		$service_exception_result = $mysql->query($service_exception_sql);
		add_log(TAG_DATABASE, $service_exception_sql);
		
		if($service_exception_result->num_rows > 0) {
			$exceptionals 	= array();
			$additionals	= array();
		
			while(($e = $service_exception_result->fetch_assoc()) != null) {
				if($e['exception_type'] == '2') {
					array_push($exceptionals, date('d.m.Y', strtotime($e['date'])));
				} else {
					array_push($additionals, date('d.m.Y', strtotime($e['date'])));
				}
			}
			
			if(count($additionals) > 0) {
				$service_exception_text = " am " . implode(', ', $additionals);
			}
			
			if(count($exceptionals) > 0) {
				$service_exception_text .= " außer am " . implode(', ', $exceptionals);
			}
		}
		
		$buf_services[$s]['text'] = (!empty($buf_services[$s]['alias']) ?  $buf_services[$s]['alias'] . ' - ' : '') . $service_period_text . $service_exception_text;
	}
}

$trips_result = $mysql->query($trips_sql);
add_log(TAG_DATABASE, $trips_sql);
	
while(($o = $trips_result->fetch_assoc()) != null) {
	array_push($buf_trips, $o);
}

// user interface
echo '<form action="index.php" method="POST">';
echo '<fieldset>';
echo '<legend>Eingabeparameter</legend>';
echo '<label for="edtRefStopList">Haltefolge</label> ';
echo '<input type="text" name="ref_stop_list" id="edtRefStopList" size="80" value="' . (isset($_POST['ref_stop_list']) ? htmlspecialchars($_POST['ref_stop_list']) : "") . '" /> <b>oder</b> ';
echo '<label for="edtRefTripId">Trip-ID</label> ';
echo '<input type="text" name="ref_trip_id" id="edtRefTripId" value="' . (isset($_POST['ref_trip_id']) ? htmlspecialchars($_POST['ref_trip_id']) : "DNK-Murgtal-s19-M1") . '" /><br /><br />';
echo '<label for="edtRefRouteId">Route-ID</label> ';
echo '<input type="text" name="ref_route_id" id="edtRefRouteId" value="' . (isset($_POST['ref_route_id']) ? htmlspecialchars($_POST['ref_route_id']) : "DNK-Murgtal") . '" /><br /><br />';
echo '<label for="edtRefServiceId">Service-ID</label> ';
echo '<input type="text" name="ref_service_id" id="edtRefServiceId" value="' . (isset($_POST['ref_service_id']) ? htmlspecialchars($_POST['ref_service_id']) : "") . '" /><br />';
echo '</fieldset><br />';
echo '<fieldset>';
echo '<legend>Anzeigeoptionen</legend>';
echo '<label for="slcOptTableScheme">Schema</label> ';
echo '<select id="slcOptTableScheme" name="opt_table_scheme">';
echo '<option value="0"' . ((isset($_POST['opt_table_scheme']) && $_POST['opt_table_scheme'] == '0') ? ' selected="selected"' : '') . '>Schema 1</option>';
echo '<option value="1"' . ((isset($_POST['opt_table_scheme']) && $_POST['opt_table_scheme'] == '1') ? ' selected="selected"' : '') . '>Schema 2</option>';
echo '<option value="2"' . ((isset($_POST['opt_table_scheme']) && $_POST['opt_table_scheme'] == '2') ? ' selected="selected"' : '') . '>Schema 3</option></select><br /><br />';
echo '<input type="checkbox" name="opt_display_add_log" id="cbxOptLogOutput"' . (isset($_POST['opt_display_add_log']) ? ' checked="checked"' : '') . ' />';
echo '<label for="cbxOptLogOutput">Log-Ausgabe</label> ';
echo '</fieldset><br />';
echo '<input type="submit" value="Laden" /> <input type="reset" value="Zuruecksetzen" />';
echo '</form><br />';

// build time table
switch($opt_table_scheme) {
	case 0:
		add_log(TAG_SYSTEM, "creating table schema 1");
		table_schema_1();
		break;
		
	case 1:
		add_log(TAG_SYSTEM, "creating table schema 2");
		table_schema_2();
		break;
		
	case 2:
		add_log(TAG_SYSTEM, "creating table schema 3");
		table_schema_3();
		break;
}

if($opt_display_add_log) {
	echo '<textarea rows="25" readonly="readonly" style="font-family:Courier New;background:white;width:100%;">' . implode("\n", $buf_log) . '</textarea>';
}

// functions
function add_log($tag, $message) {
	global $buf_log;
	
	array_push($buf_log, date('d.m.Y H:i:s', time()) . ":\t" . $tag . " - " . $message);
}

function fetch_stop_time($trip_id, $stop_id) {
	global $mysql, $table_stop_times;

	$stop_times_sql = "SELECT `t`.`arrival_time`, `t`.`departure_time` FROM `$table_stop_times` `t` WHERE `t`.`trip_id` = '" . $trip_id . "' AND `t`.`stop_id` = '" . $stop_id . "' LIMIT 1";
	$stop_times_result = $mysql->query($stop_times_sql);
	add_log(TAG_DATABASE, $stop_times_sql);
			
	if($stop_times_result->num_rows > 0) {
		$o = $stop_times_result->fetch_assoc();
		$arrival_time = date('H:i', strtotime($o['arrival_time']));
		$departure_time = date('H:i', strtotime($o['departure_time']));
				
		if($arrival_time == $departure_time) {
			return $departure_time;
		} else {
			return $arrival_time . '<br />' . $departure_time;
		}
	} else {
		return '--';
	}
}

function service_alias($service_id) {
	global $buf_services;
	
	foreach($buf_services as $service) {
		if($service['service_id'] == $service_id) {
			return $service['alias'];
		}
	}
	
	return '';
}

function table_schema_1() {
	global $buf_stops, $buf_trips, $buf_services;

	echo '<table border="1" cellpadding="10" cellspacing="0">';
	
	// headline: trip names
	echo '<tr>';
	echo '<td></td>';
	for($t = 0; $t < count($buf_trips); $t++) {
		echo '<td>' . $buf_trips[$t]['trip_short_name'] . '</td>';
	}
	echo '</tr>';
	
	// headline: trip directions
	echo '<tr>';
	echo '<td></td>';
	for($t = 0; $t < count($buf_trips); $t++) {
		echo '<td>' . (($buf_trips[$t]['trip_direction'] == '0') ? 'H' : 'R') . '</td>';
	}
	echo '</tr>';
	
	// headline: trip services
	if(count($buf_services) > 0) {
		echo '<tr>';
		echo '<td></td>';
		for($t = 0; $t < count($buf_trips); $t++) {
			echo '<td><small>' . service_alias($buf_trips[$t]['service_id']) . '</small></td>';
		}
		echo '</tr>';
	}
	
	for($s = 0; $s < count($buf_stops); $s++) {
		echo '<tr>';
		echo '<td>' . $buf_stops[$s]['stop_name'] . '</td>';
		
		for($t = 0; $t < count($buf_trips); $t++) {
			echo '<td>' . fetch_stop_time($buf_trips[$t]['trip_id'], $buf_stops[$s]['stop_id']) . '</td>';
		}
		
		echo '</tr>';
	}
	
	echo '</table>';
	
	// display service information
	if(count($buf_services) > 0) {
		for($s = 0; $s < count($buf_services); $s++) {
			echo '<p>' . $buf_services[$s]['text'] . '</p>';
		}
	}
}

function table_schema_2() {
	global $buf_stops, $buf_trips, $buf_services;

	// outbound direction
	echo '<table border="1" cellpadding="10" cellspacing="0">';
	
	// headline: trip names
	echo '<tr>';
	echo '<td></td>';
	for($t = 0; $t < count($buf_trips); $t++) {
		if($buf_trips[$t]['trip_direction'] == '0') {
			echo '<td>' . $buf_trips[$t]['trip_short_name'] . '</td>';
		}
	}
	echo '</tr>';
	
	// headline: trip services
	if(count($buf_services) > 0) {
		echo '<tr>';
		echo '<td></td>';
		for($t = 0; $t < count($buf_trips); $t++) {
			if($buf_trips[$t]['trip_direction'] == '0') {
				echo '<td><small>' . service_alias($buf_trips[$t]['service_id']) . '</small></td>';
			}
		}
		echo '</tr>';
	}
	
	for($s = 0; $s < count($buf_stops); $s++) {
		echo '<tr>';
		echo '<td>' . $buf_stops[$s]['stop_name'] . '</td>';
		
		for($t = 0; $t < count($buf_trips); $t++) {
			if($buf_trips[$t]['trip_direction'] != '0') {
				continue;
			}
		
			echo '<td>' . fetch_stop_time($buf_trips[$t]['trip_id'], $buf_stops[$s]['stop_id']) . '</td>';
		}
		
		echo '</tr>';
	}
	
	echo '</table><br /><br />';
	
	// inbound direction
	
	// reverse stop list before continuing
	$buf_stops = array_reverse($buf_stops);
	
	echo '<table border="1" cellpadding="10" cellspacing="0">';
	
	// headline: trip names
	echo '<tr>';
	echo '<td></td>';
	for($t = 0; $t < count($buf_trips); $t++) {
		if($buf_trips[$t]['trip_direction'] == '1') {
			echo '<td>' . $buf_trips[$t]['trip_short_name'] . '</td>';
		}
	}
	echo '</tr>';
	
	// headline: trip services
	if(count($buf_services) > 0) {
		echo '<tr>';
		echo '<td></td>';
		for($t = 0; $t < count($buf_trips); $t++) {
			if($buf_trips[$t]['trip_direction'] == '1') {
				echo '<td><small>' . service_alias($buf_trips[$t]['service_id']) . '</small></td>';
			}
		}
		echo '</tr>';
	}
	
	for($s = 0; $s < count($buf_stops); $s++) {
		echo '<tr>';
		echo '<td>' . $buf_stops[$s]['stop_name'] . '</td>';
		
		for($t = 0; $t < count($buf_trips); $t++) {
			if($buf_trips[$t]['trip_direction'] != '1') {
				continue;
			}
		
			echo '<td>' . fetch_stop_time($buf_trips[$t]['trip_id'], $buf_stops[$s]['stop_id']) . '</td>';
		}
		
		echo '</tr>';
	}
	
	echo '</table>';
	
	// display service information
	if(count($buf_services) > 0) {
		for($s = 0; $s < count($buf_services); $s++) {
			echo '<p>' . $buf_services[$s]['text'] . '</p>';
		}
	}
}

function table_schema_3() {
	global $buf_stops, $buf_trips, $buf_services;

	// outbound direction
	echo '<table border="1" cellpadding="10" cellspacing="0">';
	
	// headline: trip names
	echo '<tr>';
	for($t = 0; $t < count($buf_trips); $t++) {
		if($buf_trips[$t]['trip_direction'] == '0') {
			echo '<td>' . $buf_trips[$t]['trip_short_name'] . '</td>';
		}
	}
	
	echo '<td></td>';
	
	for($t = 0; $t < count($buf_trips); $t++) {
		if($buf_trips[$t]['trip_direction'] == '1') {
			echo '<td>' . $buf_trips[$t]['trip_short_name'] . '</td>';
		}
	}
	echo '</tr>';
		
	// headline: trip services
	if(count($buf_services) > 0) {
		echo '<tr>';
		for($t = 0; $t < count($buf_trips); $t++) {
			if($buf_trips[$t]['trip_direction'] == '0') {
				echo '<td><small>' . service_alias($buf_trips[$t]['service_id']) . '</small></td>';
			}
		}
	
		echo '<td></td>';
	
		for($t = 0; $t < count($buf_trips); $t++) {
			if($buf_trips[$t]['trip_direction'] == '1') {
				echo '<td><small>' . service_alias($buf_trips[$t]['service_id']) . '</small></td>';
			}
		}
		echo '<tr>';
	}
	
	for($s = 0; $s < count($buf_stops); $s++) {
		echo '<tr>';
		
		for($t = 0; $t < count($buf_trips); $t++) {
			if($buf_trips[$t]['trip_direction'] != '0') {
				continue;
			}
		
			echo '<td>' . fetch_stop_time($buf_trips[$t]['trip_id'], $buf_stops[$s]['stop_id']) . '</td>';
		}
		
		echo '<td>' . $buf_stops[$s]['stop_name'] . '</td>';
		
		for($t = 0; $t < count($buf_trips); $t++) {
			if($buf_trips[$t]['trip_direction'] != '1') {
				continue;
			}
		
			echo '<td>' . fetch_stop_time($buf_trips[$t]['trip_id'], $buf_stops[$s]['stop_id']) . '</td>';
		}
		
		echo '</tr>';
	}
	
	echo '</table>';
	
	// display service information
	if(count($buf_services) > 0) {
		for($s = 0; $s < count($buf_services); $s++) {
			echo '<p>' . $buf_services[$s]['text'] . '</p>';
		}
	}
}