remluvr has asked for the wisdom of the Perl Monks concerning the following question:

Hi everyone! I'm back playing with Perl but it looks like I will never learn :)
My problem now is the following: I have a script which gathers data from a online form and need to update a table after it. The data is some sort of a form. I have a table with 100 rows, every of which is composed by 3 columns: the first one is the data to eveluate, the second column contains a yes/no selection and the third one provides a alternative evaluation if the answer in the second column is no.
This part works and it looks like the selected data are correctly placed inside three arrays. But now I have problem updating the table, since I only succed in updating one data, instead of them all. My code for the updating part is the following:
sub framerightsave{ my($dbhsource,$dbhinstance,$dbhadmin,$firstfield,$ynfield,$thirdfi +eld,$save,$op) = @_; my(@inst,@altern,@yn,$curent,$curinst,$curalt,$curyn,$htmld,$n,$sa +vedata); @yn = split(/\|/,$ynfield); @altern = split(/\|/,$thirdfield); @inst = split(/\|/,$firstfield); print "<table border=1 bgcolor=#cbe8fc border=0 cellspacing=0 cell +padding=2 width=100%>"; print "<tr bgcolor=#fce7ca><td colspan=4><font size=-1 face=verdan +a >Save evaluations of instances: $instance</font></td></tr>"; print "<tr bgcolor=#fce7ca><td><font size=-1><b>Istanza</td><td><f +ont size=-1><b>Saved evaluation</td><td><font size=-1><b>Alternative +evaluation</td></tr>"; my $mysqldbinstance=$frameinit::MYSQLDBINS; $dsninstance = "DBI:mysql:database=$mysqldbinstance;host=$myhost"; $dbhinstance = DBI->connect($dsninstance, $mysqluser, $mysqlpwd)|| + print "NON RIESCO A CONNETTERMI\n"; while(@inst){ $curent = shift @inst; $curyn = shift @yn; $curaltern = shift @altern; if($curyn =~ /\w+/){ &updatemysql($curent,$curyn,$curaltern); } print "<tr bgcolor=$col><td><font size=-1><b>$curent</td><td>< +font size=-1><b>$curyn</td><td><font size=-1>$curaltern</td></tr>"; } print "</table>"; } sub updatemysql{ my($curent,$curyn,$curaltern) = @_; my($update,$mysqldbinstance,$dsninstance, $dbhinstance, $sthinstan +ce,$rvinstance,$toupdt); my $mysqldbinstance=$frameinit::MYSQLDBINS; $dsninstance = "DBI:mysql:database=$mysqldbinstance;host=$myhost"; $dbhinstance = DBI->connect($dsninstance, $mysqluser, $mysqlpwd)|| + print "NON RIESCO A CONNETTERMI\n"; if($curyn =~ /yes/){ $toupdt= $curyn; } else{ if ($curaltern =~/\w+/) { $toupdt= $curyn.'_'.$curaltern; } else{ $toupdt= $curyn; } } if($curyn =~ /\w+/){ $update = "update inst_3 set u1 = \"$toupdt\" where object = \ +"$curent\""; $sthinstance = $dbhinstance->prepare($update); $rvinstance = $sthinstance ->execute(); if ( !defined($rvinstance) ){ print "exec:$DBI::errstr, $select\n"; } } }
Where am I going wrong? Thanks everyone for the very much needed help

Replies are listed 'Best First'.
Re: Problem updating mysql table
by Gangabass (Vicar) on Dec 28, 2010 at 14:34 UTC

    You should use placeholders:

    $update = qq{UPDATE inst_3 SET u1=? WHERE object=?"; $sthinstance = $dbhinstance->prepare($update); $rvinstance = $sthinstance ->execute( $toupdt, $curent );
      I have now, but I get the very same results, it only updates the first entry in the table, instead of them all. Anyway, I guess your way of writing the query is better than mine, so thanks for pointing it out!

        How do you check that it only updates the first row? Are you sure your variables you put into the where clause contain what they should? Are you sure your database table contains the values it should?