PHP - Figuring out the difference between arrays & updating them appropriately

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:

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!
jen_jen_jenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

under_dogCommented:
Just remove them all ... and then just loop through the submitted variables and add them one by one. Same result, less hassle.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jen_jen_jenAuthor Commented:
Can't do that. 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.
0
under_dogCommented:
Ok ... something like this maybe?

<?php

  $product = 'Milk';
 
  $categoryA = $_POST['categoryA'];
  $categoryB = $_POST['categoryB'];
  $categoryC = $_POST['categoryC'];
  $categoryD = $_POST['categoryD'];
  $categoryE = $_POST['categoryE'];

  if ($categoryA == "") {

    mysql_query("DELETE FROM categories WHERE product='$product' AND category='A'");

  } else {

    $result = mysql_query("SELECT * FROM categories WHERE product='$product' AND category='A'");
    if (mysql_num_rows($result) == 0) {
      mysql_query("INSERT INTO categories SET product='$product', category='A'");
    }

  }

  // and so on for each category.

?>
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

TeRReFCommented:
Maybe array_merge would be an idea:

// Build an array with all possible categories:
$cats = array('A'=>'', 'B'=>'', etc...);
// Array of chosen cats in form (only checked checkboxes will appear)
$checkboxes = array('C'=>'yes', 'D'=>'yes');
// Array merge for db
$db_array = array_merge($cats, $checkboxes);

// Now alter db

foreach($db_array as $cat=>$val) {
  if ($val === 'yes') {
    // add db entry
  } else {
    // delete db entry (if entry doesn't exist, nothing happens)
  }
}
0
jen_jen_jenAuthor Commented:
Well, I thought about both of these suggestions...

underdog
---------------------------------------------
(1) I can't really do underdog's because I'm passing the values for this field as an array (i.e., <input type="checkbox" name="categories[]" value="'.$row['category'].'"> )  I think to do what you're suggesting, I would need a separate input field for each category, where each one is named differently.

TeRReF
---------------------------------------------
(2) I think I'm not completely understanding this example.
Here's what I tried:

$posted_categories = $_POST['categories'];

$query = "SELECT DISTINCT(category) from myTable";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
   $cat .= $row['category'];
}
$all_cat = explode(',',substr($cat,0,-1));  // All the categories
$db_array = array_merge($all_cat, $posted_categories);

print_r($all_cat);
// Prints Out:
// Array ( [0] => A [1] => B [2] => C [3] => D [4] => E )

print_r($posted_categories);
// Prints Out:
// Array ( [0] => B [1] => C [2] => E)

print_r($db_array);
// Prints Out
// Array ( [0] => A [1] => B [2] => C [3] => D [4] => E [5] => B [6] => C [7] => E)

So, I'm not sure where I can set a "yes" value?  
0
jen_jen_jenAuthor Commented:
I also tried:

    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($query);
        }
      }
    }

But, I don't think this helps me when trying to figure out which record should be removed.  :/
0
jen_jen_jenAuthor Commented:
Well, I tried this:

    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 (product, category) VALUES ('$product', '$value')";
          $del_res = mysql_query($cat_del);
        }
      }
    }

But, that didn't delete anything when I unchecked the boxes :/
0
jen_jen_jenAuthor Commented:
Ahh, nevermind:  

    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);
        }
      }
    }
0
jen_jen_jenAuthor Commented:
Wait, reality check:  This does what I want, so far, but I'm shocked I figured it out.  Does this code look right?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.