Creating Queries

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

Over the last few years increasing numbers of web sites have begun to integrate a relational database management system (RDBMS). Databases excel at storing and retrieving information quickly and easily and make it possible for web developers to create sophisticated applications without getting bogged down in the details.

While it is not hard to integrate a database into a web site, it can sometimes be tricky to design the database tables, as well as construct sophisticated applications to use them.

Last month we looked at (normalization) the technique that makes it possible to reduce potential errors while increasing the speed and flexibility of our database. This makes it easier to create a variety of applications with the same data. This comes at a price: the queries needed to retrieve information from the database become more complex, often joining data from two, three or four tables. With a bit of training, however, programmers can easily overcome their fear of large joins, using them to retrieve only the information they need.

Most users are not programmers, and it is unrealistic to expect them to construct complex queries on their own. The trick is to create a user interface that makes it possible to answer a large subset of the possible questions, without overwhelming users with a variety of options.

This month, we will spend some time looking at how to generate complex SQL queries from relatively easy-to-use interfaces. Our examples will draw on the example tables from last month, which describe the Israeli train system.

In the end, we will write two types of programs. Some programs (often known as “query generators” in the database trade) will generate HTML forms that can be used to create queries. Other programs will translate the HTML form into an actual SQL query, displaying the results in the user's browser.

Limiting Options

Linux users often sneer at the Macintosh interface, because it limits the available options. At the bash prompt, a Linux user can invoke many thousands of commands. Furthermore, the output of any program can be piped to a file or another program. This multitude of options makes Linux a particularly powerful system, yet one that is difficult to learn and master.

Most users are not interested in power, but simply want to get their work done. Giving users too much flexibility can sometimes be a hindrance. In such cases, we want to limit users' options, forcing them to make a choice that our programs can handle. This is one of the key advantages of a GUI, it reduces the chance for user error by reducing the number of available options. It also reduces the number of potential inputs a program might receive, making it easier to test applications.

For example, consider an HTML form that asks users to enter their destination train station. Many HTML forms use a text area to input such information as follows:

<input type="text" name="destination" size="30">

Allowing users to enter a station name in this way opens Pandora's box, forcing the program to handle misspellings, capitalization issues and even problems stemming from whitespace. Databases might be good at many things, but they do require precise inputs that match exactly.

We can improve the situation and simplify our program by limiting the user's actions with a <select> list. Each <option> in this <select> list will correspond to one row from the RailStations table, with the value attribute set to the row's “ID” and the user-visible text set to the name attribute. For example, we could do the following:

<select name="destination">
<option value="1">Nahariya
<option value="2">Akko
<option value="3">Hof Hacarmel
<option value="4">Tel Aviv Central
<option value="5">Tel Aviv Hashalom
<option value="6">Lod
<option value="7">Rehovot
<option value="8">Herzliya
</select>

The above <select> list provides more reliable input than a text field. However, it presents several problems. First and foremost, placing the above static HTML in a file means the RailStations table and the <select> list will inevitably go out of synch. From the user's perspective, the above <select> list is difficult to use, because it orders the items according to their ID numbers rather than their station names.

We can solve both of these problems with our query generator, a CGI program that produces HTML forms based on information in the database. Listing 1 (see Resources) contains a simple non-CGI program, select-list-from-table.pl, that produces the above <select> list based on the current contents of the RailStations table with the stations listed in alphabetical order. If a train station's name is changed or a new station is added, an HTML form created by select-list-from-table.pl will immediately reflect the new value.

Because rows returned by a SELECT statement can be returned in any order specified by the ORDER BY clause, it is possible to produce a <select> list in an order other than alphabetical. For example, users might prefer to see a list of train stations by location.

______________________

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