fr2-mysql.cgi

#!/usr/bin/perl -w

#USe the DBI(db interface) module
use DBI;

#Declare variables with MySQL connection data
$db="dbname/user";
$user="zenuser";

$passwd="pass";
$host="db-mysql.zen";
$connectionInfo="dbi:mysql:$db;$host";

#Print HTTP Headers
print "Content-type:text/html\n\n";

#If first time script run display form
if ($ENV{REQUEST_METHOD} eq "GET")
	{
	&displayform();
	&showfriends();
	exit;
	}
#else process form and insert into DB
else
	{
	&parseform();
	&insertfriend();
	&showfriends();
	exit;
	}
#Standard form parseing using POST method
sub parseform
	{
	# Read the standard input (sent by the form):
read(STDIN, $FormData, $ENV{'CONTENT_LENGTH'});
# Get the name and value for each form input:
@pairs = split(/&/, $FormData);
# Then for each name/value pair....
foreach $pair (@pairs) {
	# Separate the name and value:
	($name, $value) = split(/=/, $pair);
	# Convert + signs to spaces:
	$value =~ tr/+/ /;
	# Convert hex pairs (%HH) to ASCII characters:
	$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
	# Store values in a hash called %FORM:
	$form{$name} = $value;
			}


	}

sub insertfriend
	 {

	#Form SQL insert statement
	$insert = qq~insert friends(lname, fname, phone, email)
		values('$form{lname}', '$form{fname}', '$form{phone}', '$form{email}')~;

	#connect to MYSQL and create Database Handler $dbh
	$dbh=DBI->connect($connectionInfo,$user,$passwd);

	#Prepare MYSQL Statement and create Statement Handler $sth
	$sth=$dbh->prepare($insert);

	#Execute Statement Handler and test for success
	if ($sth->execute())
		{
		&displaysuccess;
		#&showfriends;
		}
	else
		{
		&displayfail;
		}

	#Disconnect Database
	$dbh->disconnect();
	}
sub displaysuccess
	{
	print qq~<html>\n
		<head>
		<title>My Friends</title>
		</head>
		<body>
		<h2>Record Added</h2>
		</body>
		</html>
		~;
	}
sub displayfail
        {
        print qq~<html>\n
                <head>
                <title>My Friends</title>
                </head>
                <body>
                <h2>Record NOT Added</h2>
                </body>
                </html>
                ~;
        }
sub displayform
	{
	print qq~
		<html>
		<head>
		<title>My Friends</title>
		</head>

		<body>
		<form action="friends2-mysql.cgi" method=POST>
		<center>
		<h2>My Friends</h2>
		Last Name: <input type=text name=lname>
		<br>
		First Name: <input type=text name=fname>
		<br>
		Phone Number: <input type=text name=phone> (10 digits only)
		<br>
		E-mail: <input type=text name=email>
		<br>
		<input type=submit value="Insert" name=Insert>
		<input type=reset value=Reset name=reset>
		</form>
		</body>
		</html>
		~;
	}

sub showfriends
	{
	#start html table
	print qq~<html>
		<head>
		<Title>My Friends</Title>
		</head>
		<body>
		<table border=1>
		<tr>
		<th>ID</th><th>Last Name</th><th>First Name</th><th>Phone Number</th><th>Email</th>
		</tr>~;

	#form SQL Select Statement
	$select = qq~select id, lname, fname, phone, email from friends~;

	#connect to MySQL and create a DB handler $dbh
	$dbh=DBI->connect($connectionInfo,$user,$passwd);

	#prepare MySQL  statement and create Statement Handler $sth
	$sth=$dbh->prepare($select);

	#Execute select statement
	$sth->execute();

	#Loop through each record selected and print in html table
	while (@row=$sth->fetchrow_array())
		{
	print qq~<tr>
			<td>$row[0]</td><td>$row[1]</td><td>$row[2]</td>
			<td>$row[3]</td><td>$row[4]</td>
			</tr>~;
		}

	#close HTML table
	print qq~</table>
		</body>
		</html>~;

	$dbh->disconnect();
	}