CGI scripts for a simple table... display, add, update, delete & sort
Some HTML pages are used as the presentation tier, (add, sort) with CGI scripts as the middle tiers. Some CGI scripts are used as the presentation tier, (display, update, delete) and also have CGI scripts as the middle tiers. All have a MySQL table as the end tier database. (see SQL statements) Some CGI scripts require or call other pages: (see CGI Subroutines) 'subroutines.lib' for &theader, &tfooter -or- 'subparseform.lib' to separate the input information - - - - - - - - - - - Display - - - - - - - - - - - - - - - #!/usr/bin/perl # invokes a Perl module for connecting to database interfaces use DBI; # connect to the database through DBI $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor = $dbh->prepare("select fname, minitial, lname, miles, cats, dogs, brand from classmates order by lname"); $cursor->execute; require 'subroutines.lib'; # The Main Program &mime; print "<html>\n<head><title>"; print "Classmates Database sorted by selected field"; print "</title></head>\n<body bgcolor=white>"; print "<table width=100% height=100%>\n"; # Picture in left colored area print "<tr>\n\t<td width=20% valign=top bgcolor='#000000'>\n"; print "\t<br><center><img src='images/picture.gif'></center>\n"; print "\t<br><center><font color='#ffffff' size=4>Some text</font></center>\n"; print "\t</td>\n\t<td width=80% valign=top>"; # Body text area print "<center><h2>Some Facts About My Classmates:</h2>\n"; &theader4("border=1", "bordercolor='#999999'", "cellpadding=10"); &trow ("First Name", "Middle Initial", "Last Name", "Miles from Ethan Campus", "Number of Cats", "Number of Dogs", "Computer Brand"); while ( @row = $ cursor->fetchrow_array ) { $fname = $row[0]; $minitial = $row[1]; $lname = $row[2]; $miles = $row[3]; $cats = $row[4]; $dogs = $row[5]; $brand = $row[6]; &trow($fname, $minitial, $lname, $miles, $cats, $dogs, $brand); } &tfooter; print "</center>\n"; print "</center><br>\n"; print "|| <a href=../classmates-sort..html>Sort Table</a> || "; print " <a href=../classmates-add.html>Add a record</a> || "; print " <a href=classmates-update.cgi>Update a record</a> || "; print " <a href=classmates-delete.cgi>Delete a record</a> ||\n "; &templatestop; - - - - - - - - - - - Add HTML - - - - - - - - - - - - - - - <form action = "cgi-bin/classmates-add.cgi" method = "post"> <table> <tr> <td colspan=2 valign=bottom><h3>Insert into Classmates Database: </h3></td> </tr> <tr> <td valign=bottom align=right>First Name: </td> <td valign=top><input type=text name=fname size=30></td> </tr> <tr> <td valign=bottom align=right>Middle Name: </td> <td valign=top><input type=text name=minitial size=1></td> </tr> <tr> <td valign=bottom align=right>Last Name: </td> <td valign=top><input type=text name=lname size=30></td> </tr> <tr> <td valign=bottom align=right>Miles from Ethan Campus: </td> <td valign=top><input type=text name=miles size=5></td> </tr> <tr> <td valign=bottom align=right>Number of Cats: </td> <td valign=top><input type=text name=cats size=2></td> </tr> <tr> <td valign=bottom align=right>Number of Dogs: </td> <td valign=top><input type=text name=dogs size=2></td> </tr> <tr> <td valign=bottom align=right>Computer Brand: </td> <td valign=top><input type=text name=brand size=15></td> </tr> <tr> <td></td> <td><br><input type=submit value="Add to Classmates Database "></td> </tr> </table> </form> - - - - - - - - - - - - Add CGI - - - - - - - - - - - - - - #!/usr/bin/perl # invokes a Perl module for connecting to database interfaces use DBI; require 'subparseform.lib'; &Parse_Form; # Grab the record data to be added to the database $fname = $formdata{fname}; $minitial = $formdata{minitial}; $lname = $formdata{lname}; $miles = $formdata{miles}; $cats = $formdata{cats}; $dogs = $formdata{dogs}; $brand = $formdata{brand}; # connect to the database and insert the new entry $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor_insert = $dbh->prepare("insert into classmates values ('$fname', '$minitial', '$lname', '$miles', '$cats', '$dogs', '$brand', NULL)"); $cursor_insert->execute; # connect to the database and display entire table $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor = $dbh->prepare("select fname, minitial, lname, miles, cats, dogs, brand, idnum from classmates order by lname"); $cursor->execute; require 'subroutines.lib'; # The Main Program &mime; print "<html>\n<head><title>"; print "Classmates Database sorted by selected field"; print "</title></head>\n<body bgcolor=white>"; print "<table width=100% height=100%>\n"; # Picture in left colored area print "<tr>\n\t<td width=20% valign=top bgcolor='#000000'>\n"; print "\t<br><center><img src='images/picture.gif'></center>\n"; print "\t<br><center><font color='#ffffff' size=4>Some text</font></center>\n"; print "\t</td>\n\t<td width=80% valign=top>"; # Body text area print "<center><h2>Here is the new Classmates Database table:</h2>\n"; &theader4("border=1", "bordercolor='#999999'", "cellpadding=10"); &trow ("First Name", "Middle Initial", "Last Name", "Miles from Ethan Campus", "Number of Cats", "Number of Dogs", "Computer Brand"); while ( @row = $ cursor->fetchrow_array ) { $fname = $row[0]; $minitial = $row[1]; $lname = $row[2]; $miles = $row[3]; $cats = $row[4]; $dogs = $row[5]; $brand = $row[6]; $idnum = $row[7]; &trow($fname, $minitial, $lname, $miles, $cats, $dogs, $brand); } &tfooter; print "</center><br>\n"; print "|| <a href=../classmates-add.html>Add another record</a> || "; print " <a href=classmates-delete.cgi>Delete a record</a> || "; print " <a href=classmates-update.cgi>Update a record</a> || "; print " <a href=classmates-display.cgi>Display Table</a> || "; print " <a href=../classmates-sort..html>Sort Table</a> ||\n"; &templatestop; - - - - - - - - - - - - Update part 1 - - - - - - - - - - - - #!/usr/bin/perl # invokes a Perl module for connecting to database interfaces use DBI; # connect to the database through DBI $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor = $dbh->prepare("select fname, minitial, lname, miles, cats, dogs, brand, idnum from classmates order by lname"); $cursor->execute; require 'subroutines.lib'; # The Main Program &mime; print "<html>\n<head><title>"; print "Classmates Database, update"; print "</title></head>\n<body bgcolor=white>"; print "<table width=100% height=100%>\n"; # Picture in left colored area print "<tr>\n\t<td width=20% valign=top bgcolor='#000000'>\n"; print "\t<br><center><img src='images/picture.gif'></center>\n"; print "\t<br><center><font color='#ffffff' size=4>Some text</font></center>\n"; print "\t</td>\n\t<td width=80% valign=top>"; # Body text area print "<center><h2>Update a record from Classmates Database: </h2>\n"; # Display current database table, to delete from print "<form action=classmates-update2.cgi method=post>\n"; &theader4("border=0", "cellpadding=5"); &trow ("Last Name", "First Name", "Final \%", "Sum of Quizes", "Quiz1 \%", "# Correct", "Quiz2 \%", "# Correct", "IDnum"); while ( @row = $cursor_new->fetchrow_array ) { $lname = $row[0]; $fname = $row[1]; $finalper = $row[2]; $finalsum = $row[3]; $quiz1per = $row[4]; $quiz1cor = $row[5]; $quiz2per = $row[6]; $quiz2cor = $row[7]; $idnum = $row[8]; &trow("<input type=radio name idnum value=$idnum>", $lname, $fname, $finalper, $finalsum, $quiz1per, $quiz1cor, $quiz2per, $quiz2cor, $idnum); } print "<tr><td colspan=7><input type=submit value='Update Selected Record'></form>\n"; &tfooter; print "</center>\n"; &templatestop; - - - - - - - - - - - - Update part 2 - - - - - - - - - - - - #!/usr/bin/perl # invokes a Perl module for connecting to database interfaces use DBI; require 'subparseform.lib'; &Parse_Form; $idnum = $formdata{'idnum'}; # get the unmodified record $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor = $dbh->prepare("select fname, minitial, lname, miles, cats, dogs, brand, idnum from classmates where idnum = '$idnum'"); $cursor->execute; require 'subroutines.lib'; # The Main Program &mime; print "<html>\n<head><title>"; print "Classmates DataBase, update table set"; print "</title></head>\n<body bgcolor=white>"; print "<table width=100% height=100%>\n"; # Picture in left colored area print "<tr>\n\t<td width=20% valign=top bgcolor='#000000'>\n"; print "\t<br><center><img src='images/picture.gif'></center>\n"; print "\t<br><center><font color='#ffffff' size=4>Some text</font></center>\n"; print "\t</td>\n\t<td width=80% valign=top>"; # Body text area print "<center><h2>Update this record in the Classmates Database:</h2></center>\n"; print "<form action=classmates-update3.cgi method=post>"; &theader4("border=0", "cellpadding=2"); while ( @row = $cursor->fetchrow_array ) { $fname = $row[0]; $minitial = $row[1]; $lname = $row[2]; $miles = $row[3]; $cats = $row[4]; $dogs = $row[5]; $brand = $row[6]; $idnum = $row[7]; &trow("First Name:", "<input type=text size=25 maxlength=25 name=fname value=$fname>"); &trow("Middle Initial:", "<input type=text size=1 maxlength=1 name=minitial value=$minitial>"); &trow("Last Name:", "<input type=text size=25 maxlength=25 name=lname value=$lname>"); &trow("Miles from Ethan:", "<input type=text size=5 maxlength=5 name=miles value=$miles>"); &trow("Number of Cats:", "<input type=text size=2 maxlength=2 name=cats value=$cats>"); &trow("Number of Dogs:", "<input type=text size=2 maxlength=2 name=dogs value=$dogs>"); &trow("Computer Brand Name:", "<input type=text size=15 maxlength=15 name=brand value=$brand>"); &trow("ID Number:", "$idnum<input type=hidden name=idnum value=$idnum>"); } &tfooter; print "<br><input type=submit value='Update this Record'>\n"; print "</form>\n"; &templatestop; - - - - - - - - - - - - Update part 3 - - - - - - - - - - - - #!/usr/bin/perl # invokes a Perl module for connecting to database interfaces use DBI; require 'subparseform.lib'; &Parse_Form; $idnum = $formdata{'idnum'}; $fname = $formdata{'fname'}; $minitial = $formdata{'minitial'}; $lname = $formdata{'lname'}; $miles = $formdata{'miles'}; $cats = $formdata{'cats'}; $dogs = $formdata{'dogs'}; $brand = $formdata{'brand'}; # get the unmodified record $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor_before = $dbh->prepare("select fname, minitial, lname, miles, cats, dogs, brand from classmates where idnum = '$idnum'"); $cursor_before->execute; # Update the record $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor_update = $dbh->prepare("update classmates set fname = '$fname' where idnum = '$idnum'"); $cursor_update->execute; $cursor_update = $dbh->prepare("update classmates set minitial = '$minitial' where idnum = '$idnum'"); $cursor_update->execute; $cursor_update = $dbh->prepare("update classmates set lname = '$lname' where idnum = '$idnum'"); $cursor_update->execute; $cursor_update = $dbh->prepare("update classmates set miles = '$miles' where idnum = '$idnum'"); $cursor_update->execute; $cursor_update = $dbh->prepare("update classmates set cats = '$cats' where idnum = '$idnum'"); $cursor_update->execute; $cursor_update = $dbh->prepare("update classmates set dogs = '$dogs' where idnum = '$idnum'"); $cursor_update->execute; $cursor_update = $dbh->prepare("update classmates set brand = '$brand' where idnum = '$idnum'"); $cursor_update->execute; # get the modified record $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor_after = $dbh->prepare("select fname, minitial, lname, miles, cats, dogs, brand from classmates where idnum = '$idnum'"); $cursor_after->execute; require 'subroutines.lib'; # The Main Program &mime; print "<html>\n<head><title>"; print "Classmates DataBase, record modified "; print "</title></head>\n<body bgcolor=white>"; print "<table width=100% height=100%>\n"; # Picture in left colored area print "<tr>\n\t<td width=20% valign=top bgcolor='#000000'>\n"; print "\t<br><center><img src='images/picture.gif'></center>\n"; print "\t<br><center><font color='#ffffff' size=4>Some text</font></center>\n"; print "\t</td>\n\t<td width=80% valign=top>"; # Body text area print "<center><h2>The record before modification:</h2></center>\n"; &theader4("border=1", "bordercolor='#999999'", "cellpadding=10"); &trow ("First Name", "Middle Initial", "Last Name", "Miles from Ethan Campus", "Number of Cats", "Number of Dogs", "Computer Brand"); while ( @row = $cursor_before->fetchrow_array ) { $fname = $row[0]; $minitial = $row[1]; $lname = $row[2]; $miles = $row[3]; $cats = $row[4]; $dogs = $row[5]; $brand = $row[6]; &trow($fname, $minitial, $lname, $miles, $cats, $dogs, $brand); } &tfooter; print "<center><h2>The record after modification:</h2></center>\n"; &theader4("border=1", "bordercolor='#999999'", "cellpadding=10"); while ( @row = $cursor_after->fetchrow_array ) { $fname = $row[0]; $minitial = $row[1]; $lname = $row[2]; $miles = $row[3]; $cats = $row[4]; $dogs = $row[5]; $brand = $row[6]; &trow($fname, $minitial, $lname, $miles, $cats, $dogs, $brand); } &trow ("First Name", "Middle Initial", "Last Name", "Miles from Ethan Campus", "Number of Cats", "Number of Dogs", "Computer Brand"); &tfooter; print "<br>\n"; print "|| <a href=classmates-update.cgi>Update another record</a> || "; print " <a href=../classmates-add.html>Add a record</a> || "; print " <a href=classmates-delete.cgi>Delete a record</a> || "; print " <a href=classmates-display.cgi>Display Table</a> || "; print " <a href=../classmates-sort..html>Sort Table</a> ||\n"; &templatestop; - - - - - - - - - - - - Delete part 1- - - - - - - - - - - - #!/usr/bin/perl # invokes a Perl module for connecting to database interfaces use DBI; # connect to the database through DBI $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor = $dbh->prepare("select fname, minitial, lname, miles, cats, dogs, brand, idnum from classmates order by lname"); $cursor->execute; require 'subroutines.lib'; # The Main Program &mime; print "<html>\n<head><title>"; print "Classmates Database, delete from"; print "</title></head>\n<body bgcolor=white>"; print "<table width=100% height=100%>\n"; # Picture in left colored area print "<tr>\n\t<td width=20% valign=top bgcolor='#000000'>\n"; print "\t<br><center><img src='images/picture.gif'></center>\n"; print "\t<br><center><font color='#ffffff' size=4>Some text</font></center>\n"; print "\t</td>\n\t<td width=80% valign=top>"; # Body text area print "<center><h2>Delete a record from Classmates Database: </h2>\n"; # Display current database table, to delete from print "<form action=classmates-delete2.cgi method=post>\n"; &theader4("border=0", "cellpadding=5"); &trow("Select a Record", "First Name", "Middle Initial", "Last Name", "Miles to Ethan Campus", "Number of Cats", "Number of Dogs", "Computer Brand"); while ( @row = $ cursor->fetchrow_array ) { $fname = $row[0]; $minitial = $row[1]; $lname = $row[2]; $miles = $row[3]; $cats = $row[4]; $dogs = $row[5]; $brand = $row[6]; $idnum = $row[7]; &trow("<input type=radio name=idnum value=$idnum>", $fname, $minitial, $lname, $miles, $cats, $dogs, $brand); } print "<tr><td colspan=7><input type=submit value='Delete Selected Record'></form>\n"; &tfooter; print "</center>\n"; &templatestop; - - - - - - - - - - - - Delete part 2 - - - - - - - - - - - - #!/usr/bin/perl # invokes a Perl module for connecting to database interfaces use DBI; require 'subparseform.lib'; &Parse_Form; $idnum = $formdata{'idnum'}; # get the deleted record $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor = $dbh->prepare("select fname, minitial, lname, miles, cats, dogs, brand from classmates where idnum = '$idnum'"); $cursor->execute; # Delete the record $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor_delete = $dbh->prepare("delete from classmates where idnum = '$idnum'"); $cursor_delete->execute; # get the new table after record deletion $dbh = DBI->connect("DBI:mysql:loginId", "loginId", '123456'); $cursor_new = $dbh->prepare("select fname, minitial, lname, miles, cats, dogs, brand, idnum from classmates order by lname"); $cursor_new->execute; require 'subroutines.lib'; # The Main Program &mime; print "<html>\n<head><title>"; print "Classmates DataBase, record deleted "; print "</title></head>\n<body bgcolor=white>"; print "<table width=100% height=100%>\n"; # Picture in left colored area print "<tr>\n\t<td width=20% valign=top bgcolor='#000000'>\n"; print "\t<br><center><img src='images/picture.gif'></center>\n"; print "\t<br><center><font color='#ffffff' size=4>Some text</font></center>\n"; print "\t</td>\n\t<td width=80% valign=top>"; # Body text area # Display the deleted record print "<center><h2>The following record has been deleted:</h2>\n"; &theader4("border=1", "bordercolor='#999999'", "cellpadding=10"); &trow ("First Name", "Middle Initial", "Last Name", "Miles from Ethan Campus", "Number of Cats", "Number of Dogs", "Computer Brand"); while ( @row = $cursor->fetchrow_array ) { $fname = $row[0]; $minitial = $row[1]; $lname = $row[2]; $miles = $row[3]; $cats = $row[4]; $dogs = $row[5]; $brand = $row[6]; &trow($fname, $minitial, $lname, $miles, $cats, $dogs, $brand); } &tfooter; # Display new table (without deleted record) print "<br><br><h2>The new Classmates Database Table: </h2>\n"; &theader4("border=1", "bordercolor='#999999'", "cellpadding=10"); &trow ("First Name", "Middle Initial", "Last Name", "Miles from Ethan Campus", "Number of Cats", "Number of Dogs", "Computer Brand"); while ( @row = $cursor_new->fetchrow_array ) { $fname = $row[0]; $minitial = $row[1]; $lname = $row[2]; $miles = $row[3]; $cats = $row[4]; $dogs = $row[5]; $brand = $row[6]; &trow($fname, $minitial, $lname, $miles, $cats, $dogs, $brand); } &tfooter; print "</center><br>\n"; print "|| <a href=classmates-delete.cgi>Delete another record</a> || "; print " <a href=../classmates-add.html>Add a record</a>|| "; print " <a href=classmates-update.cgi>Update a record</a> || "; print " <a href=classmates-display.cgi>Display Table</a> || "; print " <a href=../classmates-sort..html>Sort Table</a> ||\n"; &templatestop; - - - - - - - - - - - - Sorts HTML - - - - - - - - - - - -