Update checkbox value in SQL table

Question

I can display checkbox value from a SQL query, but I can't update them, I just can update untick value (0 value), if I untick the checkbox, it can save the untick value 0 to the SQL table. if I retick the checkbox, it cannot update the value in the SQL query. I'm using int as the data type. Here's the sample code from my test system:

Checkbox HTML:

<div class="form-group col-lg-6">
<label class="control-label col-lg-4">Pricing<span style="color:red;">&nbsp;</span></label>
<div class="col-lg-8">
    <input type="checkbox" name="rm_option" id="rm_option" value="1"><strong> RM </strong></input>&nbsp;&nbsp;&nbsp;
    <input type="checkbox" name="point_option" id="point_option" value="1"><strong> Full Point </strong></input>&nbsp;&nbsp;&nbsp;
    <input type="checkbox" name="partial_option" id="partial_option" value="1"><strong> Partial Point + RM </strong></input>
</div>
</div>

Checkbox echo edit function:

<?php
$sql = "select * from promotion_list where id=" . $_GET['id'];
$arr_sql = db_conn_select($sql);
foreach ($arr_sql as $rs_sql) {
    foreach ($rs_sql as $key => $value) {
        ?>
                                $("#<?php echo $key ?>").val("<?php echo $value ?>");
                                    <?php if($value == 1){ ?>
$("#<?php echo $key ?>").attr("checked", true).prop("checked", true);
<?php } ?>
        <?php
    }
    ?>
                            $("#filter_id").val('<?php echo $rs_sql['id'] ?>');
                            $("#promotion_content").jqteVal('<?php echo $rs_sql['promotion_content'] ?>');
                            $("#promotion_terms").jqteVal('<?php echo $rs_sql['promotion_terms'] ?>');
                            $("#promotion_instruction").jqteVal('<?php echo $rs_sql['promotion_instruction'] ?>');
                            $("#promotion_policy").jqteVal('<?php echo $rs_sql['promotion_policy'] ?>');
    <?php
}
?>

Update function:

else if($action == 'update') {

$rm_option = isset($_POST['rm_option']) ? $_POST['rm_option'] : "";
$point_option = isset($_POST['point_option']) ? $_POST['point_option'] : "";
$partial_option = isset($_POST['partial_option']) ? $_POST['partial_option'] : "";

$query = "UPDATE " . $table ." SET id_promotion_categories = '" . $id_promotion_categories . "', 
promotion_title = '".$promotion_title."',
 rm = '".$rm."', promotion_description = '".$promotion_description."', 
point = '".$point."', point_rm_point = '".$point_rm_point."', point_rm_rm = '".$point_rm_rm."', 
quantity_limit_option = '".$quantity_limit_option."', quantity_limit = '".$quantity_limit."', 
discount_percentage = '".$discount_percentage."', promotion_price_before = '".$promotion_price_before."',
 promotion_price_after = '".$promotion_price_after."', 
redemption_from_date = '".$redemption_from_date."', redemption_to_date = '".$redemption_to_date."', 
rm_option = '".$rm_option."', point_option = '".$point_option."', partial_option = '".$partial_option."', 
merchant_option = '".$merchant_option."', merchant_price = '".$merchant_price."', 
reservation = '".$reservation."', feature = '".$feature."' where id='" . $id . "'";

$arr_treatment = db_conn_update($query);

        if ($arr_treatment) {

                $result_arr['msg'] = 'Update Successful';
            } else {
                $result_arr['msg'] = 'Error in processing data. Please try again later.';
            }
            $result_arr = special_char_display_arr($result_arr);
            $json = json_encode($result_arr);
            print($json);


} 

Below is my output(It can not let me update in the empty checkbox, for example, Partial Point + RM in below):

All update record is no problem, only for the checkbox cannot update. I hope anyone can guide me solve this problem. Thanks a lot.

Answer 1

For checkboxes, the $_POST array element is present (and = 1) if the checkbox is checked, otherwise it is absent. So change

$rm_option = isset($_POST['rm_option']) ? $_POST['rm_option'] : ""; 
$point_option = isset($_POST['point_option']) ? $_POST['point_option'] : "";
$partial_option = isset($_POST['partial_option']) ? $_POST['partial_option'] : "";

to

$rm_option = isset($_POST['rm_option']) ? 1 : 0; 
$point_option = isset($_POST['point_option']) ? 1 : 0;
$partial_option = isset($_POST['partial_option']) ? 1 : 0;

and you should be able to update 0 values as well. Note that as @SergheiLeonenco said, since these are numeric values, you don't need quotes around them in your query.

Mandatory SQL injection commentary.

Because you are using $_POST values directly in your query, you are vulnerable to SQL injection. You should read How can I prevent SQL injection in PHP and move to prepared statements to protect yourself.