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!

Register Log in
  • Congratulations Mike Lewis 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

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
 
Sort by date Sort by votes
At some point in your program (and it's not in any of the parts you posted) there should be a statement where %input or $input{something} is on the left hand side of an assignment.

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]
 
Upvote 0 Downvote
I am assuming your meaning the parse routine

Code: 
sub ReadParse {
    local (*in) = @_ if @_;
    local ($i, $key, $val);

    if ($ENV{'REQUEST_METHOD'} eq "POST") {
        read(STDIN,$in,$ENV{'CONTENT_LENGTH'});
    }
    else {
        $in = $ENV{'QUERY_STRING'};
        if (!$in) {
            ($in = $ENV{'PATH_INFO'}) =~ s/^\///;
        }
    }
    @in = split(/&/,$in);

    foreach $i (0 .. $#in) {
    # Convert plus's to spaces
        $in[$i] =~ s/\+/ /g;

    # Split into key and value.
        ($key, $val) = split(/=/,$in[$i],2); # splits on the first =.

    # Convert %XX from hex numbers to alphanumeric
        $key =~ s/%(..)/pack("c",hex($1))/ge;
        $val =~ s/%(..)/pack("c",hex($1))/ge;

    # Associate key and value
        $in{$key} .= "\0" if (defined($in{$key}));
             # \0 is the multiple separator
        $in{$key} .= $val;

    }
    return length($in);
}

and I also have

Code: 
sub parse_form {

   read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
   if (length($buffer) < 5) {
         $buffer = $ENV{QUERY_STRING};
    }
   @pairs = split(/&/, $buffer);
   foreach $pair (@pairs) {
      ($name, $value) = split(/=/, $pair);

      $value =~ tr/+/ /;
     $value =~ s/%27/%60/g;
      $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;

      $input{$name} = $value;
   }
}
 
Upvote 0 Downvote
Mmm. Note how in the parse_form code above he loops through all the name/value pairs, storing them in a hash. Hashes don't allow duplicates, so although you may have ten of them, the first nine are overwritten, and you only get the tenth.

Perhaps one of the more CGI-savvy gurus can elp here, but I thought the days of rolling your own form handlers were long gone, and you just put use CGI; at the start of your program instead? (OK, possibly a gross oversimplification, but you get the general idea - don't write your own when someone has already done it better and posted the result on CPAN...)

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]
 
Upvote 0 Downvote
I already do have use CGI at the start of my program and I removed &parse_form; so now it doesn't even look at that sub. But the problem still happens in that it only recognizes the last record and updates only the last one.

 
Upvote 0 Downvote
I guess you guys gave up on me.

Well thanks for trying.

 
Upvote 0 Downvote
I suggest you validate the HTML form that you generate in the first bit. It prints this line for each record:
Code: 
<form Action="openhouseAdmin.cgi" Method="post" name="frm1" id ="frm1">
So your generated form looks a bit like this:
Code: 
<form>
<input type='hidden' name="MLSNumber" value="1">
<form>
<input type='hidden' name="MLSNumber" value="2">
<form>
<input type='hidden' name="MLSNumber" value="3">
[red]<form>
<input type='hidden' name="MLSNumber" value="4">
<input type="Submit" Name="Submit" Value="Change">
</form>[/red]
The only record that gets passed is the last one, because that's the only one in a complete <form> element.

You need to take the opening <form> tag outside the loop, so there's just one form with all the rows in it. Once you've got that straight, you'll have to deal with the fact that you're getting a load of fields passed back with the same names and how to work out which ones go with which records. I don't think your current code will cut it.



-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd
 
Upvote 0 Downvote
Thanks ChrisHunt,

The form element makes sense moving it out of the loop. Now I gotta figure out how to make the field names different for each one.

Seems that nothing can be simple anymore.

appreciated though!!
 
Upvote 0 Downvote
I have it now so the form tag is outside the loop, but I can't figure out how to make each field different.

my input statement is as follows
Code: 
<input type="checkbox" name="Active" value="yes" $checkedyes><input name="MLSNumber" type="Hidden" value="no">

here is my full form code
Code: 
if ($input{'action'} eq 'active2'){
&header; 

print <<OH;
<table border='0' cellpadding='2' cellspacing='0' width='90%'><tr>
<td class='heading2' valign=top colspan=2><br>Active Status</td>
</tr></table>

		<table width="100%" border="0" cellspacing="0" cellpadding="0" align="center">
		<tr><td width="100%" height="40" align="center" class="heading2">Active Status</TD></TR>
		</table>


<form Action="openhouseAdmin.cgi" Method="post" name="frm1" id ="frm1">
<table width="500" border="1" cellspacing="0" cellpadding="1" align="Center">
<tr BGCOLOR="#AEDAF0">
<td align ="center" WIDTH="100"><font class="BodyBold">City</font></td>
<td align ="center" WIDTH="340"><font class="BodyBold">Address</font></td>
<td align ="center" WIDTH="60" colspan=3><font CLASS="BodyBold">Active</font></td></tr>
<tr>
<td align ="center" WIDTH="60"><br></td>
<td align ="center" WIDTH="340"><br></td>
<td align ="center" WIDTH="60" colspan=><font CLASS="BodyBold">Yes</font></td>
</tr>

OH

if ($input{'city'} eq "All")  {
                $sth = $dbh->prepare ("SELECT * FROM events order by CityName");
		$sth->execute ();


}elsif ($input{'city'} eq "Other"){
 
                $sth = $dbh->prepare ("SELECT * FROM events WHERE CityName !='Anchorage' and CityName !='Eagle River' and CityName !='Palmer' and CityName !='Wasilla'");
		$sth->execute ();
}else{
                $sth = $dbh->prepare ("SELECT * FROM events WHERE CityName='$input{'city'}'");
		$sth->execute ();
}


		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; 

<input type='hidden' name="action" value="activeconfirm2">
<input type='hidden' name="MLSNumber" value="$MLSNumber">
<input type='hidden' name="city" value="$input{'city'}">

<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="no"></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();

&footer;
}

Anyone care to take a stab? Please please?
 
Upvote 0 Downvote
So I guess I'm not the only one stumped at this one?
 
Upvote 0 Downvote
In your original input form, try calling the first one MLSNumber1, the second one MLSNumber2, etc. otherwise they will all just clobber each other... ?

Annihilannic.
 
Upvote 0 Downvote
How do I do that if I don't know how many MLSNumber's there will be at any given time?
 
Upvote 0 Downvote
Why do you need to know how many there are? When you generate the original form, just start counting up from 1 until all records are displayed. You can have a hidden input variable specifying the number of rows that you can later use while processing the form so that you know where to stop iterating through the submitted data.

I think the mistake is probably the multiple forms thing... if you break each row into a separate form, I think it will only submit data from one form at a time. So you need one big form with all of the rows of data in it, each having unique variable names to identify them.

Annihilannic.
 
Upvote 0 Downvote
You have totally lost me :(

Is there a way to do that with just one input form?

 
Upvote 0 Downvote
Let me just check that I'm barking up the right tree first. Can you type in an example of what this form will look like? Is this close?

Code: 
 City Name       Street Address                Active
 =============== ============================= ======
 BigVille        1 Large St                     [ ]
 Smalltown       3768 Little Lane               [x]
 Far Away City   Suite 202a, L3, 8 Miles Rd     [ ]

 [ ]  Check to make all Active, Uncheck to make all Non-Active
 
 [Submit]

In which case the form code produced should look something like this (completely untested by the way):

Code: 
<form Action="openhouseAdmin.cgi" Method="post" name="frm1" id="frm1">
<table> 
  <tr>
    <td><FONT CLASS="BodyBold">&nbsp;$CityName</FONT></td>
    <td><FONT CLASS="BodyBold">$StreetAddress</FONT></td>
    <td align ="Center">
	  <input type="checkbox" name="Active1"    value="no">
      <input type='hidden'   name="action1"    value="activeconfirm2">
      <input type='hidden'   name="city1"      value="BigVille">
      <input type='hidden'   name="MLSNumber1" value="1234">
	</td>
    <td align ="Center">
	  <input type="checkbox" name="Active2"    value="yes">
      <input type='hidden'   name="action2"    value="activeconfirm2">
      <input type='hidden'   name="city2"      value="Smalltown">
      <input type='hidden'   name="MLSNumber2" value="3124">
	</td>
    <td align ="Center">
	  <input type="checkbox" name="Active3"    value="no">
      <input type='hidden'   name="action3"    value="activeconfirm2">
      <input type='hidden'   name="city3"      value="Far Away City">
      <input type='hidden'   name="MLSNumber3" value="6756">
	</td>
  </tr>
</table> <br /><br />
<input type='hidden'   name="NumRows" value="3">
<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>

Annihilannic.
 
Upvote 0 Downvote
Here is my example of what the form looks like, so yes, your exactly right on track with that.

Code: 
City Name       Street Address                Active
 =============== ============================= ======
 BigVille        1 Large St                     [ ]
 Smalltown       3768 Little Lane               [x]
 Far Away City   Suite 202a, L3, 8 Miles Rd     [ ]

 [ ]  Check to make all Active, Uncheck to make all Non-Active
 
 [Submit]

However, this list could be 100 or more records long at times.

Your suggestion on the form code wouldn't work. I would have to go into the code everytime they added a listing to add/subtract them. Their listings change weekly and there is no way I have time to change out 100 or more input fields on a weekly basis.

Any other suggestions?

 
Upvote 0 Downvote
I thought that was the whole point of scripting this? Aren't you generating the form dynamically, based on the list of cities in the database? Naturally you wouldn't be expected to update the form manually.

Annihilannic.
 
Upvote 0 Downvote
Right ok, I misunderstood what you were saying.

I guess I don't understand how I would generate the number in the name field .... ie: name="Active1", name="action1", name="city1", name="MLSNumber1" etc...

 
Upvote 0 Downvote
Just add a row counter variable and insert it into the output, something like this (completely untested of course):

Code: 
print <<OH;
	<form Action="openhouseAdmin.cgi" Method="post" name="frm1" id="frm1">
	<table> 
OH

my $rownumber=0;

while( @emps = $sth->fetchrow) {

    $rownumber++;
	
	if ($Active eq "yes") {
		$checked = "checked";
	} else {
		$checked = "";
	}
	
	print << OH;
	<tr>
		<td><FONT CLASS="BodyBold">&nbsp;$CityName</FONT></td>
		<td><FONT CLASS="BodyBold">$StreetAddress</FONT></td>
		<td align ="Center">
		  <input type="checkbox" name="Active$rownumber"    value="no" $checked>
		  <input type='hidden'   name="action$rownumber"    value="activeconfirm2">
		  <input type='hidden'   name="city$rownumber"      value="$CityName">
		  <input type='hidden'   name="MLSNumber$rownumber" value="$MLSNumber">
		</td>
	</tr>
OH

}

print <<OH;
	<table><br /><br />
	<input type='hidden'   name="NumRows" value="$rownumber">
	<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

Annihilannic.
 
Upvote 0 Downvote
Ok, I put in the $rownumber value for the fields. However now when I go to submit the form I get a software error saying
Can't call method "Active" on an undefined value at openhouseAdmin.cgi line 1890.

This is the code that I have for it to post to the record
Code: 
if ($input{Active$rownumber} eq "") {
$input{Active$rownumber} = "No";
}

@active = ("$input{Active$rownumber}");

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

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

Any ideas?

 
Upvote 0 Downvote
I think you need to remove the quotes around 'MLSNumber$rownumber'. You have them nested inside another set of quotes, but a language parser has no way of knowing that, so it'll just see '$input(' as one quoted string, and '}' as another.

If that's not the (only) problem, try printing out the value of $sth before running the SQL so you can make sure it looks right.

Annihilannic.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Mayur09
  • Locked
  • Question
Break array in parts at desired desired string.
Replies
2
Views
90
mikrom
mikrom
whn
  • Locked
  • Question
A simple code runs at cmdline, but not in cron job
Replies
2
Views
118
whn
whn
Mayur09
  • Locked
  • Question
declare multiple strings with blank space in one line 1
Replies
2
Views
121
feherke
feherke
eileen1017
  • Locked
  • Question
rename multiple files in a directory with one command
Replies
2
Views
157
rharsh
rharsh
Haazi2
  • Locked
  • Question
Concatenate based on 1st column in a file of records
Replies
1
Views
57
prex1
prex1

Part and Inventory Search

Sponsor

Back
Top