Orphan Database

create table kids ( childid serial, firstname VARCHAR(50), lastname VARCHAR(50), birthday timestamp, gender varchar(1), ssn char(9), contactid integer ); create table contacts ( contactid serial, firstname VARCHAR(50), lastname VARCHAR(50), email VARCHAR(30), phone VARCHAR(15) ); ##

##

GRANT SELECT,INSERT,UPDATE,DELETE ON kids TO apache;
GRANT SELECT,INSERT,UPDATE,DELETE ON contacts TO apache

##

##

#!/usr/bin/perl

use CGI qw/:standard/;
use DBI;
        
$scriptversion = "1.0";
$binname = "orphans.pl";
# Tweak this to enable some additional diagnostic info
$debug = 0;

@valid_modes = qw(main entry search);

print header;
print "Orphan Database\n";
print "\n";
        
main(); 


sub main
{
print "Orphan Database\n";
my $dbh = do_connect();
my $mode = get_mode(); 
print "$mode\n";

if($mode eq 'main')
        {
        handle_main();
        }
elsif($mode eq 'entry')
        {
        handle_entry($dbh);
        }
elsif($mode eq 'search')
        {
        handle_lastname($dbh);
        }
do_close($dbh);
print qq{
Script version $scriptversion\n};
}

sub do_connect 
{
my $dbh = DBI->connect("dbi:Pg:dbname=adoption", "", "");
if($dbh->ping)
        {if($debug) {print "Connected
";} }
else    
        {die "Not connected: $!
\n";}
return $dbh;
}       
        
sub do_close
{
my $dbh = shift;
$dbh->disconnect();
}       
        
sub get_mode
{
my $cq = new CGI;
my $mode = $cq->param('mode');

if(! defined($mode))
        {$mode = "main";}
if(! grep(/$mode/, @valid_modes) )
        {
        $mode = "main";
        }
return $mode;
}

sub handle_main
{
print <
        entry
        
search

EOMAIN
}

sub handle_lastname
{ # Tweak this as you like
my ($sql_handle) = @_; # We don't actually use this here, presently.
my $query = want_specific();
if(defined($query) )
        {
        do_specific_lastname_query($query);
        return;
        }
print "Read-Only lastname Query\n";
print <
\n};



EFORM
}

sub do_specific_lastname_query
{
my ($lastname) = @_;
my $dbh = do_connect_ro();
my $qhdl = $dbh->prepare('SELECT * from kids where lastname = ', $dbh->quote($lastname) . ';');
$qhdl->execute();
if($qhdl->err() )
        {
        print "Query failed!\n";
        do_close($dbh);
        return;
        }
my $results = $qhdl->fetchall_arrayref();
if(! defined($results))
        {
        print "Query failed!\n";
        do_close($dbh);
        return;
        }
print qq{\n};
#if(@$results = 0)
#       { print "No entries matched your query\n"; }

foreach $row (@$results)
        {
        print qq{};
        print (map {"\n";} (@$row ) );
        print qq{\n};
        }
print qq{$_};
do_close($dbh);
}


sub want_specific
{
my $cq = new CGI;
my $mode = $cq->param('specific');
return $mode;
}

sub handle_entry
{ # Either present the prompt, or catch the actual request and rename, and
  # tell the user that it's done.
my ($dbh, $gid) = @_;
my $cq = new CGI;
my $targ = want_specific();
if(defined($targ))
        { # User gave us info. Do it, and tell user all's good
        print "Adding $targ.
\n";
        my $rgroup = $dbh->prepare("insert into kids(firstname,lastname,ssn) values (" . $cq->param('firstname') . "," . $cq->param('lastname') . ',', $cq->param('ssn') . ");");
        $rgroup->execute;
        my $checkup = $dbh->prepare("select childid from kids where ssn=$ssn;");
        $checkup->execute();
        $cresults = $checkup->fetchall_arrayref();
        print "The childid of this child is " . $$cresults[0][0] . "
\n";
        print qq{Click here to continue};
        }
else    # Prompt the user for info
        {
        print "Please enter the following information.\n";
        print qq{};
        print qq{\n};
        print qq{\n};
        print qq{\n};
        print qq{\n};
        print qq{\n}; # Not really as pointful as it was in original code
        print qq{\n};
        print qq{

\n};
        print qq{\n};
        }
}