Now that we have seen some basic uses of MySQL from within Perl, let's spend some time thinking about how we can integrate the use of MySQL into a CGI program. While this might seem like overkill for some small jobs, database servers are so much more reliable and efficient at this sort of task than our CGI programs that it is almost always worth using such a server, assuming one is available.
By using a database server, we can be sure that our data is stored more reliably than with text files. As an added bonus, the information is available using SQL, which is more efficient and flexible than text files.
How can we use a database server from within our CGI programs? The simple answer is that it is actually no different from connecting to a database server from within non-CGI programs. We still create the Mysql object, use its methods to send an SQL query and retrieve results. The differences are in our ability to modify our query based on input sent to us in an HTML form and the necessity of sending our output to the user's browser using a recognized content type (usually HTML). Such a program, which I have called cgi-sql-test.pl is shown in Listing 2.
While cgi-sql-test.pl is longer than the program on which it is based, it is not much more complicated.
First, we fire up the CGI module for Perl, which you can get via the Comprehensive Perl Archive Network (CPAN) at http://www.perl.com/CPAN. After creating an instance of CGI, we send an HTTP Content-type header to the user's browser indicating that we will be returning results of type text/html, i.e., HTML-formatted text.
Following our initialization of the CGI environment, we go ahead with what we had done in the non-CGI version of the program, namely connecting to the database, sending our query and retrieving the results.
This is where the big difference lies. Rather than printing the results to standard output, we send them in HTML format to the user's browser, so that we can use all sorts of nifty HTML formatting techniques to display the results.
In this particular example, I decided to put the results of the telephone list in an HTML table, which is attractive and makes it easy to understand the results. The <tr> tag introduces a table row, while the <td> tag introduces a column within a row. Because each iteration through the while loop represents a new record in the database, we can start a new HTML row at the top of each loop, ending it at the bottom of each loop.
We will continue to explore the interaction between SQL and CGI in the next few installments, but before I conclude this month's column, I want to show at least one example of how we can modify the SQL queries based on the user's input. For the sake of simplicity, we modify our program such that it will ask the database server to return only those rows whose name column matches what we enter in the query string. Thus, if we are interested in finding out Gil's telephone number, we can go to:
And if we are interested in finding out Andy's telephone number, we can go to:
/cgi-bin/cgi-sql-test.pl?Andywhich produces only that listing.
But what happens if someone invokes our program without entering a name in the query string? Well, our program cleverly notices it and produces a very small page of HTML in response. This small page of HTML asks the user to enter a name for which to search and then uses the <isindex> tag to create a text field in the page of HTML.
The <isindex> tag has generally fallen out of favor, since HTML forms are more flexible and useful. When a user enters information into an <isindex> field and presses enter, the URL in which the <isindex> tag appeared is reloaded—with the user's input appended as part of the query string.
Thus, if our program receives no input in the query string, it produces a page containing <isindex>. Whatever the user enters in that text field causes our program to be reloaded, this time with a value in the query string. That value is picked up by our program and passed to MySQL, which returns the results in an HTML table.
That concludes the basic introduction regarding the integration of SQL and CGI programs. As you might imagine, SQL databases are far more powerful than the programs and databases we have seen this month. Over the next few months, we will spend some more time looking at different ways in which we can use MySQL (and relational database servers in general) to make for more interesting, efficient and useful web sites.
Reuven M. Lerner is an Internet and Web consultant living in Haifa, Israel, who has been using the Web since early 1993. In his spare time, he cooks, reads and volunteers with educational projects in his community. You can reach him at firstname.lastname@example.org.
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"
- Peppermint 7 Released
- Client-Side Performance
- Sony Settles in Linux Battle
- Libarchive Security Flaw Discovered
- Maru OS Brings Debian to Your Phone
- Profiles and RC Files
- The Giant Zero, Part 0.x
- Snappy Moves to New Platforms
- Git 2.9 Released
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