Page 1 of 1
update db with foreach
Posted: Mon Dec 09, 2013 2:23 am
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']; ?> </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>
Re: update db with foreach
Posted: Mon Dec 09, 2013 4:06 am
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
Re: update db with foreach
Posted: Mon Dec 09, 2013 5:04 am
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...)
Re: update db with foreach
Posted: Mon Dec 09, 2013 6:46 am
by Celauran
Did you add the IDs as requinix suggested? What does your $_POST['fee'] array look like?
Re: update db with foreach
Posted: Mon Dec 09, 2013 7:36 am
by wendy
Yes I put it inside this way you said:
<td><?php echo $row_master['ID']; ?> </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!
Re: update db with foreach
Posted: Tue Dec 10, 2013 6:36 am
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})";
Re: update db with foreach
Posted: Tue Dec 10, 2013 9:39 am
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!!