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 email@example.com.
Practical Task Scheduling Deployment
July 20, 2016 12:00 pm CDT
One of the best things about the UNIX environment (aside from being stable and efficient) is the vast array of software tools available to help you do your job. Traditionally, a UNIX tool does only one thing, but does that one thing very well. For example, grep is very easy to use and can search vast amounts of data quickly. The find tool can find a particular file or files based on all kinds of criteria. It's pretty easy to string these tools together to build even more powerful tools, such as a tool that finds all of the .log files in the /home directory and searches each one for a particular entry. This erector-set mentality allows UNIX system administrators to seem to always have the right tool for the job.
Cron traditionally has been considered another such a tool for job scheduling, but is it enough? This webinar considers that very question. The first part builds on a previous Geek Guide, Beyond Cron, and briefly describes how to know when it might be time to consider upgrading your job scheduling infrastructure. The second part presents an actual planning and implementation framework.
Join Linux Journal's Mike Diehl and Pat Cameron of Help Systems.
Free to Linux Journal readers.Register Now!
- SUSE LLC's SUSE Manager
- My +1 Sword of Productivity
- Murat Yener and Onur Dundar's Expert Android Studio (Wrox)
- Managing Linux Using Puppet
- Non-Linux FOSS: Caffeine!
- SuperTuxKart 0.9.2 Released
- Doing for User Space What We Did for Kernel Space
- Parsing an RSS News Feed with a Bash Script
- Google's SwiftShader Released
- Rogue Wave Software's Zend Server
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