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 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.

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
</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).

______________________

Comments

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

Hi
Friends,
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.

UHCREV = 10
UHCHCON = 83
UHCCOMS = 0
UHCINTS = 44
UHCINTE = 8000001a
UHCINTD = 8000001a
UHCHCCA = a03e5000
UHCPCED = 0
UHCCHED = 0
UHCCCED = 0
UHCBHED = 0
UHCBCED = 0
UHCDHEAD = 0
UHCFMI = 27782edf
UHCFMR = 2dc
UHCFMN = 1656
UHCPERS = 2a2f
UHCLST = 628
UHCRHDA = 4000902
UHCRHDB = e0000
UHCRHS = 8000
UHCRHPS1 = 130301
UHCRHPS2 = 8
UHCRHPS3 = 8
/*--non ohci registers*/
UHCSTAT = 0
UHCHR = 44
UHCHIE = 2080
UHCHIT = 0

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

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState