update association table with post array from checkboxes php/mysql

[robotmustdie]

ok, I have a dynamic site so the checkboxes are added to the page dynamically from database. the name of checkboxes sets it to an array "checkbox[]". now my $_Post['checkbox'] will be an array. now I want to take that array and make a for loop that updates my table once for each variable in the array but it's not working and I'm stumped...

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  for ($i=0;$i<count($_POST['SpecificationEquipped']);$i++) {
   $updateSQL = sprintf("UPDATE TestersSpecifications SET SpecificationEquipped=%s WHERE TesterModel=%s AND SpecificationID=%s",
         GetSQLValueString(isset($_POST['SpecificationEquipped'][$i]) ? "true" : "", "defined","1","0"),
         GetSQLValueString($_POST['TesterModel'], "text"),
         GetSQLValueString($_POST['SpecificationID'][$i], "int"));
 
  mysql_select_db($database_ampacin1_ampac, $ampacin1_ampac);
  $Result1 = mysql_query($updateSQL, $ampacin1_ampac) or die(mysql_error());
  }
}

I'm not sure how to make the array work the way the code is for "defined". anyone think they can tell me the best way to do this?

[David_Powers]

When you use a checkbox group as an array, only those checkboxes that are selected are included in the array. So, your isset() test for each value in the array is meaningless. What your current code does is run the same number of times as there are selected checkboxes, and insert 1 in the SpecificationEquipped column each time.

[robotmustdie]

ok, so how would I make it update each record in the table for that selection whether the box is checked or not? For some reason, it wasn't changing all the records to 1. I don't have a clue what is actually happening but I'm printing the output on a test page to see what the $updateSQL string looks like. If there is a better way to do all this, please let me know!

[David_Powers · Accepted solution]

Printing out the SQL to see what's being generated is the best way to troubleshoot problems with dynamically generated queries.

It's not easy to understand exactly what you're trying to do. However, it might help you to sort out the problem if you understand what the following section of code actually does:

GetSQLValueString(isset($_POST['checkbox']) ? "true" : "", "defined","1","0")

When a form is submitted, the value of a checkbox is submitted through the $_POST array only if the checkbox is selected. Let's say you have a checkbox like this:

<input type="checkbox" name="agree" value="y" id="agree" />

If the checkbox is selected, the value of $_POST['agree'] is "y". If the checkbox is not selected, $_POST['agree'] is NOT set. It's not blank, null, or anything like that. It simply doesn't exist.

What Dreamweaver is doing is checking whether the checkbox variable exists (isset()). If it does, it inserts 1 into the database field. Otherwise, it inserts 0.

The problem is that you have created a checkbox group, using an array. Let's say you have 10 checkboxes in that group, if only 5 are selected, your array will contain only 5 items.

Depending on how you are setting things up, I think you need to have differently named checkboxes for each record that is to be inserted into the database. You can do this with a counter and a loop:

<?php
$i = 1;
do { ?>
  <input type="checkbox" name="cb_<?php echo $i; ?>" value="y" id="cb_<?php echo $i; ?>" />
<?php
$i++;
} while (some condition);
?>

If you don't know in advance how many items will be in your loop, you can use $i as the value for a hidden field after the loop has finished. This will enable you to loop through your update queries, using the value from the hidden field to control the loop, and using a counter to change the name of the checkboxes.

for ($i = 1; $i < $_POST['count']; $i++) {
// other stuff
GetSQLValueString(isset($_POST["cb_$i"]) ? "true" : "", "defined","1","0")
// other stuff
}

[robotmustdie]

ok, what if I add a hidden field and call it SpecificationEquipped[$i], set it to 0, and loop through the update with a for loop using

 GetSQLValueString($_POST['SpecificationEquipped'][$i], "int")

that way, I can change it to 0 if it's no longer checked? I did it this way and it's still not updating the values in the table. I have no clue why but here's the link: http://ampac-intl.com/tester_update.php?TesterModel=Nova%20112 for some reason, all the checkboxes are checked too and they shouldn't be. I checked the table and it has a 1 for the first and last value and 0 for the rest. on the table, the default value of the column SpecificationEquipped is 0.

[David_Powers]

Is it an ENUM column? ENUM is considered to be a string value, so use "text" instead of "int".