Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating multiple records at same time

Status
Not open for further replies.

weibs

Programmer
Dec 17, 2008
61
US
I can't figure out why I cannot update multiple records at the same time. Here is my code

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

Code:
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
 
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.

 
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.
 
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.

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

 
$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).
Perl:
# 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::perlDesignPatterns)[/small]
 
Hi Steve,

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

 
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.
Perl:
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::perlDesignPatterns)[/small]
 
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.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top