Listing 2: client-editor.html
<HTML>
<Head><Title>Client Editor</Title></Head>
<Body>
[- use Apache::DBI; -]
[- $dbh = DBI->connect("DBI:mysql:test:localhost"); -]
[- @colnames = qw(id name address1 address2
city state country zip contact_name
contact_phone1 contact_phone2
contact_fax initial_contact_date
dollars_per_hour);
$colnames = join ', ', @colnames;
-]
[$ if scalar keys %fdat > 0 $]
<!-- ===================================== -->
<!-- Should we add a new client? -->
[$ if $fdat{"modify-0"} ne "" $]
<!-- Make sure we avoid setting the ID -->
[- @insert_colnames = grep !/^id$/,
@colnames; -]
[- $insert_colnames = join ', ',
@insert_colnames; -]
<!-- Create the query -->
[- $values = join '", "', map {$fdat{$_ .
"-0"}} @insert_colnames -]
[+ $sql = "INSERT INTO Clients
($insert_colnames)
VALUES (\"$values\")"; +]
<!-- Send the SQL -->
[- $sth = $dbh->prepare($sql); -]
[- $sth->execute; -]
[- $id = $sth->{"insertid"} -]
<!-- If error, print the message -->
<P><B>[+ $sth->errstr +]</B></P>
[$ endif $]
<!-- ===================================== -->
<!-- Should we update existing clients? -->
[$ foreach $clientid
(grep {($_ =~ /^modify-\d+$/) &&
($fdat{$_} eq "modify")}
(sort keys %fdat)) $]
<!-- Create the query -->
[+
$clientid =~ m/(\d+)$/;
$pairs = join ', ',
map {"$_ = '" . $fdat{$_ . "-$1"} .
"'"}
grep (!/^id$/, @colnames);
$sql = "UPDATE Clients SET $pairs WHERE
id = $1";
+]
<!-- Do the query -->
[- $sth = $dbh->prepare($sql); -]
[- $sth->execute; -]
<!-- If error, print a message -->
<P>[+ $sth->errstr +]</P>
[$ endforeach $]
<!-- ===================================== -->
<!-- Should we delete existing clients? -->
[$ foreach $clientid
(grep {($_ =~ /^modify-\d+$/) && ($fdat{$_}
eq "delete")} (sort keys %fdat)) $]
<!-- Grab the ID, and create the SQL -->
[+ $clientid =~ m/^modify-(\d+)$/;
$sql = "DELETE FROM Clients WHERE
id = $1" +];
<!-- Do the query -->
[- $sth = $dbh->prepare($sql); -]
[- $sth->execute; -]
<!-- If error, print a message -->
<P>[+ $sth->errstr +]</P>
[$ endforeach $]
[$ endif $]
<!-- ============================================== -->
<!-- Here is the manual HTML part of the file,
for entering new clients -->
<H1>Add/Modify Client Information</H1>
<Form method="POST" action="/embperl/client-editor.html">
<P><input type="submit" value="Add/update values">
<input type="reset" value="Revert values"></P>
<Table border="2">
<TD colspan="2" bgcolor="#abcdef">
<P><input type="checkbox" name="modify-0">
Add this new client <P>
</TD>
<!-- Create table for entering new client -->
[$ 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 $]
</Table>
<!-- Set up the SQL query for existing
information -->
[- $sql = "SELECT $colnames FROM Clients"; -]
<!-- Send the query -->
[- $sth = $dbh->prepare ($sql) -]
<!-- If error, print the message -->
<P><B>[+ $sth->errstr +]</B></P>
<!-- Execute the query -->
[- $sth->execute -]
<!-- If error, print the message -->
<P><B>[+ $sth->errstr +]</B></P>
<!-- Iterate through each row (record) -->
[$ while ($record = $sth->fetchrow_arrayref) $]
<!-- Get the unique ID for this record -->
[- $recordid = $record->[0]; -]
<P><input type="submit"
value="Add/update values">
<input type="reset"
value="Revert values"></P>
<Table border="1">
<TR bgcolor="#abcdef">
<TD colspan="2">
<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
</TD> </TR>
<!-- Iterate through each field -->
[- $fieldcounter = 0; -]
[$ foreach $field @$record $]
<!-- Don't print ID numbers -->
[$ if $colnames[$fieldcounter] ne "id" $]
<!-- Print value in editable field -->
<TR>
<TD>[+ $colnames[$fieldcounter] +]</TD>
<TD>
<input type="text"
name="[+ $colnames[$fieldcounter] .
'-' . $recordid +]" size="50"
maxlength="100" value="[+ $field +]">
</TD> </TR>
[$ endif $]
<!-- Increment our counter -->
[- $fieldcounter++; -]
[$ endforeach $]
<!-- End table and add whitespace -->
</Table><P></P>
[$ endwhile $]
<P><input type="submit" value="Add/update values">
<input type="reset" value="Revert values"></P>
</Form> </Body> </HTML>
