DBI MySQL problems - WebmasterForums.NET

Shoshana · 07-31-2006, 01:08 PM

Hi, I've been having trouble with this script from day one. I'm not
sure what the problem is, but I'm trying to query the database with
the form parameters ie: $product_id = $q->param('product_id');. If the
item in question is already in the database, the script should UPDATE.
If not, the script should INSERT. Instead, I get a 500 error. If I
turn the warn flag on, I get:
"Filehandle main::header never opened at add.cgi line 29". and
"Use of uninitialized value at add.cgi line 107". I have totaly
revamped this script about four times, nothing seems to work. Any help
on this would be greatly apprecieated. Thanks in advance.
Cheers. Martinblack.
Below is a copy of the script:

#!/usr/bin/perl

BEGIN {
open (STDERR, ">html/error.txt");

}

### add.cgi
use DBI;
use CGI::Carp qw(fatalsToBrowser carpout);
use CGI;
$q=new CGI;

# input..................................

$io{'company_id'} = $company_id = $q->param('company_id');
$io{'product_id'} = $product_id = $q->param('product_id');
$io{'title'} = $title = $q->param('title');
$io{'description'} = $description = $q->param('description');
$io{'spex'} = $spex = $q->param('spex');
$io{'price'} = $price = $q->param('price');
$io{'sex'} = $sex = $q->param('sex');

# mainline...............................

print header;

$DBname= "dbi:mysql:minmin";
$DBhost = "localhost";
$DBusername = "minmin";
$DBpassword = "fFUZsFtD";

$dbh = DBI->connect("$DBname:$DBhost", $DBusername, $DBpassword ||
die "connection problem: ", $DBI::errstr;
$dbh->{'RaiseError'} = 1;

&kaboom;

&print_output || die "we've got a problem: ";

# Disconnect from the database
$dbh->disconnect || die "disconnection problem: ", $DBI::errstr;
exit;

# the subs...............................

sub print_output{
print<<HTML;
<HTML><BODY>
<CENTER><FONT SIZE=5>
Record added to database
<P>
he he he ha ha
</P>
</FONT></CENTER>
</BODY></HTML>
HTML

}

sub kaboom() {
$sth = $dbh->prepare( q[
SELECT company_id, product_id
FROM product
WHERE company_id = ? and
product_id= ?
] ) || die "prep problem: ", $DBI::errstr;
$sth->execute( $company_id, $product_id ) || die "execution problem:
", $DBI::errstr;

&fetch_results;

if ($newid) {

$dbh->do(do { local $" = ", "; qq[
UPDATE product
SET @{[map "$_ = ?", keys %io]}
WHERE company_id = ? AND product_id = ?
] }, undef,
values %io,
@io{qw(company_id product_id)} );

} else {

$dbh->do(do { local $" = ", "; qq[
INSERT INTO product (@{[ keys %io ]})
VALUES (@{[ ('?') x values %io ]})
] }, undef, values %io );

}

}

sub fetch_results {
while ($x = $sth->fetchrow_hashref) {
$com = $x->{'company_id'};
$pro = $x->{'product_id'};
}
$newid = $com ."-".$pro;