in reply to Help with creating Web Database Frontend

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.

Replies are listed 'Best First'.
Re: Re: Help with creating Web Database Frontend
by benn (Vicar) on Apr 26, 2003 at 18:19 UTC
    serious ++Improv - what a star. I hereby swallow all my words about "possible volunteers" - and email support to boot! :)
    Cheers,
    Ben
[reply]
Re: Re: Help with creating Web Database Frontend
by Divine2aT (Initiate) on Apr 26, 2003 at 23:04 UTC
    Thankyou Iprov I will take you up on the offer I emailed you seperately just incase you didn't get the email I will post a copy here for you. Hi this is Tina aka Divine2aT Can you put this up for me if I give you the web URL and Permission to use the file server ect. to my web page. I am scared I don't want to mess everything up. I do not no how to wright scripts like this and I am really bad with directions when it comes to anything to do with HTML CGI PERL the scripts I do have on my page where copies made by others on a shared HTML goodies like page with very simple directions the forms I wanted them to ask Your Name at time of Birth : First M. Last DOB of Adoptee : MM/DD/YYYY City, ST-State of Birth : City Name, ST Abbr.- State Name In Search Of : Bmom, Bdad, Bsis, Bbro, Bson, Bdau, Bpar Current email Address : your email@ your host.com Date of Post Date : MM/DD/YY URL web address if any : your http:// www. url .com and I wanted it to auto post itself to any proper designated page and a script that would search "this database only" for DOB, Names that kinda stuff and it should be able to be accessed for updating, deleting, changing info, ect. by myself, and by the poster. I would also like a link to you for help put on the page since you are the righter and credit to you for the script Just tell me what you need to know if you can help thanks again
[reply]

      For the love of Dog please add HTML tags to your posts. :)

[reply]

In Section Seekers of Perl Wisdom