YA: Checked/Unchecked Database 20130616122614AAYSDP0

Answer for Yahoo Answers Question
http://answers.yahoo.com/question/index?qid=20130616122614AAYSDP0

Form Snippet

<?php

// You really should at the least be using
// MySQLi functions. Better still; PDO.

// I am going to include clauses in the future
// SQL, due to the category_id, to make it easier
// to edit, I am also going to assign it a
// variable. Ensure that it stays the same on
// the processing page.

$category_id = '1';

$r1 = mysql_query("SELECT id,product_name,highlighted FROM products WHERE category_id='{$category_id}'");

if( !$r1 ){
    echo 'Internal Server Error. Query Failed.';
    echo '<br' . ' />'; // Yahoo Doesn't like br elements
    echo mysql_error();
}else if( mysql_num_rows( $r1 ) ){
    echo 'No Products in the Database matching Conditions ("category_id=' . $category_id . '")';
}else{
    echo '<input type="hidden" name="category_id" value="' . $category_id . '" />'; // One way to pass it, if need be
    while($q1 = mysql_fetch_assoc($r1)){
        $checked = ((int)$q1['highlighted'] === 1)? ' checked="checked"' : '';
        echo $q1['product_name'] . ' - <input type="checkbox" name="ids[]" value="' . $q1['id'] . '"' . $checked . '><b' . 'r>';
        // Again, Yahoo Doesn't like br tags
    }
}


?>


Processing Script


<?php

if( isset( $_POST['submit'] ) ){
    // Ensure that $category_id is the same as the previous form
    $category_id = mysql_real_escape_string( trim( $_POST['category_id'] ) );

    if( isset($_POST['ids']) && isset($_POST['ids'][0])){
        $ids = $_POST['ids'];
        $ids = mysql_real_escape_string( implode( ',', $ids ) );
        // In MySQLi, you could combine the two statements and make it slightly more efficient
        $stmt1 = "UPDATE products SET highlighted='1' WHERE category_id='{$category_id}' AND id IN({$ids});";
        $stmt2 = "UPDATE products SET highlighted='0' WHERE category_id='{$category_id}' AND id NOT IN({$ids});";

        if( !mysql_query( $stmt1 )){
            echo 'Statement 1 Failed because: ' . mysql_error();
        }else if( !mysql_query( $stmt2 ) ){
            echo 'Statement 2 Failed because: ' . mysql_error();
        }else{
            echo 'Success! <a href="index.php?function=highlighted">Go Back</a>';
        }
    }else{
        // No Ids Checked (~ all ids unchecked)
        $stmt = "UPDATE products SET highlighted='0' WHERE category_id='{$category_id}'";
        if( !mysql_query( $stmt ) ){
            echo 'Statement Failed because: ' . mysql_error();
        }else{
            echo 'Success! <a href="index.php?function=highlighted">Go Back</a>';
        }
    }
}
?>