Creating Queries

The <select> list produced by Listing 1 has at least one problem. What if the user doesn't care about the particular piece of information in the <select> list? For example, assume the user is interested in traveling from Tel Aviv to Rehovot at some point during the day, but has not yet decided when. Forcing a user to choose a time from a <select> list makes things more difficult than they should be. It would be nice to have an “any” option on each <select> list, allowing users to indicate this particular field can have any information.

Implementing this strategy requires two things. First, one <option> value can be guaranteed not to correspond to an existing row's primary key. Second, the CGI program creating the final query will identify this value and modify the SQL accordingly.

Luckily, the combination of MySQL and Perl works quite well in this context. MySQL's auto-incrementing primary keys begin with 1 and increase until the maximum value is reached. Because an auto-incrementing primary key can never be zero, we can create an additional <option> line in the <select> list:

<option value="0" selected>Any

By making this value 0, we ensure it cannot correspond to any actual row's primary key. And by marking it “selected”, we set this to be the default value for the <select> menu. New visitors to the site who accept the default values will thus get the widest possible search, with the fewest possible WHERE clauses. Each selection of an <option> with a non-zero value will add a new WHERE clause to the resulting SQL.

The following query lists all times at which trains from Nahariya (ID 1) have an endpoint of Tel Aviv (ID 5):

SELECT S.name, DT.departure_time
FROM RailStations S, DepartureTimes DT, Trains T,
     StationLines SL
   WHERE T.id = DT.train_id
      AND T.line_id = SL.line_id
      AND S.id = 1
      AND SL.station_id = DT.station_id
      AND DT.station_id = S.id
      AND T.destination_id = 5
   ORDER BY DT.departure_time
;

We can turn the above query into a CGI program by replacing the two ID numbers with placeholder values. If we fill in the placeholders with the contents of the “origin” and “endpoint” HTML form elements, we can find the times of all trains from one station heading toward a particular endpoint. Such a CGI program, list-trains-to-endpoint.pl, is in Listing 2 (see Resources).

The above code works just fine until someone selects one of the Any items with a value of 0. If that happens, MySQL will not return any rows from the SELECT, because no stations have an ID of 0. The solution is to make those parts of the query conditional, inserting them only if an actual value was indicated.

We accomplish this by placing two “if” statements in the middle of the code that assembles the SQL statement. Because the generic ID is 0, our program can test for a set value simply by putting the variable name inside parentheses, which implies a test for non-zero values:

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 ($origin)
{
$sql .= "   AND S.id = ? ";
push @placeholders, $origin;
}
$sql .= "   AND SL.station_id = DT.station_id ";
$sql .= "   AND DT.station_id = S.id ";
if ($endpoint)
{
$sql .= "   AND T.destination_id = ? ";
push @placeholders, $endpoint;
}
$sql .= " ORDER BY DT.departure_time ";

Listing 3 (see Resources) contains a program, better-list-trains-to-endpoint.pl, that allows users to specify a station of origin, an endpoint for the train, neither or both. If the user specifies only the station of origin, the program will display a list of trains departing from that station without regard for direction. If the user specifies only the endpoint, it will list all departures toward that station. Most applications do not need to give such headway to users, and might want to trap inputs in which both elements are assigned values of 0. At the same time, no harm comes from allowing users to amuse themselves with nonsensical queries.

The above queries work just fine, yet they ignore a crucial issue when working with train schedules: people typically want to specify the time they prefer to leave or arrive. It would certainly be possible to give users a set of <select> lists corresponding to various hours and minutes in the day, giving them fine control over the assembled query. We could also allow them to enter dates and times in text fields, but as with station names, there are too many possible ways for such input to go wrong.

It is probably easier for them to relate to time information such as “morning”, “afternoon” and “night”, rather than specifying hours. We can do this by using another set of <select> lists, this time specifying hours as values and by setting the hours to the end of the specified period. For example:

<select name="time">
<option value="12:00">Morning
<option value="17:00">Afternoon
<option value="21:00">Evening
<option value="24:00">Night
</select>

We can find all morning trains by asking for those earlier than 12:00, afternoon trains by asking for those before 17:00 and so forth.

With such a <select> list in place in the query generator form, we can rest assured that users will be able to find their train. If we are concerned that too many trains will match their query, we can add another <select> list to the HTML form, allowing users to limit the number of returned trains. MySQL supports a LIMIT clause on SELECT queries, making it possible to return a maximum number of rows.

Note that LIMIT will always contain a numeric value and can thus be inserted directly into the SQL query without the use of placeholders. Indeed, trying to use placeholders in a LIMIT clause will force the number to be quoted, which will cause a MySQL error.

best-list-trains.pl, a program that implements both of these ideas—the “time” element and a “limit” element—is in Listing 4 (see Resources).