one to many update checkboxes - update syntax

[john_6767]

hey guys, thanks for your help with my inserting of one to many, i am now creating the update script and have run into a problem.

i have checkboxes that appearing correctly and are either checked or unchecked, i am having troubles getting the changes to these checkboxes updated into the database, ???

here is my checkbox code..
[code=php:0]
<input name="size_id[]" type="checkbox" id="size_id[]" value="">
[/code]

here is my update code..
[code=php:0]
if (isset($_POST['size_id'])){
foreach ($_POST['size_id'] as $size_id) {
echo "checkbox value checked: " .$size_id ."<BR>";

  //Delete from sizesball (the join table) only those that have been deselected

  if ($size_id <> "") {
  $updateSQL2 = sprintf("DELETE * FROM balls_sizes WHERE size_id not in(%s) and ball_id=%s",
                      $size_id,
                      GetSQLValueString($_POST['ball_id'], "int"));

  mysql_select_db($database_cnnTest, $cnnTest);
  $Result2 = mysql_query($updateSQL2, $cnnTest) or die(mysql_error());
  }

  //Now insert any newly selected sizes into sizesball

}
foreach ($_POST['size_id'] as $size_id) {

//First check to see if that size is already in the table sizesball

mysql_select_db($database_cnnTest, $cnnTest);
$query_rssizesballs = sprintf("SELECT size_id, ball_id FROM balls_sizes where ball_id =%s and size_id = %s",
                        GetSQLValueString($_POST['ball_id'], "int"), $size_id);

$rssizesballs = mysql_query($query_rssizesballs, $cnnTest) or die(mysql_error());
$row_rssizesballs = mysql_fetch_assoc($rssizesballs);
$totalRows_rssizesballs = mysql_num_rows($rssizesballs);

//If the recordset is empty, we can insert a new record

if($totalRows_rssizesballs == 0) {
  $updateSQL3 = sprintf("INSERT INTO balls_sizes (ball_id,size_id) VALUES (%s,%s)", 
GetSQLValueString($_POST['ball_id'], "int"), $size_id);
  $Result3 = mysql_query($updateSQL3, $cnnTest) or die(mysql_error());
  }
  }
}

[/code]

[john_6767]

bump

[btherl]

Can you explain what you did, what you expected to happen and what actually happened?  Three things in total.  Then it will be much easier for us to help you :)

[john_6767]

ok, i am trying to do an update on an item, in my case a ball, this ball can come in one to  many sizes.

I have all the fields updating but when it comes to the sizes for the ball, this information is stored into a joining table that contains the balls id i am updating as well as the size id, this allows the one to many relationship to work.

Now i have the page showing the size checkboxes, they display properly as either checked or unchecked, what i am trying to do is get this information shown by the checkboxes into the database.

My method to do this (which could be totally off the pace) was to

- Delete from sizesball (the join table) only those that have been deselected

- Now insert any newly selected sizes into sizesball - first checking if the size/ball is already in the table in which case we don't need to insert it..

hope this helps!

[HuggieBear]

You need values in your checkbox really as you won't be able to distinguish between size...

[code]<input type="checkbox" name="size_id[]" id="size_id[]" value="Large">
<input type="checkbox" name="size_id[]" id="size_id[]" value="Medium">
<input type="checkbox" name="size_id[]" id="size_id[]" value="Small">
[/code]
If they're checked they return the value in value, if they're not, then they don't.

Rgards
Huggie

[john_6767]

thanks, yep i had values, i have got it working now, it a bit dodgy but it works..