Home: Perl Programming Help: DBI:
Perl CGI script Mysql Help


Jul 23, 2013, 5:30 PM

Views: 27680
Perl CGI script Mysql Help

I'm having an issue connecting to the database or maybe it's the query? But when this script is executed I receive a blank page without results. I greatly appreciate if you could look over this.

Database name: perltest
table name: schedule
Columns: name(varchar),day(varchar),number(int)


#use statements
use CGI;
use DBI;
my $q=new CGI;


my $sth;
my $sql;
my $sn;
my $name;
my $day;
my $number;
my @data;

#make the page output

print $q->header( "text/html" ),
$q->start_html( -title => "Printing the table", -bgcolor => "#ffffff" ),
$q->p(" The following data is provided" ),

# HTML for the start of the table

print "<table border=\"1\" with=\"800\">\n";

# print table columns headers

print "<tr><td>Name</td><td>Day</td><td>Number</td></tr>\b";

#connect to the database
my $dbh = DBI->connect('DBI;mysql:perltest','root','password',{RaiseError=>1});

#prepare statements

my $query = qq{
select * from schedule};
$sth = $dbh->prepare($query);

#execute the data insert

#retrieve the values returned from SQL statements
while (@data = $sth->fetchrow_array()) {

$name = $data[0];
$day = $data[1];
$number = $data[2];

#print table rows

print "<tr><td>$name</td><td>$day</td><td>$number</td></tr>\n";

# close table
print "</table>\n";



Veteran / Moderator

Jul 24, 2013, 6:55 AM

Views: 27651
Re: [Alaskan5] Perl CGI script Mysql Help

The first 2 use statements should always be:

use strict; 
use warnings;

When developing/debugging CGI scripts, you should also include:

use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

Vars should be declared in the smallest scope they require and close to where they're first used.

$q->end_html should be part of the last output, not prior to your table output.

You should learn about the different methods of quoting so that you can reduce or eliminate the unnecessary escaping of the quotes.

Your connect statement should include an "or die" clause/statement because the RaiseError won't be in effect until after the successful connection. I see a semi-colon in that statement when it should be a colon.

You almost never want to do this: select * from

Instead, you should state which fields you want. It's not uncommon to alter the order of the fields in the db, which then alters order of the fields being selected when using select * from and that would introduce a bug in your script.

Since you're only selecting 3 fields, I'd specify those vars when fetching the data rather than using the @data array which you then use to copy the data.


Jul 24, 2013, 10:55 AM

Views: 27645
Re: [FishMonger] Perl CGI script Mysql Help

I greatly appreciate the help! I was able to fix it.

Websites/Forums like this deserve more credit