Ok, here's a cooking list. I'll assume we're starting from scratch: Let's create the database..
Run the following commands as the user 'postgres': The last will bring up the interactive psql tool. Do the following: create user apache;. That will create an account that apache can use to access the database we'll be creating. Now, we need to actually create the tables. In that psql tool, do the following: 
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)
);

[download]
Later on, you might want to add new columns to these table, but this is just an example. Now let's grant apache the ability to alter these tables: 
GRANT SELECT,INSERT,UPDATE,DELETE ON kids TO apache;
GRANT SELECT,INSERT,UPDATE,DELETE ON contacts TO apache

[download]
Ok, now we're done with this (for now. There's a lot more fancy stuff we could do, like adding constraints or other data, later). Let's quit the psql tool: \q. Now we need to write our cgis. I personally like to write my CGIs with the GET method, although it might be better to later rewrite this to use POST. Here's a drop'n'go solution: 
#!/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 "<HEAD><TITLE>Orphan Database</TITLE></HEAD>\n";
print "<BODY BGCOLOR=\"#AAACCC\">\n";
        
main(); 


sub main
{
print "<H1>Orphan Database</H1>\n";
my $dbh = do_connect();
my $mode = get_mode(); 
print "<CENTER>$mode</CENTER><HR>\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{<HR><FONT SIZE="-2">Script version $scriptversion</FONT>\n};
}

sub do_connect 
{
my $dbh = DBI->connect("dbi:Pg:dbname=adoption", "", "");
if($dbh->ping)
        {if($debug) {print "Connected<BR>";} }
else    
        {die "Not connected: $!<BR>\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 <<EOMAIN;
Browse...
<UL>
        <li><a HREF="$binname?mode=entry">entry</a>
        <li><a HREF="$binname?mode=search">search</a>
</UL>
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 "<h1>Read-Only lastname Query</h1>\n";
print <<EFORM;
<FORM ACTION="$binname">
<input type=hidden name=mode value="sql">\n};
<input maxLength=512 size=512 name=specific value="">
<input type=submit value="Query">
</FORM>
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{<TABLE border=1>\n};
#if(@$results = 0)
#       { print "No entries matched your query\n"; }

foreach $row (@$results)
        {
        print qq{<TR>};
        print (map {"<TD>$_</TD>\n";} (@$row ) );
        print qq{</TR>\n};
        }
print qq{</TABLE>};
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.<br>\n";
        my $rgroup = $dbh->prepare("insert into kids(firstname,lastnam
+e,ssn) values (" . $cq->param('firstname') . "," . $cq->param('lastna
+me') . ',', $cq->param('ssn') . ");");
        $rgroup->execute;
        my $checkup = $dbh->prepare("select childid from kids where ss
+n=$ssn;");
        $checkup->execute();
        $cresults = $checkup->fetchall_arrayref();
        print "The childid of this child is " . $$cresults[0][0] . "<b
+r>\n";
        print qq{Click <A HREF="$binname?mode=main">here</A> to contin
+ue</A
>};
        }
else    # Prompt the user for info
        {
        print "Please enter the following information.\n";
        print qq{<FORM ACTION="$binname">};
        print qq{<input maxLength=256 size=55 name=firstname value="FI
+RSTNAME">\n};
        print qq{<input maxLength=256 size=55 name=lastname value="LAS
+TNAME">\n};
        print qq{<input maxLength=256 size=55 name=ssn value="SSN">\n}
+;
        print qq{<input type=hidden name=mode value="entry">\n};
        print qq{<input type=hidden name=specific value="child">\n}; #
+ Not really as pointful as it was in original code
        print qq{<input type=submit value="Do it">\n};
        print qq{</FORM><BR>\n};
        print qq{\n};
        }
}

[download]
Note that this is just a bare bones of what you'll want, but it'll get you started, or at least thinking in the right direction. You will want to learn SQL eventually. This code is also kind of ugly -- I ripped it from another project that I'm working on, and hacked it up to kind of resemble what you might want. It doesn't exercise the full capabilities of the tables we've designed either. To use, drop it in your CGI-bin directory as orphans.pl, make sure all the postgres-perl bindings are installed, and it should work with hopefully no or minimal debugging. Feel free to ask or email me any questions you have. As it's for a good cause, and involves Perl, I'd be happy to continue to develop the thing with you for awhile.

In reply to Re: Help with creating Web Database Frontend by Improv
in thread Help with creating Web Database Frontend by Divine2aT

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.