phpmysqlcheckbox

Php update database if checkbox is not checked


so I have a dynamically generated table

<tr>
    <td><?php echo $row['RequestID']?></td>
    <td><?php echo $row['FirstName'] . " " . $row['LastName']?></td>
    <td><?php echo $row['DateRequested']?></td>
    <td><?php echo $row['DateNeeded']?></td>
    <td><?php echo $row['TotalPrice']?></td>
    <td><?php echo $row['Company']?></td>
    <td><?php echo $row['Account']?></td>
    <td><?php echo $row['Brand']?></td>
    <td><?php echo $quantity?></td>
    <td><input type="checkbox" name="bill[]" value=<?php echo '"' . $row['RequestID'] . '"'; if($row['BillBack'] == "1") {echo "checked='checked'"; } ?></td>
</tr>

and I want to update the database when a row is checked or unchecked.

if(isset($_POST['submit'])){//to run PHP script on submit
    if(!empty($_POST['bill'])){
    // Loop to store and display values of individual checked checkbox.
    foreach($_POST['bill'] as $selected){
    echo "hello";
    $sql2 = "UPDATE Requests SET BillBack = '1' WHERE RequestID = '$selected'";
    $result2 = $conn->query($sql2);

    }} elseif(empty($_POST['bill'])){
    // Loop to store and display values of individual checked checkbox.
    foreach($_POST['bill'] as $selected){
    echo "hello";
    $sql2 = "UPDATE Requests SET BillBack = '0' WHERE RequestID = '$selected'";
    $result2 = $conn->query($sql2);
    }}

         }

Now. The if statement works like a charm. For every checked checkbox on a submit, that record gets updated. The issue comes with unchecking the checkboxes. As you can see, checked boxes are found by finding what is not empty. But, when I delete that exclamation point, it does not effectively do the opposite. It also doesn't work as it's own if statement, as a straight else statement, and checking for !isset. Is this because of how the inputs are deemed checked? is there a syntax error? I've done about 5 hours of error checking, googling, and I couldn't figure it out. Thanks guys


Solution

  • Its very simple: just update ALL your entries to 0, an then set 1 on those that are checked.

    //to run PHP script on submit
    if(isset($_POST['submit'])){    
        // Setting everyone to 0
        $sql2 = "UPDATE Requests SET BillBack = '0'";
        $result2 = $conn->query($sql2);
    
        // Setting only those checked to 1
        if(!empty($_POST['bill'])){
            // Loop to store and display values of individual checked checkbox.
            foreach($_POST['bill'] as $selected){
                echo "hello";
                $sql3 = "UPDATE Requests SET BillBack = '1' WHERE RequestID = '$selected'";
                $result3 = $conn->query($sql3);
            }
        }
    }
    

    EDIT:

    Be aware that having queries inside a foreach may cause overload in your server, to avoid this, you can store the ids of entries that you want to update and use it in only one query outside the foreach:

    //to run PHP script on submit
    if(isset($_POST['submit'])){
        // Setting everyone to 0
        $sql2 = "UPDATE Requests SET BillBack = '0'"; // Maybe add a WHERE clause, like BizzyBob pointed out
        $result2 = $conn->query($sql2);
    
        // Setting only those checked to 1
        if(!empty($_POST['bill'])){
    
            // Initialize an array of selected ids
            $selected_ids = array(); 
    
            // Loop to store individual checked checkboxes on array        
            foreach($_POST['bill'] as $selected){
                $selected_ids[] = $selected;
            }
    
            // Get the ids separated by comma
            $in_clause = implode(", ", $selected_ids);
    
            // Do only one sql query
            $sql3 = "UPDATE Requests SET BillBack = '1' WHERE RequestID in (".$in_clause.")";
            $result3 = $conn->query($sql3);
        }
    }