Ive got this following cgi code and it dont wanna seem to work, it inserts a blank record into the MySQL database, the ID field is an auto number field



my $dbh = DBI->connect($dsn, $username, $password);

my $sql_appendstatement = "INSERT INTO `TournamentDates` (`ID`, `Course`, `CourseLoc`, `CompType`, `CompDay`, `CompDate`, `TeeOff`, `Finished`, `Order`) VALUES ('', '$FORM{Coursenew}', '$FORM{Locationnew}', '$FORM{Typenew}', '$FORM{Daynew}', '$FORM{Datenew}', '$FORM{TeeOffnew}', '0', '$FORM{Ordernew}')";


my $sth = $dbh->prepare($sql_appendstatement) || die "Could not prepare: " . $dbh->errstr();


$sth->execute() || die "Could not execute: " . $dbh->errstr();
$sth->finish();
$dbh->disconnect();

All the $FORM{*} values contain values cause ive put a print statement after it runs the sql statement with the value of the sql string and it printed out.



INSERT INTO `TournamentDates` (`ID`, `Course`, `CourseLoc`, `CompType`, `CompDay`, `CompDate`, `TeeOff`, `Finished`, `Order`) VALUES ('', '5555555', '5576g7657657', '5g7765765', '76yf', '5765757567', 'fhfgfg', '0', '1')



Whats wrong with that?? why wont it work right? ive copy and pasted the above sql string into myphpadmin and executed it and it appends the record fine, but it wont append it from my script. well it appends it but its blank. ??

Hello TheIceman,

Generally single quotes act differently from double quotes.

So : '$Form{Coursenew}' may not be a correct usage here.

Try : $Form{'Coursenew'}

example might be

my $bob = 'happy';

print "Bob = $bob";

print 'Bob = $bob';

Hope this helps

cornball

nope that didnt work, just gives me sql syntax errors.

the $FORM{Course} is a variable passed into the script from a submit button.

The code i use to get all those values is

read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
if($buffer eq ''){$buffer = $ENV{'QUERY_STRING'};}
@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ s/\r//g;
$value =~ s/\n//g;
$FORM{$name} = $value;

Then i use the sql to insert it into the database. Heres all the code for that function, if anyone can tell me a better way to do it then say do.

for($count=$minid;$count<=$maxid;$count++){

$ID = $count;

$newCourse = "Course$ID";
$newLocation = "Location$ID";
$newType = "Type$ID";
$newDay = "Day$ID";
$newDate = "Date$ID";
$newTeeOff = "TeeOff$ID";
$newOrder = "Order$ID";
$newFinish = "Finish$ID";
$newDelete = "Delete$ID";
if($FORM{$newFinish} eq 'on'){$Finished = 1} else {$Finished = 0}

my $sql_update="UPDATE TournamentDates SET TournamentDates.Course = '$FORM{$newCourse}', TournamentDates.CourseLoc = '$FORM{$newLocation}', TournamentDates.CompType = '$FORM{$newType}', TournamentDates.CompDay = '$FORM{$newDay}', TournamentDates.CompDate = '$FORM{$newDate}', TournamentDates.TeeOff = '$FORM{$newTeeOff}', TournamentDates.Finished = '$Finished', TournamentDates.Order = '$FORM{$newOrder}' WHERE (((TournamentDates.ID)=$ID))";
my $sth = $dbh->prepare($sql_update) || die "Could not prepare: " . $dbh->errstr();
$sth->execute() || die "Could not execute: " . $dbh->errstr();
$sth->finish();
if($FORM{$newDelete} eq 'on'){
my $sql_delete="DELETE FROM `TournamentDates` WHERE `ID` = '$ID' LIMIT 1";
my $sth = $dbh->prepare($sql_delete) || die "Could not prepare: " . $dbh->errstr();
$sth->execute() || die "Could not execute: " . $dbh->errstr();
$sth->finish();
}
}

$dbh->disconnect();



All the other sql statements i have work ok, the update and delete work, just the insert doesnt. thinking maybe i use an update statement after i insert a blank line if no one can help me out here.

Hello TheIceman,

Sorry about the errors.

I created a simple test and mine worked fine.

The only thing I can think of is case sensitive form names.

my test form had only one field named "one" and the value came across into $FORM{'one'}

my $insert_sql="INSERT INTO $table (id,one) VALUES ('','$FORM{'one'}')";

my $insert_sql="INSERT INTO $table (id,one) VALUES ('','$FORM{one}')"; also worked

$FORM{One} is different from $FORM{one}.

hope this helps

cornball

i dont think its case sensitivity, i will keep looking to see what i can find.

Hello!

Try the following by wrapping your my statement with a pipe:



my $sql_appendstatement = qq|"INSERT INTO `TournamentDates` VALUES ('', '$FORM{Coursenew}', '$FORM{Locationnew}', '$FORM{Typenew}', '$FORM{Daynew}', '$FORM{Datenew}', '$FORM{TeeOffnew}', '0', '$FORM{Ordernew}')"|;


Instead of `TournamentDates` you could also use simply TournamentDates

Also in the my statement you could simply use ? instead of '$FORM{Daynew}' and then later in the prepare use the entire line inbetween ( and )! So it would look like :

my $sth = qq|"INSERT INTO TournamentDates VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"|;

$sth->execute("", $FORM{Coursenew}, $FORM{Locationnew}, $FORM{Typenew}, $FORM{Daynew}, $FORM{Datenew}, $FORM{TeeOffnew}, "0", $FORM{Ordernew}) || die "Could not execute: " . $dbh->errstr();

Now if you want to find if there is any problem with the $Form values, there is nothing to prevent you in making the INSERT only a fixed value like "please or "solve" or "my" or "problem" instead of $FORM{value}! Here there is no value to be given as an output to the INSERT statement.

Also this problem implies that your insert statement did not have any value and therefore it could not insert of because the number of the columns where not matching the insert matrix.

INSERT will be executed IF AND ONLY IF the insert format, in your case nine columns, is equal to the table column fields if they are nine. Otherwise there will be simply a mysql_insertid generated and the fields will not be inserted.

You could also use in place of the autoincrementfield "NULL" for it not to insert anything but increase the number forcefully.

That won't work because you left the quotes around the insert statement inside the pipes.

But either way using qq|| vs " " shouldn't really make any difference.