PHP / MySQL - 更新数据库中的复选框选择

Question

Basically I have a form that contains several checkboxes (belonging to the same group), these are category selections.

When a user wants to UPDATE their selections, they can view this form - which already has their CURRENT selections ticked.

The user then changes their selections and submits the form. I now need to update these selections in the database. Here is the code I have at the moment:

// $old_selections contains an ARRAY of IDs - I use this to pre-select the checkboxes
$old_selections = Listing::getSelections();

if(isset($_POST['update']))
{
        // $_POST['selections'] is an ARRAY of posted IDs
        $new_selections = $_POST['selections'];

        foreach($new_selections as $selection)
        {
                // insert a new record using $selection
        }
}

So currently this ADDS the new selections to the database, but does not delete any existing ones. Ideally this should be a bit clever - rather than just deleting all existing entries, it should compare the two arrays and delete / insert as necessary.

Also if a user unticks all selections, then it would obviously need to delete all the existing entries.

What would be the most efficient way of achieving this?

Answer 1

Complementing @ManZzup answer.

When you submit the form, the update can be done like this:

// You no longer need this
// $old_selections = Listing::getSelections();

if(isset($_POST['update']))
{
    // $_POST['selections'] is an ARRAY of posted IDs
    $new_selections = $_POST['selections'];

    $list;
    foreach($new_selections as $selection)
    {
        $list .= $selection + ",";
    }
    $list = substr($list, 0, strlen($list));

    $query = "DELETE FROM tablename WHERE selection_id NOT IN (" . $list .") AND user_id = " . $id;
    mysqli_query($con, $query);

    foreach($new_selections as $selection)
    {
        $query = "INSERT INTO tablename VALUES (" . $id . "," . $selection . ")";
        mysqli_query($con, $query);
    }
...
}

Try something like this.