Solved

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

Posted on 2007-04-03
9
170 Views
Last Modified: 2007-04-03
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!
0
Comment
Question by:jen_jen_jen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 9

Accepted Solution

by:
under_dog earned 250 total points
ID: 18843206
Just remove them all ... and then just loop through the submitted variables and add them one by one. Same result, less hassle.
0
 

Author Comment

by:jen_jen_jen
ID: 18843222
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
 
LVL 9

Expert Comment

by:under_dog
ID: 18843310
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 29

Assisted Solution

by:TeRReF
TeRReF earned 250 total points
ID: 18843339
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
 

Author Comment

by:jen_jen_jen
ID: 18843488
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
 

Author Comment

by:jen_jen_jen
ID: 18843676
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
 

Author Comment

by:jen_jen_jen
ID: 18843719
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
 

Author Comment

by:jen_jen_jen
ID: 18843746
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
 

Author Comment

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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question