[Beds] web form into and out of a database using perl

Fri Jan 14 15:04:00 GMT 2005

A question came up at the meeting last night regarding how to get data 
from a html form into a datbase and then back out into a html report. I 
have posted a basic solution here so that anyone who is interested can 
see it.

To get data from the form into the database you can use CGI.pm to access 
the parameters from the form. I have assumed four fields on the form 
called formItemOne, Two, Three, Four. These are assigned to perl 
variables and put into a database before printing a thankyou message.
====================================================
#!/usr/bin/perl -w

use strict;
use CGI ':standard';
use DBI;

print header();

my ($statement, $dbh, $sth, $formItemOne, $formItemTwo, $formItemThree, 
$formItemFour);

$formItemOne = param('formItemOne');
$formItemTwo = param('formItemTwo');
$formItemThree = param('formItemThree');
$formItemFour = param('formItemFour');

$statement = qq(insert into tableName values(?, ?, ?, ?));

    $dbh = DBI->connect('DBI:mysql:databaseName')
        or die "couldn't connect to database:" . DBI->errstr;

    $sth = $dbh->prepare($statement)
        or die "couldn't prepare statement:" . DBI->errstr;

    $sth->execute($formItemOne, $formItemTwo, $formItemThree, $formItemFour)
        or die "couldn't execute statement:" . DBI->errstr;

print <<END_HTML;

<html>
<head></head>

<body bgcolor="#ffffff">

Thanks for your input - you entered:<br>
<ul>
    <li>$formItemOne</li>
    <li>$formItemTwo</li>
    <li>$formItemThree</li>
    <li>$formItemFour</li>
</ul>

</body>
</html>

END_HTML

$sth->finish;
$dbh->disconnect;
===================================================

The next script collect all the data from the database and outputs it as 
a (simple) html report.

Hope its useful.
Lee

===================================================
#!/usr/bin/perl -w

use strict;
use CGI ':standard';
use DBI;

print header();

my ($statement, $dbh, $sth, @response, $formItemOne, $formItemTwo, 
$formItemThree, $formItemFour);

$statement = qq(select * from tableName);

    $dbh = DBI->connect('DBI:mysql:databaseName')
        or die "couldn't connect to database:" . DBI->errstr;

    $sth = $dbh->prepare($statement)
        or die "couldn't prepare statement:" . DBI->errstr;

    $sth->execute()
        or die "couldn't execute statement:" . DBI->errstr;

print <<END_HTML;

<html>
<head></head>

<body bgcolor="#ffffff">
END_HTML

    while (@response = $sth->fetchrow_array()) {
        $formItemOne = $response[0];
        $formItemTwo = $response[1];
        $formItemThree = $response[2];
        $formItemFour = $response[3];

print <<END_HTML;
<p>
<ul>
    <li>$formItemOne</li>
    <li>$formItemTwo</li>
    <li>$formItemThree</li>
    <li>$formItemFour</li>
</ul>
END_HTML

    }

print <<END_HTML;
</body>
</html>

END_HTML

$sth->finish;
$dbh->disconnect;