Using "foreach" Loop With Database "UPDATE" Function

Volitics

The scripts below are excerpts from a script on which I am working. The script is working - but only partly. What I am wanting to do is update the Remove column with a Y in each of the rows in the table where the Reference[] checkbox is checked. The foreach loop works in that it will loop through each of the Reference[] variables and output the values (1,3,5,7,9,11) just exactly as it should.

However, the problem is that it will update only the LAST ROW in the database table. I can't figure out why it will update only one row in the database table since it is making all of the iterations through the loop.

I've been wrestling with this problem since 9:00 a.m. this morning. It's now 11:00 p.m. and I still don't have it figured out.

I would appreciate any assistance that anyone could provide.

//------------ FirstPage.php --------------------------
<form method="post" action="SecondPage.php"><table>
<tr><td><input type="checkbox" name="Reference[]" value="1"></td><td>John</td></td></tr>
<tr><td><input type="checkbox" name="Reference[]" value="3"></td><td>Fred</td></tr>
<tr><td><input type="checkbox" name="Reference[]" value="5"></td><td>Joe</td></tr>
<tr><td><input type="checkbox" name="Reference[]" value="7"></td><td>Irma</td></tr>
<tr><td><input type="checkbox" name="Reference[]" value="9"></td><td>Sally</td></tr>
<tr><td><input type="checkbox" name="Reference[]" value="11"></td><td>Bob</td></tr>
<tr><td colspan="25"><input type="hidden" name="Remove" value="Y">
<input type="submit" name="UpdateTable" value="Click Here To Update"></td></tr>
</table></form>

//---------------- SecondPage.php ---------------------
if (isset($UpdateTable)){
require "../../quote/conf/config.inc.php";
$TableName = "TypeInsurance"; 
//It should update each $Value but strangely it updates only the last $Value in the array.
foreach ($_POST[Reference] as $Key => $Value){ 
$Query = "UPDATE $TableName SET Remove='$Remove' WHERE Reference='$Value'";
echo "<b>$Value</b>,"; //This is to test the loop, it outputs 1,3,5,6,9,11 - exactly as it should.
}
$Result = mysql_db_query ($db_info[dbname], $Query, $db_connection);
if ( !$Result )
	die ("Couldn't update:".mysql_error());
echo "<b>Table updated ".mysql_affected_rows()." row(s) changed</b><p>";
mysql_close( $db_connection );
}

Thank you in advance.

Volitics

dustbuster

You are calling the mysql_query outside of the foreach loop so it goes through the loop and writes over $Query each time until the last one

foreach ($_POST[Reference] as $Key => $Value){  

$Query = "UPDATE $TableName SET Remove='$Remove' WHERE Reference='$Value'"; 
echo "<b>$Value</b>,"; //This is to test the loop, it outputs 1,3,5,6,9,11 - exactly as it should. 
} 
$Result = mysql_db_query ($db_info[dbname], $Query, $db_connection); 
if ( !$Result ) 
    die ("Couldn't update:".mysql_error()); 
echo "<b>Table updated ".mysql_affected_rows()." row(s) changed</b><p>"; 
mysql_close( $db_connection );

should be

foreach ($_POST[Reference] as $Key => $Value){  

$Query = "UPDATE $TableName SET Remove='$Remove' WHERE Reference='$Value'"; 
echo "<b>$Value</b>,"; //This is to test the loop, it outputs 1,3,5,6,9,11 - exactly as it should. 

$Result = mysql_db_query ($db_info[dbname], $Query, $db_connection); 
if ( !$Result ) 
    die ("Couldn't update:".mysql_error()); 
echo "<b>Table updated ".mysql_affected_rows()." row(s) changed</b><p>"; 
}
mysql_close( $db_connection );

Volitics

dustbuster,

Thank you for your kind help.

Best Regards;

Volitics

Boudga

based on your explanation above, I modified your code to work with my project and the output was:

Array ( [firstname] => [lastname] => [org] => [email] => [telephone] => [Send] => Send [searchvalue] => 1040 [check] => Array ( [0] => 161 [1] => 543 [2] => 756 [3] => 194 ) )

the nested Array data are my checked checkbox's with appropriate data in them how do I make them variables that can be used in my INSERT statement

here's the INPUT: <INPUT Type=\"Checkbox\" Name=\"check[]\" Value=\"".$row['VDN_PKey']."\">

dustbuster

There are a number of ways to turn the check boxes into variables for use in an update statement.

The easiest is to go into your html form and change the name of each check box to the name of the field it will be inserted into.

So instead of name="check[]" it would be name="field_name".

If you can't do this for some reason though you can try:


foreach ($_POST['check'] as $value){
     $tmparray[]=$value;
}

//we can now reference the variables like this
$Query="UPDATE table SET field_name_1='$tmparray[0]' WHERE condition_field='$_POST[searchvalue]'

When doing the update I have found that mysql doesn't like multidimensional arrays so that is why I put the values in a tmparray