Updating multiple records at same time

[weibs]

I can't figure out why I cannot update multiple records at the same time. Here is my code

    while( @emps = $sth->fetchrow) {
    $MLSNumber  = $emps[0];
    $SatHourFrom = $emps[1];
    $SatMinutesFrom = $emps[2];
        $SatHourTo = $emps[3];
           $SatMinutesTo = $emps[4];
        $SunHourFrom = $emps[5];
        $SunMinutesFrom = $emps[6];
        $SunHourTo = $emps[7];
    $SunMinutesTo = $emps[8];
    $Headline1 = $emps[9];
    $Headline2 = $emps[10];
    $ListingPriceFrom = $emps[11];
    $IncludePlusSign = $emps[12];
    $ListingPriceTo = $emps[13];
    $Description1 = $emps[14];
    $Description2 = $emps[15];
    $StreetAddress = $emps[16];
    $CityName = $emps[17];
    $CityNameOther = $emps[18];
    $AgentName1 = $emps[19];
    $AgentName1Phone1 = $emps[20];
    $AgentName1Phone2 = $emps[21];
    $Agent1Days = $emps[22];
    $AgentName2 = $emps[23];
    $AgentName2Phone1 = $emps[24];
    $AgentName2Phone2 = $emps[25];
    $Agent2Days = $emps[26];
    $MarketedBy = $emps[27];
    $MarketedByDays = $emps[28];
    $bFeatureHome = $emps[29];
        $yahoo = $emps[30];
    $Active = $emps[31];
print <<OH;
<form Action="openhouseAdmin.cgi" Method="post" name="frm1" id ="frm1">
<input type='hidden' name="action" value="activeconfirm2">
<input type='hidden' name="city" value="$input{'city'}">
<input type='hidden' name="MLSNumber" value="$input{'MLSNumber'}">
<tr>
<td><FONT CLASS="BodyBold">&nbsp;$CityName</FONT></td>
<td><FONT CLASS="BodyBold">$StreetAddress</FONT></td>

OH

if ($Active eq "yes") {
$checkedyes = "checked";
$checkedno = "";
} else {
$checkedno = "checked";
$checkedyes = "";
}


print <<OH;
    <td align ="Center"><input type="checkbox" name="Active" value="yes" $checkedyes><input name="MLSNumber" type="Hidden" value="$MLSNumber"></td>
</tr>
OH
}


print <<OH;
</table> <br /><br />
<input type='checkbox' name='checkall' onclick='checkedAll(frm1);'> Check to make all Active, Uncheck to make all Non-Active<br />
<input type="Submit" Name="Submit" Value="Change"></form>
OH

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

and then

if ($input{'action'} eq 'activeconfirm2'){   

if ($input{Active} eq "") {
$input{Active} = "No";
}


        $sth = $dbh->prepare ("SELECT * FROM events where MLSNumber = '$input{MLSNumber}'");
    $sth->execute ();


          
     $sth = "UPDATE events SET Active = '$input{Active}' WHERE MLSNumber = '$input{MLSNumber}'";
    $dbh->do( $sth ) or die( $dbh->errstr );   
 

       
&header;
print <<OH;

        print "  
        <script language=\"JavaScript\">
        <!-- Begin
        var name = navigator.appName;
         if (name == \"Microsoft Internet Explorer\")
        url=(\"openhouseAdmin.cgi?action=active2&city=$input{'city'}\");
        else
        url=(\"openhouseAdmin.cgi?action=active2&city=$input{'city'}\");
        window.location=url;
        // End -->
        </script>
          ";
OH

But that only updates the last record.

Any hints on how to change all records at the same time?

Thanks in advance

 

[weibs]

This is getting frustrating.

I removed the quotes and now I'm getting
Can't call method "MLSNumber" on an undefined value

I printed out the value of $sth and it all looks good.

 

[Annihilannic]

What types of objects are $sth and $dbh? I'm no DBI aficionado, so you might need help from someone else here... but it seems strange to me that you are using $sth in two different ways, first as some kind of object handle, and then to contain a query string?

Annihilannic.

 

[weibs]

I'm no DBI aficionado either. It's a code I copied from another program.

maybe I should name the second one $sth2

I'll give it a shot.

 

[weibs]

Nope, naming the second one $sth2 had no difference.

 

[stevexff]

$dbh is the connection handle. It is used to generate the $sth statement handle, which is then executed to return the results.

You don't need to fetch the rows in order to update them, SQL is a set-level action (although you do need to fetch them to display the screen).

# prepare outside the loop

$sth = $dbh->prepare ("UPDATE events SET Active=? WHERE MLSNumber=?");

for (my $i = 0; $i < $rowcount; $i++) {
   $sth->execute($input{Active$i}, $input{MLSNumber$i});
}

obviously a bit of error handling won't go amiss if this is a production application...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[weibs]

Hi Steve,

I implemented your code but it doesn't update anything. It doesn't throw up any errors either.

 

[stevexff]

Here is a fully functioning example. It only has three columns on the table, because there is a limit to how much time I can waste explaining the same thing over. It reads the table into an array (in your code this is where you produce your form).

Then it loops through the data, flipping the value of Active for each record and updating the table using a prepared SQL statement with placeholder values. This is similar to what you need to do when you update the table from your form input.

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=demo.db') or die $DBI::errstr;
my $sql = 'SELECT MLSNumber, Active, Address FROM tek';

my $data = $dbh->selectall_arrayref($sql);

$sql = 'UPDATE tek SET Active = ? WHERE MLSNumber = ?';
my $sth = $dbh->prepare($sql);

for my $row (@$data) {
   my $active = $row->[1] == 1 ? 0 : 1;
   my $mls = $row->[0];
   $sth->execute($active, $mls);
}

$dbh->disconnect();

This code works; if yours doesn't, perhaps you could consider putting a few print statements in there for illumination?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[weibs]

Well I must do one last post to thank everyone that helped out. I have been taken off the project now, so I won't need anyone's help with this any longer.

Again, I appreciate all your efforts and time in helping.