Pasted below is my Perl script to generate a data entry form on a
webpage, process the data when Submit is clicked, and insert the data
into my MySQL database.

Now, I'm trying to do the opposite, and display query results from
same database to a webpage, but my Perl script isn't working. I need a
simple Perl script to "SELECT * FROM STAFF" and display those results
on a webpage.

"insert.pl" script: (currently functional)
#!/usr/bin/perl 
use DBI; 
use CGI; 
my $q = new CGI; 
 
# Definitions
my $table = "STAFF";
my $db = "test";
my $user = "rick";
my $password = "rick";
# end definitions

if ($q->param('name') eq "") {&printform()} else {&results()}

sub printform {

print $q->header;
print $q->start_html (-title=>'test database',
				-BGCOLOR=>'black',
				-TEXT=>'white');
				
print "<CENTER><H1>Test Database</H1></CENTER><HR>";
print $q->startform;

print "Name: ", $q->textfield(-name => name,
				-size => 16), "<BR>";
print "Phone: ", $q->textfield(-name => phone,
				-size => 16), "<BR>";
print "Address: ", $q->textfield(-name => address,
				-size => 16), "<BR>";
print "Age: ", $q->textfield(-name => age,
				-size => 16), "<BR>";
print $q->submit;
print $q->endform;
print $q->end_html;
}

sub results {

my ($name, $phone, $address, $age);
$name = $q->param('name');
$phone = $q->param('phone');
$address = $q->param('address');
$age = $q->param('age');

print $q->header;
print $q->start_html (-title=>'Database Results',
			-BGCOLOR=>'black',
			-TEXT=> 'white');

# Tell script we will use a MySQL database
my ($drh, $dbh);
$drh = DBI->install_driver( 'mysql' );

# Establish connection with database
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost","rick","rick",
					{'RaiseError'=> 1});

#Check connection
if (!$dbh) {
	print "Cannot connect: $DBI::errstr<BR>";
	print $q->end_html;
	die;
}

#Build and do SQL statement
my $sql_name = $dbh->quote($name);
my $sql_add = $dbh->quote($address);
my $sql_ph = $dbh->quote($ph);
my $sql_age = $dbh->quote($age);

#$SQLstatement = "insert ($sql_name)";
$SQLstatement = "insert into $table (name, phone, address, age) values
($sql_name, $sql_ph, $sql_add, $sql_age)";
#print "$SQLstatement";
$dbh->do($SQLstatement);

print "$name added to database<br><br>";
print $->end_html;
}

"select.pl" Perl script (currently non-funcitonal--i.e. "Internal
Server error--'can't call method 'header' " results when accessed from
browser

I need the "select.pl" script to work. Please give working coe only,
no tutorial links or technological alternatives.

Thank you

---

Request for Question Clarification by sgtcory-ga on 08 Nov 2002 10:52 PST

Can you paste a copy of the non-functional 'select.pl' perl code here as well?

Thanks -

SgtCory

---

Clarification of Question by omniscientbeing-ga on 08 Nov 2002 11:27 PST

Sorry, I meant to paste the non-funcitonal code as well. Here it is:

#! /usr/bin/perl -w
# connect to MySQL, retrieve data, write HTML output
use DBI;
use CGI;
my ($dbh, $sth);

#establish connection to database
$dbh = DBI->connect("DBI:mysql:database=test;host=localhost","rick","rick",
					{'RaiseError'=> 1});

#MySQL database query
$sth = $dbh->prepare ("SELECT * FROM STAFF");
$sth->execute ();

#generate HTML page with query results
print $q->header;
print $q->start_html (-title=>'test database',
				-BGCOLOR=>'black',
				-TEXT=>'white');
$sth->finish ();
$dbh->disconnect ();
exit (0);

Hello omniscientbeing,

Since I don't have the ability to test this on your server, please ask
for further clarification if this does not work, and I will modify
until it does. I have a working example calling a database of my own
with this code. You can see it here :

http://www.sgtsearch.co.uk/cgi-bin/test.pl
(Let me know once you had a chance to see it, so I can take it down.)

I used the same array method that alek used. Your original problem?
(you are going to kick yourself ;-) You forgot to define $q. The
script fails because it can't process a function that doesn't exist.
Here you go:

#! /usr/bin/perl
# connect to MySQL, retrieve data, write HTML output 
use DBI; 
use CGI;
my $q = new CGI; #defining
my (@ary); #thanks to alek for the array :-)
my ($dbh, $sth);
 
#establish connection to database 
$dbh = DBI->connect("DBI:mysql:database=test;host=localhost","rick","rick",
{'RaiseError'=> 1});
 
#MySQL database query 
$sth = $dbh->prepare ("SELECT * FROM STAFF"); 
$sth->execute (); 
#generate HTML page with query results
print $q->header;
print $q->start_html (-title=>'Here you go omniscientbeing',
-BGCOLOR=>'black',-TEXT=>'white');

print "<table>\n"; # manipulte to look like you want it
 
 # read results of query, then clean up 
 while (@ary = $sth->fetchrow_array ()) 
 { 
  print "  <tr>\n"; 
  foreach(@ary) 
  { 
   print "    <td>$_</td>\n"; 
  } 
  print "  </tr>\n"; 
 } 
print "</table>\n";

$sth->finish (); 
$dbh->disconnect (); 
exit (0);

Thanks for the question. I rarely get to work with Perl anymore in
this PHP world :-)

SgtCory

Thank you very much, sgtcory-ga! It works, it was the declaration!
Also thanks to alekb-ga for his work on the array.

I haven't testing this but it should be very close.  Copy insert.pl to
select.pl, then replace the results() sub with the following:

###########################################################

sub results { 
 
	my ($dsn) = "DBI:mysql:database=test;host=localhost"; # data source
name
	my ($user_name) = "rick";  # user name
	my ($password) = "rick";         # password
	my ($dbh, $sth);             # database and statement handles
	my (@ary);                   # array for rows returned by query



	print $q->header; 
	print $q->start_html (-title=>'Database Results', 
	   -BGCOLOR=>'black', 
	   -TEXT=> 'white'); 


	# connect to database
	$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1
});

	# issue query
	my $query = qq{ SELECT * FROM STAFF };
	$sth = $dbh->prepare ( $query );
	$sth->execute ();

	print "<table>\n";

	# read results of query, then clean up
	while (@ary = $sth->fetchrow_array ())
	{
		print "  <tr>\n";
		foreach(@ary)
		{
			print "    <td>$_</td>\n";
		}
		print "  </tr>\n";
	}
	$sth->finish ();

	$dbh->disconnect ();
}

alekb-ga,

I want the select action to be its own separate script, so that the
data entry (inset.pl) is a separate URL from the show query URL
(select.pl).

thanks,

~omniscientbeing-ga