Embperl and Databases

This month, Mr. Lerner returns to the subject of Embperl, showing us how it can be used to edit database records.
Creating the Form

If you are new to templates, it might take a while to understand the idea of a single file containing both an HTML form and the program necessary to process it. Consider that this is no different from a CGI program producing the form from which it can get input.

Listing 1 contains two parts: form processing and form creation. While Embperl looks at the former before the latter, we will look at creation first, since it is generally easier to handle, especially when working with templates for the first time.

We will have one HTML form element for every column in our table except for id, since MySQL generates the ID for us automatically. Later, we'll expand this program to handle editing and deleting of rows in our table, which means we will need to handle one form element for each column and row in our database, in addition to one for the “new” record we will be submitting.

My solution is to give each form element the name of the column to which it is attached, followed by a hyphen and the ID number. The “city” column for the row with id = 5 will be an element named “city-5”, and the name of the client with id = 30 will be an element named “name-30”. Since MySQL starts auto-incrementing ID with 1, we can use “name-0”, “address-0” and so forth for our new entry.

Early on in our program, we will define the @colnames array, which will contain the names of the columns in our database:

@colnames = (id name address1 address2 city
        state country zip
         contact_name contact_phone1 contact_phone2
         initial_contact_date dollars_per_hour);

Now that we have defined @colnames, we can create the HTML form with Embperl's meta-commands. We want to create an entry for each element (except for id, since modifying that would create serious problems), so we will iterate through each element of @colnames, adding the necessary HTML and remembering to skip id. This part of my implementation looks like this:

[$ foreach $column @colnames $]
[$ if $column ne "id" $]
<TR> <TD>
        [+ $column +]
 </TD> <TD>
        <input type="text" name="[+ $column +]-0"
         size="40" maxlength="40" >
 </TD> </TR>
 [$ endif $]
 [$ endforeach $]
The above code looks a lot like Perl, with good reason. It uses a foreach loop, which iterates over the elements of an array (@colnames), putting each successive element of the array in a scalar ($column). We can then use that scalar value by putting it in square-plus brackets at the appropriate points in our HTML.

You are probably not used to seeing the endif and endforeach meta-commands in the square-dollar brackets. These tell Embperl where the if and foreach meta-commands end their scope, just as closing curly braces would do in a standard Perl program.

We set the maximum length of each field to “40”, just as the fields in our table are all defined to be VARCHAR(40). If we were to modify the table definition such that each column were set to a more reasonable size (e.g., name should probably be closer to 60, and contact_phone closer to 15), we would also want to modify the size of each field in the HTML form. Otherwise, users will blindly enter too many characters, and their input will be silently truncated by the database server. The MySQL DBD (DBD::mysql) has a length attribute that can be used for such purposes, if you wish.

Processing the Form

Now that we have created the form, let's think about how we can process it once we receive it. The Embperl document will receive the form's name-value pairs exactly as if they were being submitted to a CGI program, although we will have to extract them somewhat differently. The pairs are sent in the %fdat hash, in which the hash's keys are names of the submitted HTML form elements, and the hash's values are those values. We can grab the name of the new client with $fdat{"name-0"}, the main telephone number with $fdat{"contact_phone1-0"} and so forth.

Inserting a record into a table follows the pattern:

INSERT (column1, column2, column3) "
 VALUES ("value1", "value2", "value3")

We will want to do something like this:

INSERT (@columns)
 VALUES (%fdat)
Of course, life isn't quite that easy; we must first create a new array, @insert_colnames, with the names of the columns we wish to insert—in other words, everything except id:
[- @insert_colnames = grep !/^id$/, @colnames; -]
Then we turn that into a comma-separated list, which is what we will need for the first part of the INSERT:
[- $insert_colnames = join ', ', @insert_colnames; -]
With that accomplished, we will use Perl's built-in map function to turn @insert_colnames from an array of column names into an array of column values. We then convert the resulting array into a scalar, in which each value is separated by a comma and surrounded by double quotation marks:
[- $values = join '", "', map {$fdat{$_ .
        @insert_colnames -]
If @insert_colnames were to consist of
(column1, column2, column3)
the above use of map would turn it into:
($fdat{"column1-0"}, $fdat{"column2-0"},
which join would then turn into:
There aren't any quotes at the beginning or the end, but we can add them when we finally construct the query:
[+ $sql = "INSERT INTO Clients ($insert_colnames)
        VALUES (\"$values\")"; +]
We use square-plus brackets here in order to see (and debug, if necessary) the query we send to the database. Don't forget that if we are using double quotes to take advantage of variable interpolation, we must escape the double quotes we wish to send in our query with backslashes.

We finally send that query with the statements:

[- $sth = $dbh->prepare($sql); -]
[- $sth->execute; -]

If there are any errors, print them for the user:

<P><B>[+ $sth->errstr +]</B></P>
Our new record is now inserted in the database.

This entire form-processing section is unnecessary if the user has not submitted any form elements. In Listing 1, you can see how we used the Embperl if meta-command to exclude evaluation of this entire block of code if the user has already done something.

The first time you run this, don't be surprised if everything seems to work and you get your original form back. As they say, that's not a bug—it's a feature! If Embperl finds fields in an HTML form that match the name-value pairs in %fdat, it fills them in automatically. You can turn this option off by modifying the EMBPERL_OPTIONS bitmask field, described in the Embperl documentation.