Update multiple rows in a single query?
6 posts by 3 authors in: Forums > CMS Builder
Last Post: April 21, 2014 (RSS)
By nmsinc - April 12, 2014
I need to update multiple rows with a single query and having some problems - see code below:
<?php //location header - update 'claims_submission' page = auto_assign.php
if (@$_REQUEST['save']) {
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);
}
}
?>
<!-- PHP Form -->
<form action="auto_assign.php?" method="post">
<?php $id = ""; ?>
<?php foreach ($claims_submissionRecords as $record): ?>
<?php $id[]=$record['num']; ?>
<td align="center" height="35" bgcolor="<?php echo $bgColor ?>"><font face="Verdana" size="3">
<!-- Select A Independent Dispatcher -->
<select id="inddispatcher_drop" idd="independent_dispatcher "name="independent_dispatcher[]">
<?php foreach ($independentAdjusterDispatcherDistanceToRecords as $distance => $users): ?>
<?php foreach ($users as $user): ?>
<?php if ($user['user_type'] == "Dispatcher" AND !$user['disabled']): ?>
<option value = "<?php echo $user['num'];?>"><?php echo $user['fullname'];?> - <?php echo $distance != "Distance NA" ? $distance." mi" : 'Distance NA'; ?></option>
<?php endif; ?>
<?php endforeach ?>
<?php endforeach ?>
<?php foreach ($independentAdjusterDispatcherDistanceToRecords as $distance => $users): ?>
<?php foreach ($users as $user): ?>
<?php if (($user['user_type'] == "Dispatcher/Adjuster" OR $user['user_type'] == "Dispatcher/Processor" OR $user['user_type'] == "Dispatcher/Processor/Adjuster") AND !$user['disabled']): ?>
<option value = "<?php echo $user['num'];?>"><?php echo $user['fullname'];?> - <?php echo $distance != "Distance NA" ? $distance." mi" : 'Distance NA'; ?></option>
<?php endif; ?>
<?php endforeach ?>
<?php endforeach ?>
</select>
<!-- //Select A Independent Dispatcher -->
<?php endforeach ?>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
</table>
<p> </p>
<p>
<br/>
<input class="auto-style3" type="submit" name="submit" value="Assign Dispatchers, Adjusters & Processors >>" /><span class="auto-style3">
</span>
</td>
</tr>
</table>
I have only listed the primary code above that I feel I'm having problems with as the entire coded page would be to lengthy for viewers to search through. I feel it is within the '$i", '$id" or 'idd' sets and I can't seem to get a handle on it
Even though rows are present and changes have been made, nothing is updated upon submit and I receive no errors - any ideas on what I'm doing wrong?
Thanks - nmsinc
By Chris - April 14, 2014
Hi nmsinc,
Where are $count, $independent_dispatcher, and $id set? Can you please post the complete PHP source code for your page?
Chris
By gregThomas - April 15, 2014
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
PHP Programmer - interactivetools.com
By nmsinc - April 15, 2014
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
By Chris - April 15, 2014
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?
Chris