update db with foreach

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wendy
Forum Newbie
Posts: 4
Joined: Mon Dec 09, 2013 2:13 am

update db with foreach

Post by wendy »

Hi there
I've problems with an do while slope.
I want to update my database but it doesn't work. I want to click the ones they have paid. Can somebody help me?

The code:

Code: Select all

if (isset($row_master['fee'])) {
$fee[] = $row_master['fee'];} 
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
foreach ($fee as $v) {	
$updateSQL = "UPDATE table (fee) VALUES ('$v')";                       
mysql_select_db($database_tool, $tool);
 $Result1 = mysql_query($updateSQL, $tool) or die(mysql_error());
 }
}
and the html-code:

Code: Select all

  <?php do { 
  	?>
    <tr>

       <td><?php echo $row_master['ID']; ?>&nbsp; </td>
    <td><input type="checkbox" name="fee[]" value='1' <?php if ($row_master['fee'] == 1) echo 'checked="checked"';?> />  
    <?php } while ($row_master = mysql_fetch_assoc($master));  ?>
</table>
 <input type="submit" value="update" />  </td>
          <input type="hidden" name="MM_update" value="form1" />
</form>
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: update db with foreach

Post by requinix »

Code: Select all

$updateSQL = "UPDATE table (fee) VALUES ('$v')";
That is not an UPDATE statement. It needs to be in the form

Code: Select all

UPDATE table SET fee = (value) WHERE (conditions)
"value" would be the $v values, but you need to fill in the "conditions" part. Which requires knowing the ID values.

You've got arrays in the form already, that's great, but you need to include the IDs in there as well. Use them as the array keys:

Code: Select all

input type="checkbox" name="fee[<?php echo $row_master['ID']; ?>]" value='1' <?php if ($row_master['fee'] == 1) echo 'checked="checked"'; ?> />
From there I'm not sure what your PHP should be. Probably

Code: Select all

foreach ($_POST["fee"] as $id => $fee) {
    $updateSQL = "UPDATE table SET fee = " . ($fee ? 1 : 0) . " WHERE ID = " . (int)$id; // don't forget about sql injection!
One more thing: your UPDATE should reflect the same conditions that you used in the SELECT that you ran to get all those ID/fee things to update. If you didn't have any conditions then great, but if you did then not including them in the UPDATE could mean the user could set the fee on things they shouldn't be able to. For example, if these fee things have to do with specific users and you had

Code: Select all

SELECT ID, fee FROM table WHERE user = 123
(to only get the ones for that 123 user) then your UPDATE should read like

Code: Select all

UPDATE table SET fee = (fee) WHERE id = (id) AND user = 123
wendy
Forum Newbie
Posts: 4
Joined: Mon Dec 09, 2013 2:13 am

Re: update db with foreach

Post by wendy »

Thanks a lot for your help.
Now there is no more a warning of invalid code.
But it update all the checkboxes with 1... the array is not read out correctly it seems...

The code is now like this:

Code: Select all

mysql_select_db($database_tool, $tool); //WHERE WKNR= 64
$query_master = "SELECT * FROM table";
$master = mysql_query($query_master, $tool) or die(mysql_error());
$row_master = mysql_fetch_assoc($master);
$totalRows_master = mysql_num_rows($master);

if (isset($_POST['fee'])) { //tried because of $row_master['fee'] doesn't work either...
$fee[] = $_POST['fee'];} 
if (isset($_POST['ID'])) {
  $id[]= $_POST['ID']; }

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
    foreach ($_POST["fee"] as $id => $fee) {
     $updateSQL = "UPDATE msport SET fee = ". ($fee ? 1 : 0) . " WHERE ID = " .$id;

mysql_select_db($database_tool, $tool);
 $Result1 = mysql_query($updateSQL, $tool) or die(mysql_error());
 }
}
The HTML-Code works correctly (it shows the boxes which have a 1 in table, but now all boxes have a one (even those which shouldn't...)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: update db with foreach

Post by Celauran »

Did you add the IDs as requinix suggested? What does your $_POST['fee'] array look like?
wendy
Forum Newbie
Posts: 4
Joined: Mon Dec 09, 2013 2:13 am

Re: update db with foreach

Post by wendy »

Yes I put it inside this way you said:
<td><?php echo $row_master['ID']; ?>&nbsp; </td>
<td>
<input type="checkbox" name="fee[<?php echo $row_master['ID']; ?>]" value='1' <?php if ($row_master['fee'] == 1) echo 'checked="checked"';?> />
I put in var_dump($_POST['fee']) and print_r($_POST['fee'])
The result:
array(8) { [2]=> string(1) "1" [1]=> string(1) "1" [4]=> string(1) "1" [5]=> string(1) "1" [6]=> string(1) "1" [7]=> string(1) "1" [8]=> string(1) "1" [9]=> string(1) "1" } Array ( [2] => 1 [1] => 1 [4] => 1 [5] => 1 [6] => 1 [7] => 1 [8] => 1 [9] => 1 )
1 array(8) { [2]=> string(1) "1" [1]=> string(1) "1" [4]=> string(1) "1" [5]=> string(1) "1" [6]=> string(1) "1" [7]=> string(1) "1" [8]=> string(1) "1" [9]=> string(1) "1" } Array ( [2] => 1 [1] => 1 [4] => 1 [5] => 1 [6] => 1 [7] => 1 [8] => 1 [9] => 1 )
1 array(8) { [2]=> string(1) "1" [1]=> string(1) "1" [4]=> string(1) "1" [5]=> string(1) "1" [6]=> string(1) "1" [7]=> string(1) "1" [8]=> string(1) "1" [9]=> string(1) "1" } Array ( [2] => 1 [1] => 1 [4] => 1 [5] => 1 [6] => 1 [7] => 1 [8] => 1 [9] => 1 )

Thank you so much for your help!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: update db with foreach

Post by Celauran »

The array looks right. You should be able to just implode the array keys and run an update.

Code: Select all

$keys = implode(',', array_keys($_POST['fee'']));
$query = "UPDATE msport SET fee = 1 WHERE id IN ({$keys})";
wendy
Forum Newbie
Posts: 4
Joined: Mon Dec 09, 2013 2:13 am

Re: update db with foreach

Post by wendy »

Thanks a lot for your help! It works!!!
But the file doesn't reload, so you can't see the updated file (unless you reload manually...) How can I fix this?
The other problem: If I thick off the checkbox, I want to have the 0 in the table. To do this I truncate the table befor setting the 1. But is there no better possibility? Thanks a lot for your help!
mysql_select_db($database_tool, $tool);
$query_master = "SELECT * FROM table";
$master = mysql_query($query_master, $tool) or die(mysql_error());
$row_master = mysql_fetch_assoc($master);
$totalRows_master = mysql_num_rows($master);

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {

$truncate = "UPDATE table SET fee = 0";
mysql_select_db($database_tool, $tool);
$Result1 = mysql_query($truncate, $tool) or die(mysql_error());

$keys = implode(',', array_keys($_POST['fee']));

$updateSQL = "UPDATE table SET fee = 1 WHERE ID IN ({$keys})";
mysql_select_db($database_tool, $tool);
$Result1 = mysql_query($updateSQL, $tool) or die(mysql_error());
}
?>
Thanks a lot!!
Post Reply