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.
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.
Fast/Flexible Linux OS Recovery
On Demand Now
In this live one-hour webinar, learn how to enhance your existing backup strategies for complete disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible full-system recovery solution for UNIX and Linux systems.
Join Linux Journal's Shawn Powers and David Huffman, President/CEO, Storix, Inc.
Free to Linux Journal readers.Register Now!
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- Profiles and RC Files
- Maru OS Brings Debian to Your Phone
- OpenSwitch Finds a New Home
- Astronomy for KDE
- Understanding Ceph and Its Place in the Market
- Git 2.9 Released
- SoftMaker FreeOffice
- Snappy Moves to New Platforms
- What's Our Next Fight?
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide