使用带有mysql和php的复选框更新多行

Question

I have already created a settings form. The link of the settings form is here -

I have already insereted value. Please check my database.

I want to retrieve the option_value from the database and populate in the checkbox as value.

When there is "Y" then the checkbox will be checked. Then I want to change it as "N" and it will update to the option_value field and vice versa.

The update query would be affect single element or multiple element.

Would anybody please help me how can I acheive this?

Answer 1

Here is a basic mockup (may not be fully working) but you'll get the idea of the code.

-> Grabs all db options and makes checkboxes based off that.
-> Checks the database and tries to find changed values of checkboxes and updates accordingly.

More information is commented in the code.

File showing the checkboxes:

<?php
$result = mysqli_query($connection, "SELECT option_name, option_value FROM mytable");
//Gets all the options from the database, so the values have to be in there in the first place.
while($row = mysqli_fetch_assoc($result))
//Loops through each value in the database
{?>
    <?php if($row['option_value'] == 'Y') 
    {
    ?>
        <input type='checkbox' name="<?php echo $row['option_name']; ?>" checked>
    <?php 
        //Adds a checked checkbox if the value is "Y" 
    }
    else {
    ?>
        <input type='checkbox' name="<?php echo $row['option_name']; ?>">
    <?php
        //Adds a unticked checkbox if the value is not "Y" e.g. "N"
    }
}
?>

File in which the checkboxes are submitted to (the page the form posts to)

<?php

$result = mysqli_query($connection, "SELECT option_name, option_value FROM mytable");
//Gets all of the option names and values
while($row = mysqli_fetch_assoc($result))
{
    if(isset($_POST[$row['option_name']]) && $row['option_value'] == "N")
    {
        //If the option is now yes (isset checks returns true if the box is selected) and the option in the db is N then update.
        mysqli_query($connection, "UPDATE mytable SET option_value = 'Y' WHERE option_name ='" . $row['option_name'] . "'");
    }
    if(!isset($_POST[$row['option_name']]) && $row['option_value'] == "Y")
    {
        //If the option is now no (isset checks returns false if the box is not selected) and the option in the db is Y then update.
        mysqli_query($connection, "UPDATE mytable SET option_value = 'N' WHERE option_name ='" . $row['option_name'] . "'");
    }
}

?>