# Listing 3: better-list-trains-to-endpoint.pl

#!/usr/bin/perl -wT
# filename: better-list-trains-to-endpoint.pl

use strict;
use diagnostics;
use CGI qw(:standard);
use CGI::Pretty;
use CGI::Carp qw(fatalsToBrowser);
use DBI;

# Change these for your own system
my $database = "test";
my $username = undef;
my $password = undef;

# Connect to the database
my $dbh = DBI->connect("DBI:mysql:$database:localhost",
		       $username, $password) ||
    die qq{DBI error from connect: "$DBI::errstr"};

# Create an instance of CGI
my $query = new CGI;

# Send a MIME header to the user's browser
print $query->header("text/html");

# Get the HTML form elements
my $origin = $query->param("origin") || 0;
my $endpoint = $query->param("endpoint") || 0;

# Create an empty array, into which we will put placeholders
my @placeholders = ();

# Create the query
my $sql = "SELECT S.name, DT.departure_time ";
$sql   .= "FROM RailStations S, DepartureTimes DT, Trains T, StationLines SL ";
$sql   .= "  WHERE T.id = DT.train_id ";
$sql   .= "   AND T.line_id = SL.line_id ";

# If there is an origin station, handle it
if ($origin)
{
    $sql   .= "   AND S.id = ? ";
    push @placeholders, $origin;
}

$sql   .= "   AND SL.station_id = DT.station_id ";
$sql   .= "   AND DT.station_id = S.id ";

# If there is an endpoint station, handle it
if ($endpoint)
{
    $sql   .= "   AND T.destination_id = ? ";
    push @placeholders, $endpoint;
}

$sql   .= " ORDER BY DT.departure_time ";

# Prepare the query
my $sth = $dbh->prepare($sql) 
    || die "Cannot prepare: $DBI::errstr";

# Execute the query
my $success = $sth->execute(@placeholders);

print $query->start_html;
print h1("Train timetable");

print "<table>\n";

# Iterate through the results
while (my $row_ref = $sth->fetchrow_arrayref)
{
    my ($id, $name) = @$row_ref;

    print Tr(td($id), td($name));
}

if ($sth->rows == 0)
{
    print p("Sorry, but no trains matched your criteria.");
}

print "</table>\n";

print $query->end_html;
$sth->finish;
$dbh->disconnect;