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

 

[ggriffit]

At a guess I would suggest that $sth is only being processed at the end of the loop, what do you get if you do the following :

$sth = $dbh->prepare ("SELECT * FROM events where MLSNumber = '$input{MLSNumber}'");
$sth->execute ();
print $sth
          
$sth = "UPDATE events SET Active = '$input{Active}' WHERE MLSNumber = '$input{MLSNumber}'";
print $sth
$dbh->do( $sth ) or die( $dbh->errstr );

Also your Javascript :

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;

is identical for both browsers.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[weibs]

I added in the print $sth as you suggested, but
It gives me an error saying

Not a GLOB reference at openhouseAdmin.cgi line 1898.

line 1898 is the first print $sth after
$sth->execute ();

 

[weibs]

And if I add in a semicolon after the print $sth, then it only updates the last record again.

 

[weibs]

As for the javascript being identical, I just don't know how to redirect the page any other way at this time.

 

[ggriffit]

As far as the JS is concerned, as both the IE and non IE link are the same, I'd reduce it to the following :

url=(\"openhouseAdmin.cgi?action=active2&city=$input{'city'}\");
window.location=url;

although a quick Google for "perl redirect url" yields several examples similar to

http://theos.in/perl/how-to-redirect-url-with-perl-script/

As for the PERL code, I'm not sure what the SELECT is doing as you don't seem to do anything with the data and its a select so its not modifying any data, can you shed any light on this ?

What I'm looking for with the prints is to dump the SQL out to see what the SQL is that is being generated as this may shed any light on the problem you are having.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[stevexff]

Can I just check my understanding - you create a form from your employee table, each row of which has an MLSNumber and a checkbox that shows/sets whether it's active. All of these have the same names, so when the browser sends the form back, won't CGI put these in an array? Your code seems to expect that $input{'active'} is a scalar, won't it be an array value?

Also, I don't understand why you fetch the rows from the database before you update them - especially as you don't actually do anything with the result. And you should use a prototype statement with '?' in it to stop SQL injection attacks - your current code is wide open to abuse as it stands...

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]

 

[stevexff]

I'd make sure you fix that SQL statement - I just saw your other thread where you posted the live URL for the site...

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

while (some code to iterate over all your MLSNumbers) {
   $sth->execute($active, $MLSnum);
}

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]

ggriffit... thank you for the tip on the redirect url. much appreciated.

As for the select statement, I just thought it had to be there to prepare the table for input. I guess I'm wrong?


stevexff .... right, I have a form for the administrator to be able to check all or none for active status on the events table. Each row has an mlsnumber, active, and several other fields. But I'm only wanting using active in this particular form. Yes, each active field has the same name as well.

I would guess it should be an array, but I have to confess, I'm no expert here and I'm not sure how to put it into an array.

I fetched the rows, so my script could view the mlsnumbers. and associate them with the specific row in the form.

"And you should use a prototype statement with '?' in it to stop SQL injection attacks"
Oh my, how do I fix this?

As for seeing the live url in the other thread, that was a total foopah on my part and there is no way to edit my posts

You'll have to forgive me guy, I'm coming off a recent brain injury and it's effected my programming skills

So I thank you and appreciate all the help you guys have given.

 

[Annihilannic]

You can "red flag" your own post using the link underneath it, and in the red flag reason just ask the site admins to change what you want changed. They usually react very quickly.

Annihilannic.

 

[weibs]

Thank you very much Annihilannic. I have done the Red Flag on it so hopefully they will remove it.

Appreciated!!

 

[stevexff]

weibs, I'm not giving you a hard time, just advice. My previous post shows you how to prepare an SQL statement with ? placeholders - this precompiles the SQL and allows you to supply the values as positional parameters on the execute statement. This is a lot safer than what you are doing at the moment. It also works well here, as you have a bunch of MLSNumber/status pairs and you need to iterate over them to update the rows on the table.

To help you visualise what's in your $input hash you could try Data:umper

use Data::Dumper;
print Dumper($input);

(not sure how well this will work in a web setting - maybe one of the other gurus can advise) but it will allow you to see what data you have in there and how it is structured.



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]

 

[ggriffit]

Preparing is fine as per stevexff's examples, if you want to get the SQL just print the SQL and then the variables you pass in as params before you use them and putting the two together you should be able to see what SQL is being passed to the DB in case that is the cause of your issue.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[weibs]

If I use

use Data:umper;
print Dumper($input)

Then it prints out
$VAR1 = undef;

OR
if I use this
$sth = $dbh->prepare ("SELECT * FROM events where MLSNumber = '$input{MLSNumber}'");
$sth->execute ();
print $sth

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

it prints out
DBI::st=HASH(0x9760098)UPDATE events SET Active='No' WHERE MLSNumber='99999915'
Which that is the last event in the form. And it does save that last event to the database.

I totally don't understand what is happening here.

 

[stevexff]

My bad. Should be

print Dumper([red]%[/red]input);

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]

If I add in
print Dumper(%input);

I get a software error saying
Undefined subroutine &main:umper called at openhouseAdmin.cgi line 1902.

 

[Annihilannic]

You still need the use Data:umper; line...

Annihilannic.

 

[weibs]

Ahh thanks,

Ok, now this is what it prints out

$VAR1 = 'city'; $VAR2 = 'All'; $VAR3 = 'Submit'; $VAR4 = 'Change'; $VAR5 = 'Active'; $VAR6 = 'No'; $VAR7 = 'MLSNumber'; $VAR8 = '99999915'; $VAR9 = 'action'; $VAR10 = 'activeconfirm2';

Which again is the last record in the form data.

 

[stevexff]

Where does %input get set?

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 stevexff,

I'm not sure what you mean by
Where does %input get set?

Please explain.