tekniko has asked for the wisdom of the Perl Monks concerning the following question:

Is there a better way to do what I have done here? This is for manipulating database records through an HTML interface. There are 2500+ records in the DB and more every day. The goal will be for non-technical people to access this page and manipulate the data without knowing SQL. 
#!/usr/bin/perl -w

use strict;
use CGI::Request;
use DBI;

require 5.005;

$| = 1; # OUTPUT_AUTOFLUSH

my $req = new CGI::Request;
my $DataKey = "domain";
my $Driver = 'mysql';
my $DataBase = 'webstat1';
my $Table = 'sites';
my $User = 'statadmin';
my $Password = 'statsrus';
my $dbh;
my @Fields = qw( priority domain server_name server_type log_code log_
+location ip_addr username timestamp );
my $button = (defined($req->param("button"))) ? $req->param("button") 
+: "Default";
my %FormData = getformdata();


##  MAIN  ################################
MAIN:
{
    db_connect();


    search_page() if ($button eq "Search" || ( $button eq "Default" &&
+ !defined %FormData));
    form_page() if ($button eq "Find Record" || $button eq "Cancel" ||
+ ($button eq "Default" && defined (%FormData)));
    edit_page() if ($button eq "Edit");
    add_page() if ($button eq "Add Record");
    save_record() if ($button eq "Save");
    delete_record() if ($button eq "Delete");


    db_disconnect();
}



## Subroutines  ##########################
sub getformdata()
{
    my $p;
    my %F;
    foreach $p ( @Fields )
    {
        $F{$p} = $req->param($p) if (defined ($req->param($p)));
    }
    $F{record} = $req->param("record") if (defined ($req->param("recor
+d")));
    $F{savetype} = $req->param("savetype") if (defined ($req->param("s
+avetype")));
    return %F;
}

sub print_htmlheader()
{
    print "Content-type:  text/html\n\n";
    print <<__END_OF_HTML__;
<HTML>
<HEAD><TITLE>Webstat Administration</TITLE></HEAD>
<BODY bgcolor="#FFFFFF">
<FORM METHOD=POST ACTION="statadmin.pl">
<CENTER>
__END_OF_HTML__
# FIXME
#<TABLE  width=75%>
#    <TR><TD rowspan=2 align=center valign=top><IMG ALT="" WIDTH=150 H
+EIGHT=129 SRC="http://www.primary.net/primarylogo_150.gif"></TD>
#    <TD align=center><H2>Webstat Site Administrator</H2></TD></TR>
#    <TR><TD>
}

sub print_htmlfooter
{
    my $page = shift;
    print "    <HR width=75%>\n";
    print "    <CENTER>\n";

    if ($page eq "search")
    {
    print "      <INPUT TYPE=SUBMIT NAME=\"BUTTON\" VALUE=\"Add Record
+\">\n";
    }
    elsif ($page eq "form")
    {
    print "      <INPUT TYPE=SUBMIT NAME=\"button\" VALUE=\"Search\">\
+n";
    print "      <INPUT TYPE=SUBMIT NAME=\"button\" VALUE=\"Add Record
+\">\n";
    print "      <INPUT TYPE=SUBMIT NAME=\"button\" VALUE=\"Edit\">\n"
+;
    print "      <INPUT TYPE=SUBMIT NAME=\"button\" VALUE=\"Delete\">\
+n";
    }
    elsif ($page eq "edit" || $page eq "add")
    {
    print "      <INPUT TYPE=HIDDEN NAME=\"savetype\" VALUE=\"$page\">
+\n";
    print "      <INPUT TYPE=SUBMIT NAME=\"button\" VALUE=\"Save\">\n"
+;
    print "      <INPUT TYPE=SUBMIT NAME=\"button\" VALUE=\"Cancel\">\
+n";
    }



    print <<__END_OF_HTML__;
    </CENTER>
</FORM>
</BODY>
</HTML>
__END_OF_HTML__
}

sub db_connect()
{
    my $dsn = "DBI:$Driver:$DataBase";
    $dbh = DBI->connect($dsn, $User, $Password, {RaiseError => 1, Auto
+Commit => 1});
}

sub db_disconnect()
{
    $dbh->disconnect;
}


##  Pages  ###############################
sub search_page
{
    my $message = shift;
    print_htmlheader();
    print "<CENTER><H2>$message</H2></CENTER>" if ($message);
    print <<__END_OF_HTML__;
<CENTER>
    Search by $DataKey:
    <INPUT TYPE=TEXT NAME="domain">
    <INPUT TYPE=SUBMIT NAME="button" VALUE="Find Record">
</CENTER>
__END_OF_HTML__
    print_htmlfooter("search");
}

sub form_page
{
    my $message = shift;
    my $hashref;
    my $field;
    my $query = "select * from $Table where $DataKey = ?";
    my $sth = $dbh->prepare($query);
    $sth->execute($FormData{$DataKey});

    print_htmlheader();
    print "<CENTER>\n";
    print "<H2>$message</H2>\n" if (defined($message));
    print "<TABLE border>\n";
    while (defined($hashref = $sth->fetchrow_hashref))
    {
    foreach $field ( @Fields )
    {
        print "    <TR><TD><B>$field:</B>&nbsp;</TD><TD>$$hashref{$fie
+ld}&nbsp;</TD></TR>\n";
    }
    }
    print "<INPUT TYPE=HIDDEN NAME=\"record\" VALUE=\"$FormData{$DataK
+ey}\">";
    print "</TABLE>\n";
    print "</CENTER>\n";
    print_htmlfooter("form");
}

sub edit_page
{
    my $message = shift;
    my $hashref;
    my $query = "select * from $Table where $DataKey = ?";
    my $sth = $dbh->prepare($query);
    $sth->execute($FormData{record});

    print_htmlheader();
    print "<CENTER>\n";
    print "<H2>$message</H2>\n" if (defined($message));
    while (defined($hashref = $sth->fetchrow_hashref))
    {
    print<<__END_OF_HTML__;
<TABLE border>
    <TR><TD><B>Priority:</B></TD>
        <TD>$$hashref{priority}&nbsp;</TD></TR>
    <TR><TD><B>Domain:</B></TD>
        <TD><INPUT TYPE=HIDDEN NAME="domain" VALUE="$$hashref{domain}"
+>$$hashref{domain}&nbsp;</TD></TR>
    <TR><TD><B>Server Name:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="server_name" VALUE="$$hashref{serve
+r_name}"></TD></TR>
    <TR><TD><B>Server Type:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="server_type" VALUE="$$hashref{serve
+r_type}"></TD></TR>
    <TR><TD><B>Log Code:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="log_code" VALUE="$$hashref{log_code
+}"></TD></TR>
    <TR><TD><B>Log Location:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="log_location" VALUE="$$hashref{log_
+location}"></TD></TR>
    <TR><TD><B>IP Address:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="ip_addr" VALUE="$$hashref{ip_addr}"
+></TD></TR>
    <TR><TD><B>Username:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="username" VALUE="$$hashref{username
+}"></TD></TR>
    <TR><TD><B>Time Stamp:</B></TD>
        <TD>$$hashref{timestamp}&nbsp;</TD></TR>
</TABLE>
</CENTER>
__END_OF_HTML__
    }
    print_htmlfooter("edit");   
}

sub add_page
{
    print_htmlheader();
    my $hashref;
    my $field;
    print "<CENTER>\n<TABLE border>\n";
    print <<__END_OF_HTML__;
    <TR><TD><B>Priority:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="priority" VALUE=""></TD></TR>
    <TR><TD><B>Domain:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="domain" VALUE=""></TD></TR>
    <TR><TD><B>Server Name:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="server_name" VALUE=""></TD></TR>
    <TR><TD><B>Server Type:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="server_type" VALUE=""></TD></TR>
    <TR><TD><B>Log Code:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="log_code" VALUE=""></TD></TR>
    <TR><TD><B>Log Location:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="log_location" VALUE=""></TD></TR>
    <TR><TD><B>IP Address:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="ip_addr" VALUE=""></TD></TR>
    <TR><TD><B>Username:</B></TD>
        <TD><INPUT TYPE=TEXT NAME="username" VALUE=""></TD></TR>
__END_OF_HTML__
    print "</TABLE>\n</CENTER>\n";
    print_htmlfooter("add");
}

sub save_record()
{
    # This sub is used to actually save the data to the database 
    if ($FormData{savetype} eq "add")
    {
    my $hashref;
    my %rst;
    my $field;
    my @values;
    my @colname;
    }
    elsif ($FormData{savetype} eq "edit")
    {
    my $field;
    my $query = "update $Table set ? where $DataKey = '$FormData{$Data
+Key}'";
    my $sth = $dbh->prepare($query);
    print_htmlheader();
    print $query;
    foreach $field (@Fields)
    {
        print "$field:  $FormData{$field}<BR>\n" if ($field ne "domain
+" && $field ne "priority");
    }
    form_page("Record Saved");
    }
}

sub delete_record()
{
}

sub data_page()
{
}

[download]

Replies are listed 'Best First'.
Re: HTML, DB, and Perl
by OeufMayo (Curate) on Jan 05, 2001 at 21:42 UTC

    Personnaly, when I have to do things like this, I use HTML::Template or similar packages (maybe Template-Toolkit. It makes your program file less clutered by the alternance of HTML/Perl code, and eventually making it easier to read and maintain.

    <kbd>--
    PerlMonger::Paris(http => 'paris.pm.org');</kbd>
[reply]
Re: HTML, DB, and Perl
by kschwab (Vicar) on Jan 05, 2001 at 22:19 UTC
    In your save_record sub you have: 
    my $query = 
 "update $Table set ? where ...";
  my $sth = $dbh->prepare($query);

    [download]
    Two suggestions:
    1. If you are doing an update (instead of a query), you don't really need to go through the prepare(),execute() sequence. You may find that: 
      $dbh->do("update $Table set...");

      [download]
      is easier to deal with.

    2. It looks like you are aware of the ? placeholder syntax, but you aren't using it everywhere. There are several cases where you are putting "raw" variables into a select or update clause. This can cause problems if the variable content has a single quote, percent sign, etc. You'll either have to use the $dbh->quote() method to escape chars (better), or the ? placeholder syntax (better still).
[reply]
[d/l]
[select]
Re: HTML, DB, and Perl
by ichimunki (Priest) on Jan 05, 2001 at 22:09 UTC
    Barring templating, why not use more CGI.pm methods? Hand coding all the HTML into your code has got to be painful. Then for commonly used elements, like your input fields table, you can write a real short routine that puts in the next table cell using variables for the, um, variable bits. :)

    If you find yourself typing the same thing more than twice or using cut and paste more than twice, it's time to abstract the process and give it a name. Your typing fingers will thank you and your code will be clearer.
[reply]
Re: HTML, DB, and Perl
by tekniko (Deacon) on Jan 06, 2001 at 00:43 UTC
    End-users will only see buttons for ADD, DELETE, EDIT, and VIEW. That will be the extent of the functionality. It will, however, take those duties out of the hands of my sysadmins, allowing them more time to download MP3s, shop at Thinkgeek, and (of course) seek the wisdom of Perl Monks..
[reply]

Back to Seekers of Perl Wisdom