[Resolved] Update from checkbox array

[slowhandz]

Hello,

I'm having a problem updating a Section lookup table
from a checkbox array, and need some help.

I have an EDIT page that loads all the current
Sections for each Product, using checkboxes.

The code I'm currently using works (see attachment),
BUT, I'm using a cheap hack when the form is submitted:

1) I Delete all entries in the lookup table for that Product ID. (this sucks!)
2) Then, Add the new selections (checked items) to the lookup table for that Product ID.

I could live with this, EXCEPT my Section lookup table also includes a column
for ordering the items in that section, and when I delete all entries
for a Product ID, the ordering for that section becomes useless.

What I want to do is:

1) If an item remains checked (no change), DO NOTHING.
2) If an item that was previously checked is UNCHECKED, DELETE IT.
3) If an item that was previously unchecked is CHECKED, ADD IT.

Simple, huh?

The only thing looping right now is my brain,
and any suggestions are welcome!

The attached Zip file includes:
1) index.php - Change the username/password and database name at the top.
2) SQL dump to create two tables (tbl_section and tbl_sectionlookup).
3) Image icons for sections also included.

Batteries not included.

Thanks!

[tsinka]

Hi,

seems like you forgot to attach the zip file ...

Thomas

[slowhandz]

Lets see if the file attachment works...

[slowhandz]

RESOLVED !!

<?php

// Let's test Product ID = 2 
$prodID = 2; 

// The code in this section needs to be fixed 
if (isset($_POST['submit'])) {     

     // READ the current list of checked items from the DB and put it in an array, before any changes are made.
     $sql="SELECT SECTIONID FROM tbl_sectionlookup WHERE PRODUCTID = ".$prodID;
     $result=mysql_query($sql) or die("SELECT Error: ".mysql_error());
     $current=array();
     while ($row=mysql_fetch_array($result))
         $current[]=$row['SECTIONID'];

 if(isset($_POST['fsection'])) {
    // Step through the new list of checked boxes and compare it to the current list.
    $values="";
    $checked="";
    foreach ($_POST['fsection'] as $value) {
        $checked.=",".intval($value);
        if (!in_array($value,$current)) {
            $sql="SELECT MAX(ORDERID) AS Ordermax FROM tbl_sectionlookup
            WHERE SECTIONID=".$value;
            $result=mysql_query($sql) or die("SELECT Error: ".mysql_error());
             if ($result) {
                 while ($resultrows = mysql_fetch_array($result)) {
                       $m_neworder2 = $resultrows['Ordermax'] + 1;
                       }
                  }
            $values=$values.",(".$prodID.",".intval($value).",".$m_neworder2.")";
           }
        }
    $checked=substr($checked,1);  // List of checked boxes
    $values=substr($values,1);    // List of checked boxes NOT in the DB - Add List

    // DELETE only the ones not on the checked list.
    $sql="DELETE FROM tbl_sectionlookup WHERE PRODUCTID = ".$prodID." AND SECTIONID NOT IN ($checked) ";
    $result=mysql_query($sql) or die("SELECT Error: ".mysql_error());
    echo "<h3>DELETE Query:  $sql</h3>";  // Remove when done with testing.
    echo "<h1>".mysql_affected_rows()." Records Deleted!!</h1>";  // Remove when done with testing.

    // INSERT Only items not already in the db
    if ($values){
        $sql="INSERT INTO tbl_sectionlookup (PRODUCTID, SECTIONID, ORDERID) VALUES $values";
        $result=mysql_query($sql) or die("SELECT Error: ".mysql_error());
        echo "<h3>INSERT QUERY: $sql</h3>";  // Remove when done with testing.
        echo "<h1>".mysql_affected_rows()." Records Added!!</h1>";  // Remove when done with testing.
    }
}
else
{
    // No checkboxes checked, DELETE all.
    $sql="DELETE FROM tbl_sectionlookup WHERE PRODUCTID = ".$prodID;
    $result=mysql_query($sql) or die("SELECT Error: ".mysql_error());
    echo "<h3>DELETE All Query:  $sql</h3>";  // Remove when done with testing.
    echo "<h1>".mysql_affected_rows()." Records Deleted!!</h1>";  // Remove when done with testing.
}
  } 
?>