Figuring out the difference between arrays & updating them appropriately

jjfletch

Trying to wrap my brain around how to do this...

The user is presented a form that will allow them to set the category(ies) for a particular product. So, I need to check for the following:

(1) If a posted category matches a current record, then nothing happens
(2) If a posted category does not match a current record, then a new record is created
(3) If a current record does not match the posted categories, then that record is deleted

So, my table looks something like this:
(NOTE: This is just sample data. The actual data has sensitive information, and there are other columns specific to the product/category, so I can't store the category in the db as A,B,C, and I can't delete them and then re-create them all, because I'd be deleting information for each record that isn't editable from this form.)

id -------> category --------> product
1 ------------> A ---------------> Milk
2 ------------> B ---------------> Milk
3 ------------> C ---------------> Milk
4 ------------> B ---------------> Salt
5 ------------> D ---------------> Bread
6 ------------> E ---------------> Bread

The user is taken to a form that displays information for Milk, and is presented with a list of all the categories, with prechecked checkboxes where appropriate:

Milk Information
---- Categories
-------- [x] A
-------- [x] B
-------- [x] C
-------- [] D
-------- [] E

The user deselects A and selects E:

Milk Information
---- Categories
-------- [] A
-------- [x] B
-------- [x] C
-------- [] D
-------- [X] E

I need to update the db appropriately, deleting the record where the product=Milk and the category=A, inserting a record where the product=Milk and the category=E, and keeping the records where the product=Milk and the category = B and C:

id -------> category --------> product
2 ------------> B ---------------> Milk
3 ------------> C ---------------> Milk
4 ------------> B ---------------> Salt
5 ------------> D ---------------> Bread
6 ------------> E ---------------> Bread
7 ------------> E ---------------> Milk

I tried using array_diff, but I can't figure out how to differentiate between the ones that should be added, and the ones that should be removed.

Help!

jjfletch

I came up with this:

nvm! I came up with this solution:


//$all_cat contains all possible categories

foreach($all_cat as $value) {
  if(in_array($value, $categories)) {
    $cat_query = "SELECT id FROM myTable WHERE product='$product' AND category='$value'";
    $cat_result = mysql_query($cat_query);
    if(mysql_num_rows($cat_result) == 0) {
      $cat_ins = "INSERT INTO myTable (product, category) VALUES ('$product', '$value')";
      $ins_res = mysql_query($cat_ins);
    }
  } else {
    $cat_query = "SELECT id FROM myTable WHERE product='$product' AND category='$value'";
    $cat_result = mysql_query($cat_query);
    if(mysql_num_rows($cat_result) > 0) {
      $cat_del = "DELETE FROM myTable WHERE product=''$product' AND category='$value'";
      $del_res = mysql_query($cat_del);
    }
  }
}