Updating table using checkboxes

Trying to insert or delete based on boxes checked

[ichabod]

Hi,
I'm hoping that someone can point me in the right direction. I have an update form with checkboxes and I'm trying to insert a row if the value isn't already in the table or delete the row if the box is unchecked. This is my first attempt at programming so I wouldn't be surprised if I'm going about it all wrong. Any help/advice would be greatly appreciated. I'd be embarrassed to say how long I've spent trying to figure it out. This is the code for the checkboxes part of the form:

echo "<pre>";
print_r($_POST);
echo "</pre>";

//if any LCNS_REQ boxes are checked do the following
if ($LCNS_REQ_NM!= "")
{
while (list($key, $value) = each($LCNS_REQ_NM))
{
//find LCNS_REQ_CDs for boxes checked on form
$query = "SELECT license_reqs.LCNS_REQ_CD FROM license_reqs WHERE license_reqs.LCNS_REQ_NM = '$value'";
$result1 = mysql_query($query)
or die ("Couldn't execute get license_reqs_cd query.");
$row = mysql_num_rows($result1);
while ($row = mysql_fetch_array($result1))
{
extract($row);
$ischecked = trim("$row[LCNS_REQ_CD]");
echo "ischecked = $ischecked<BR>";
}

//find LCNS_REQ_CDs already in table
$query = "SELECT license_lcnsreqs.LCNS_REQ_CD FROM license_lcnsreqs WHERE license_lcnsreqs.LCNS_CD = '$LCNS_CD'";
$result2 = mysql_query($query)
or die ("Couldn't execute get license_lcnsreqs_cd query.");
$row = mysql_num_rows($result2);
while ($row = mysql_fetch_array($result2))
{
extract($row);
$issaved = trim("$row[LCNS_REQ_CD]");
echo "issaved = $issaved<BR>";
}

//delete LCNS_REQ_CD if unchecked and!= 3 (none selected but needed to generate checkbox html)
$intable = ($issaved == $ischecked);
echo "intable = $intable<BR>";
if ($intable!= 1)
{
$query = "DELETE FROM license_lcnsreqs WHERE (LCNS_CD='$LCNS_CD' AND LCNS_REQ_CD='$issaved' AND '$issaved'!= '3')";
$result = mysql_query($query)
or die ("Couldn't execute delete license_lcnsreqs query.");
}

//insert LCNS_REQ_CD if checked and not already in table
$notintable = ($ischecked!= $issaved);
echo "notintable = $notintable<BR>";
if ($notintable == 1)
{
$query = "INSERT INTO license_lcnsreqs (LCNS_CD,LCNS_REQ_CD) VALUES ('$LCNS_CD','$ischecked')";
$result = mysql_query($query)
or die ("Couldn't execute insert new license_lcnsreqs query.");
}
}
}

Thanks for your help!

[coopster]

Welcome to WebmasterWorld, ichabod.

Don't be embarrassed, this is a somewhat more advanced task. The trick is to recognize which values already exist in the database table and which values the user has now selected as those to keep. Arrays work well in these instances. In pseudocode:

1. Read the selected options from the database table into an array
   
2. Get the updated selections from the user into an array
   
3. DELETE from the database table any selections that are found in the database-table array but are not found in the user-supplied selections
   
4. INSERT new selections found in the user-supplied selections that are not found in the database-table array

The trick comes in comparing the two arrays.

[ichabod]

Thanks for the advice. I've learned alot but still have a long way to go. Picked up a bunch of good ideas for other bits of my code from the threads here too. It's been very helpful.

[dreamcatcher]

ichabod, please don`t be afraid to post about any issue you have. We are all here to help each other out.

Good luck,

dc