Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

poj's scratchpad

by poj (Curate)
on Jun 12, 2013 at 17:16 UTC ( #1038520=scratchpad: print w/ replies, xml ) Need Help??

#!/usr/local/bin/perl use strict; use CGI; use CGI::Carp 'fatalsToBrowser'; #remove for prod use DBI; # get form parameters my $q = new CGI; my $action = $q->param('go'); my $empid = $q->param('empid'); my $newid = $q->param('newid'); my $dbh = dbh(); # connect to db $dbh->do("SET search_path to northwind") or die; my $title; my $body; if ($action =~ /(Add|Edit)/) { $title = "$1 Employee $empid records"; $body = form_edit($empid) ; } elsif ($action eq 'Delete') { $title = "Confirm $empid record delete"; $body = delete_confirm($empid) ; } else { if ($action eq 'Save'){ if ($newid){ $body = record_add($newid); $body .= record_save($newid); } else { $body = record_save($empid); } } elsif ($action eq 'Confirm Delete'){ $body = record_delete($empid); } $title = "View Employee records"; $body .= table(); } # build html page my $style = q! body { background-color: pink ; color: #3300cc; } .container { width: 500px; clear: both; } .container input { width: 100%; clear: both;} .message { font-size:1.5em; background-color:yellow; font-weight:bol +d } !; # Send out the header and form print $q->header; print $q->start_html(-title=>$title, -style=>{ -code=>$style } ); print $q->h1($title); print $body; print $q->end_html; # table of records sub table { my $sql = qq!SELECT "EmployeeID", "LastName", "FirstName" FROM "Employees" ORDER by "LastName","FirstName"!; my $sth = $dbh->prepare($sql); $sth->execute(); my $table = q!<table cellspacing="0" cellpadding="3" border="1"> <tr><td>EmployeeID</td> <td>Last Name</td> <td>First Name</td> <td>Edit</td> <td>Delete</td> </tr>!; while (my @f = $sth->fetchrow_array){ my $edit = qq!<a href="?go=Edit;empid=$f[0]">edit</a>!; my $del = qq!<a href="?go=Delete;empid=$f[0]">delete</a>!; $table .= qq!<tr> <td>$f[0]</td> <td>$f[1]</td> <td>$f[2]</td> <td>$edit</td> <td>$del</td> </tr>!; } $table .= qq!<tr> <td colspan="5"> <a href="?go=Add">Add Record</a> </td></tr>!; return $table; } # form for one record sub form_edit { my ($empid) = @_; my $sql = 'SELECT * FROM "Employees" WHERE "EmployeeID" = ?'; my $hr = $dbh->selectrow_hashref($sql,undef,$empid); my $form = qq!<form action="" method="post">!; # add if no id if ($empid){ $form .= qq!Employee ID : $hr->{'EmployeeID'}<br/> <input type="hidden" name="empid" value="$hr->{'EmployeeID'}"/>!; } else { $form .= qq!Employee ID : <input name="newid"/><br/>!; } $form .= qq! Last Name : <input name="LastName" value="$hr->{'LastName'}"/><br/> First Name : <input name="FirstName" value="$hr->{'FirstName'}"/><br/> Title :<input name="Title" value="$hr->{'Title'}"/><br/> Title of Courtesy :<input name="TitleOfCourtesy" value="$hr->{'TitleOf +Courtesy'}"/><br/> Birth Date :<input name="dob" value="$hr->{'dob'}"/><br/> Hire Date :<input name="doh" value="$hr->{'doh'}"/><br/> Address :<input name="address" value="$hr->{'address'}"/><br/> City :<input name="city" value="$hr->{'city'}"/><br/> Region :<input name="region" value="$hr->{'region'}"/><br/> Postal Code :<input name="pcode" value="$hr->{'pcode'}"/><br/> Country :<input name="country" value="$hr->{'country'}"/><br/> Home Phone :<input name="homephone" value="$hr->{'homephone'}"/><br/> Extention :<input name="ext" value="$hr->{'ext'}"/><br/> Notes :<input name="notes" value="$hr->{'notes'}"/><br/> Reports To :<select name="repto">hh</select><br/> <input type="submit" name="go" value="Save"/> <input type="submit" name="go" value="Cancel"/> </form>!; return $form; } sub delete_confirm { my ($empid) = @_; my $form = qq!<form action="" method="post"> <input type="hidden" name="empid" value="$empid"/> <input type="submit" name="go" value="Confirm Delete"/> <input type="submit" name="go" value="Cancel"/> </form>!; return $form; } sub record_save { my ($empid) = @_; my @fields = ('LastName','FirstName'); my @data = map { $q->param($_) } @fields; push @data,$empid; my $sql = qq!UPDATE "Employees" SET "LastName" = ?, "FirstName" = ? WHERE "EmployeeID" = ? !; my $count ;$dbh->do($sql,undef,@data); return qq!<div class="message">$count Records Updated for EmpoyeeID +[$empid] </div>!; } sub record_add { my ($empid) = @_; my $sql = q!INSERT INTO "Employees" ("EmployeeID") VALUES (?)!; my $count = $dbh->do($sql,undef,$empid); $dbh->commit; return qq!<div class="message">$count Records Added for EmpoyeeID [$ +empid]</div>!; } sub record_delete { my ($empid) = @_; my $sql = q!DELETE FROM "Employees" WHERE "EmployeeID" = ?!; my $count = $dbh->do($sql,undef,$empid); return qq!<div class="message">$count Records Deleted for EmpoyeeID +[$empid]</div>!; } # connect to database sub dbh { my $dsn = 'DBI:Pg:dbname=northwind;host=localhost'; my $user = 'test'; my $pwd = 'test'; my $dbh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1}); return $dbh; }
Log In?
Username:
Password:

What's my password?
Create A New User
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (5)
As of 2014-07-13 09:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (248 votes), past polls