asked on

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!

ASKER CERTIFIED SOLUTION

under_dog

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jen_jen_jen

ASKER

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.

under_dog

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.

?>

SOLUTION

TeRReF

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jen_jen_jen

ASKER

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?

jen_jen_jen

ASKER

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. :/

jen_jen_jen

ASKER

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 :/

jen_jen_jen

ASKER

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);
}
}
}

jen_jen_jen

ASKER

Wait, reality check: This does what I want, so far, but I'm shocked I figured it out. Does this code look right?

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

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