How to update a bunch of un-checked rows?

[bernk]

Hi guys. I could really use some help with this problem.

I'm builing a little CMS to manage a basic product catalog. Each product's information is stored in a products table in a MySQL DB. The parts I'm concerned about here are the product's unique ID (primary key) and the product's availability (boolean). When added a product is available by default (1). In the CMS the products are displayed in a simple table with each row having a checkbox which reflect the current state of that product's availability:

1 x
2 x
4 x
6 x
9 x

// this is how the actual inputs look like
<input type='checkbox' name='available[]' value='1' checked />
<input type='checkbox' name='available[]' value='2' checked />
<input type='checkbox' name='available[]' value='4' checked />
<input type='checkbox' name='available[]' value='6' checked />
<input type='checkbox' name='available[]' value='9' checked />

Now let's say I uncheck one of the rows in the table:

1 x
2 x
4
6 x
9 x

How can I get PHP to update the MySQL table with the unchecked row(s)? I have been successful in accomplishing the opposite, but am having trouble with writing a loop for the unchecked IDs. Please note that the IDs are not sequential since products can be deleted, which leaves holes.

My code for updating freshly checked boxes looks like this:

if ($_GET['action']=="update")
{
require("includes/connect.ini");
foreach ($_POST['available'] as $id)
{
$query = sprintf("UPDATE products SET available='%u' WHERE id='%u'", 1, mysql_real_escape_string($id));
$result = mysql_query($query, $con);
if (!$result)
{
echo 'could not update availability of products: ' . mysql_error($con);
exit;
}
}
mysql_close($con);
}

This would be a lot easier if the checkbox array would contain unchecked boxes with a value of 0, but it does not. It only contains the checked boxes.

[lokeshshettyk]

I hope you populate the table data on the page from the DB? if so,
1) while selecting the rows from the DB table store the values in an array(array_initial).

2) While submitting the form back to the DB, store the selected values in an array (array_checked).

3) Do a compare between these arrays, i.e. deduct array2 from array1 (array_unchecked = array_initial - array_checked)

4) Now you have the unchecked values in array_unchecked.

Hope this helps

Cheers :)

[bernk]

Thanks for your reply.

I am definitely populating the table dynamically from the DB. I totally get he logic of what you wrote, unfortunately I'm not so sure how to go about making it happen in PHP.

More specifically how do I compare the arrays and deduct one from the other so that I'm left with an array of unchecked IDs?

Thanks again loke!

[coopster]

Take a look at the PHP Array Functions [php.net] ... array_diff [php.net] perhaps.

[bernk]

Thanks for the help guys.