Embperl and Databases

Now that we have seen how to enter new records using Embperl, let's expand the template such that it will allow us to modify and delete existing records, as well as add new ones. You can see the complete listing for such a template in Listing 2 in the archive file, client-editor.html.

The first task is to retrieve existing elements from the database and turn them into a list of form elements the user can grab. As we saw earlier, it will be easiest if we give each form element the name of the column with which it is associated, along with a number indicating its record ID number.

The first order of business is to retrieve rows from the current database. We do that with a SELECT statement, whose syntax looks like this:

SELECT column1, column2, column3 FROM Tablename;

We set up our query as follows:

[- $sql = "SELECT $colnames FROM Clients"; -]

Now we prepare and execute the query using the standard DBI syntax:

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

The result from a SELECT is a table, which we can retrieve in a number of different ways. Perhaps the easiest method is to grab it as an array reference, then turn that array reference into an array containing the name-value pairs, continuing to fetch array references until we run out. If we use Embperl's while meta-command, we can do that fairly easily:

[$ while ($record = $sth->fetchrow_arrayref) $]

We then grab the id column:

[- $recordid = $record->[0]; -]

We can turn that array reference into an array, using Embperl's foreach meta-command to iterate over each element, printing each one except id in a table row. If we store the current record (row) number in $recordid and the current field number in $fieldcounter, we can create this by iterating over the following code:

<TR>
<TD>[+ $colnames[$fieldcounter] +]</TD>
<TD>
 <input type="text"
 name="[+ $colnames[$fieldcounter] .
        '-' . $recordid +]" size="50"
maxlength="100"
        value="[+ $field +]" >
</TD>
</TR>

We will also add a set of three radio buttons to indicate whether the user wishes to delete this record, modify it or do nothing. We will set “nothing” as the default, since we don't want users to inadvertently delete any elements. We create the radio buttons, using the modify- stem just as we would in normal HTML. However, we will add the current ID number to that stem:

<P><input type="radio" value="nothing"
 name="modify-[+$recordid +]" checked> Do nothing
 <input type="radio" value="modify"
 name="modify-[+$recordid +]"> Modify this client
 <input type="radio" value="delete"
 name="modify-[+$recordid +]"> Delete this client </P>

As you can see in Listing 2, we also added a check box to the initial “new client” form to indicate whether a user is interested in adding a new client. This check box can be hardcoded in HTML, since we are allowing users to add new elements from only that one form, with the pseudo-ID of 0:

<P><input type="checkbox"
name="modify-0">
 Add this new client <P>

Just as add-client.html (Listing 1) was divided into a processing section (the first part) and the form-generation section (the second part), so too is our full client-editor.html (Listing 2). The above section describes how we will use SELECT to create the HTML form, so all that remains is describing the processing section, which comes at the top of the template.

With add-client.html, we could assume that the user wanted to add a new client. There are now four possibilities: adding a new client, updating an existing client, deleting an existing client and doing nothing at all. While add can be true only for modify-0 (the new record), we have to check every set of HTML form elements that comes to us. The simplest case, of course, is when the modify- radio button is set to “nothing”.

If the user wants to add a new record, the element modify-0 will be checked. We can use an Embperl if meta-command to check for its existence:

[$ if $fdat{"modify-0"} ne "" $]

In other words, if the user checked modify-0, we will add a new record, just as we did in add-client.html.

Finding out if the user checked modify for one of the records is a bit trickier. We take the names of all submitted form elements (sort keys %fdat), and use grep to grab all of those with the modify- stem:

[$ foreach $clientid
 (grep {($_ =~ /^modify-\d+$/) && ($fdat{$_} eq
        "modify@bb:1.      )}
 (sort keys %fdat)) $]

If the above looks a bit intimidating, remember that $_ contains the value of the scalar currently being handled by grep. We tell grep to return only those array elements that match modify-\d+ (that is, modify- followed by one or more digits), and whose value is modify. We then take the array returned by grep and iterate over it using Embperl's foreach meta-command.

Once inside the foreach loop, how do we create the SQL query? We first have to grab the ID of the element in question, so that we will update only the appropriate record. We do that by giving:

$clientid =~ m/(\d+)$/;

This puts the ID value in the temporary variable $1. We then use a combination of grep, map and join to create the list of name-value pairs necessary to complete an UPDATE statement syntax with:

UPDATE Clients SET
name1="value1",name2="value2"
 WHERE id = $1

We use grep to grab all column names except for id (once again, we don't want to change that value). We then filter that result through map, turning the list of column names into a list of name="value" pairs. Finally, we join that list together with commas, resulting in the scalar $pairs:

$pairs = join ', ',
 map {"$_ = '" . $fdat{$_ . "-$1"} . "'"}
 grep (!/^id$/, @colnames);

We can then set up the SQL query as follows:

$sql = "UPDATE Clients SET $pairs WHERE id = $1";

Deleting elements is easier than updating, since we don't need the name-value pairs. We can use the statement:

$sql = "DELETE FROM Clients WHERE id = $1";

where $1 matched the number of the current element.