At the Forge: Embperl and Databases

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>
Archive Index Issue Table of Contents Article