Update multiple rows in a single query?

Hi nmsinc,

Where are $count, $independent_dispatcher, and $id set? Can you please post the complete PHP source code for your page?

Hi nmsinc,

I've looks as if you're mysql update statement is calling variables that don't exist. I can't see where the following variables are set:

• $independent_dispatcher
• $independent_adjuster

Do you know where these are being set? What should they contain?

It also looks as if the $ide variable is being set after the MySQL update statement is called.

Thanks!

Greg

Hi Greg,

I highlighted in blue the variables that you could not find in the previous post - they are set by a select command and listed by the distance mapping routine just prior to the selections.

As far as the $ide variable, it looks as if it is set prior to the update command and not after it as I have a if (@$_REQUEST['save']) {} function that manages this in the header!

Any help would be much appreciated!

Thanks - nmsinc

Oh, you're using register_globals, which sets global variables from form data! Note that register_globals has long been considered a security weakness and was turned off as a default in PHP 4.2.0, deprecated in PHP 5.3.0, and removed completely from PHP 5.4.0. It's highly recommended that you use $_REQUEST to access form data instead, and additionally turn off register_globals in your php.ini. Even if you can't turn it off on your server (e.g. because other legacy code requires it) it's best to use $_REQUEST in any new code so that at your new code will survive a server move or a PHP upgrade. There's more information on register_globals here: http://www.php.net/manual/en/security.globals.php

The reason why your loop isn't working is that your $count variable hasn't been set, so it's effectively looping zero times.

You can get a count of an array by using the count() function on the array, like this:

$count = count( $independent_dispatcher );

Assuming that your three arrays will have the same number of elements, you can do this:

if ( @$_REQUEST['save'] ) {
  $count = count( $independent_dispatcher );
  for ( $i = 0 ; $i < $count; $i++ ) {
    $sql1 = "UPDATE 'claims_submission' SET independent_dispatcher='$independent_dispatcher[$i]', independent_adjuster='$independent_adjuster[$i]' WHERE idd='$id[$i]'";
    $result1 = mysql_query($sql1);
  }
}

...but you should really escape those variables, otherwise a visitor can perform an SQL injection attack:

if ( @$_REQUEST['save'] ) {
  $count = count( $independent_dispatcher );
  for ( $i = 0 ; $i < $count; $i++ ) {
    $sql1 = mysql_escapef("UPDATE 'claims_submission' SET independent_dispatcher=?, independent_adjuster=? WHERE idd=?", $independent_dispatcher[$i], $independent_adjuster[$i], $id[$i]);
    $result1 = mysql_query($sql1);
  }
}

...and finally, without using register_globals — a little longer, but safer and a lot more readable for fresh eyes:

if ( @$_REQUEST['save'] ) {
  $count = count( $_REQUEST['independent_dispatcher'] );
  for ( $i = 0 ; $i < $count; $i++ ) {
    $sql1 = mysql_escapef("UPDATE 'claims_submission' SET independent_dispatcher=?, independent_adjuster=? WHERE idd=?", $_REQUEST['independent_dispatcher'][$i], $_REQUEST['independent_adjuster'][$i], $_REQUEST['id'][$i]);
    $result1 = mysql_query($sql1);
  }
}

Does that help?

Hi Chris,

Got it work with your idea!

Thanks - nmsinc