Integrating SQL with CGI, Part 1
Now that we can retrieve postcards without too much trouble, we have to take care of the final part of this project: allowing users to create postcards using HTML forms.
The basic idea is as follows: The sender enters all of the necessary information into an HTML form. The CGI program receiving the submitted form saves the data to the “postcards” table, sends e-mail to the recipient indicating how to retrieve the postcard, and thanks the sender for using our service.
We have already seen how to insert data into the table using an SQL query. All we have to do now is create a CGI program that turns the contents of a form into such a query, and an HTML form that submits its data to our program. You can see an example of such a program, send-postcard.pl, in Listing 2.
In many ways, send-postcard.pl does the same thing as show-postcard.pl. It takes variable values from the HTML form and inserts those values into a canned SQL query. That query is then sent to the database server, which processes it—in this case, by inserting a new row into the database.
As you can see from the listing, we first grab the contents of each of the HTML form elements. In this particular version of the program, we do not check the lengths of each of the fields. It would undoubtedly be a good idea to do so in a production version, given that the database has been instructed to accept names and addresses with a certain maximum length.
Next, we create an ID number for the postcard:
my $id_number = time & 0xFFFFF & $$;
Why didn't we take a simple value, such as time (the number of seconds since January 1, 1970) or $$ (the current process ID)? And why do we perform a bitwise “and” on these values? Because the ID number must be unique; otherwise the database will not accept the new row. We also want to avoid sequential numbers, so that users will not be able to easily guess the numbers. This is far from random and can be guessed by someone interested in doing so; however, it is better than nothing at all and makes life a bit more interesting.
Finally, we create the entry for this postcard in the table, building up the SQL command little by little:
my $command = ""; $command = "insert into postcards "; $command .= " (id_number, sender_name, sender_email, recipient_name, "; $command .= " recipient_email, graphic_name, postcard_text) "; $command .= "values "; $command .= " ($id_number, \"$sender_name\", \"$sender_email\", "; $command .= " \"$recipient_name\", \"$recipient_email\", "; $command .= " \"$graphic_name\", \"$postcard_text\") ";
Notice how we have to surround all but one of the values with quotation marks. This is because they are character values and blobs (as opposed to integers), and thus must be quoted when passed in an SQL query.
Once the SQL query has returned, we know that the postcard has been inserted into the database. Unless, of course, $sth is undefined, in which case we die inelegantly with an error message.
# Make sure that $sth returned reasonably die "Error with command \"$command\"" unless (defined $sth);
Finally, we send e-mail to the recipient indicating that there is a postcard waiting for her, along with the URL for retrieving the postcard. So long as the ID number stored in the database matches the value of $id_number in our program, we should not have any problems. We finish up by thanking the sender for using our system.
Now we come to the part which will enable our users to send postcards to each other: The HTML form from which the information is submitted to the send-postcard.pl program.
This form, as you might expect, is relatively straightforward. It contains five text fields, one for each of the fields we expect to get from the user, as well as a text area into which the user can enter arbitrary text. You can see the page of HTML for yourself in Listing 3.
This system, while a bit crude, does demonstrate how to create a postcard system on your web site with a bit of work. In addition, by taking advantage of the power of SQL and the features of a relational database, we created a relatively robust system without a lot of work and without having to debug a lot of code.
You could easily add another few HTML form elements to postcard.html, making it possible for the sender of a postcard to set the background color, text style and font of a particular postcard. The possibilities are indeed limitless, although you should avoid making such an HTML form look like the cockpit of a jumbo jet.
There are, of course, a number of loose ends with this project. One such problem has to do with the graphics, which we mentioned briefly above. In addition, what happens if the ID number is lost? Currently, there isn't any way for someone to come to our site and retrieve any postcards that they might have sent or received. We will take care of that next month, as we continue to look at and use SQL in our CGI programs.
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.
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