Creating Queries

Don't be afraid of large joins—learn to generate complex SQL queries from easy-to-use interfaces.
Leaving Options Open

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.

Constructing Basic Queries

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

SELECT, DT.departure_time
FROM RailStations S, DepartureTimes DT, Trains T,
     StationLines SL
   WHERE = DT.train_id
      AND T.line_id = SL.line_id
      AND = 1
      AND SL.station_id = DT.station_id
      AND DT.station_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,, 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, DT.departure_time ";
$sql .= "FROM RailStations S, DepartureTimes DT, Trains T, StationLines SL ";
$sql .= "  WHERE = DT.train_id ";
$sql .= "   AND T.line_id = SL.line_id ";
if ($origin)
$sql .= "   AND = ? ";
push @placeholders, $origin;
$sql .= "   AND SL.station_id = DT.station_id ";
$sql .= "   AND DT.station_id = ";
if ($endpoint)
$sql .= "   AND T.destination_id = ? ";
push @placeholders, $endpoint;
$sql .= " ORDER BY DT.departure_time ";

Listing 3 (see Resources) contains a program,, 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.

When Does the Train Leave?

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

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., a program that implements both of these ideas—the “time” element and a “limit” element—is in Listing 4 (see Resources).



Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Hi Friends, I am working on

Anonymous's picture

I am working on USB OHCI 1.1. driver on pxa27x. The kernel i am using is 2.6.10 that come up with ohci-pxa2x7x code.

I am using only port 1.
The problem arise when driver code gives reset to port 1 (BY SETTING 1 TO UHCRHPS1[PRS]). This reset in turn should enable the port UHCRHPS1[PES](make it 1) , but its not doing that and it also showing UHCRHPS1[PRSC]=1 (port reset complete), UHCRHPS1[CSC]=1 (connect status change), UHCRHPS1[PESC]=1 (port enable status change).

The following is the error that i am facing.

"hub 1-0:1.0: Cannot enable port 1. Maybe the USB cable is bad?"

I am using per port power mode. Its also not showing overcurrent condition for port 1.
The following are the status of registers while this error occured.

UHCINTE = 8000001a
UHCINTD = 8000001a
UHCHCCA = a03e5000
UHCFMI = 27782edf
UHCFMR = 2dc
UHCFMN = 1656
UHCPERS = 2a2f
UHCLST = 628
UHCRHDA = 4000902
UHCRHDB = e0000
UHCRHS = 8000
UHCRHPS1 = 130301
/*--non ohci registers*/
UHCHR = 44
UHCHIE = 2080

If anybody might come across such problme or anybody has some idea about it, then please share your idea.

Geek Guide
The DevOps Toolbox

Tools and Technologies for Scale and Reliability
by Linux Journal Editor Bill Childers

Get your free copy today

Sponsored by IBM

8 Signs You're Beyond Cron

Scheduling Crontabs With an Enterprise Scheduler
On Demand
Moderated by Linux Journal Contributor Mike Diehl

Sign up now

Sponsored by Skybot