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

问题描述：

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?

基本上我有一个包含多个复选框（属于同一组）的表单，这些是类别选择。 p>

当用户想要更新他们的选择时，他们可以查看此表格 - 已经勾选了他们的CURRENT选项。 p>

然后用户更改他们的选择和提交表格。我现在需要在数据库中更新这些选择。这是我目前的代码： p>

  // $ old_selections包含一个ID的ARRAY  - 我用它来预先选择复选框
 $ old_selections =列表：  ：getSelections（）; 
 
if（isset（$ _ POST ['update']））
 {
 // $ _POST ['selections']是发布ID的ARRAY 
 $ new_selections = $ _POST [' 选择']; 
 
 foreach（$ new_selections as $ selection）
 {
 //使用$ selection 
插入新记录
} 
} 
  code>  pre> 
 
  因此，目前这会将新选择添加到数据库，但不会删除任何现有选择。 理想情况下，这应该有点聪明 - 而不是仅删除所有现有条目，它应该比较两个数组并根据需要删除/插入。 p> 
 
 
此外，如果用户取消所有选择，那么它 显然需要删除所有现有的条目。 p> 
 
 
实现这一目标的最有效方法是什么？ p> 
  div>

答

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.

答

In cases like this I usually have a separate table with UserID, SelectionID and 'Y' or 'N' - or even simpler drop the Y/N bit and assume if there is a record for a user/selection pair then they've ticked it, if not they haven't.

I delete all records for user x when they update and write new records in. There's no point checking if a record already exists..

If options are simple you could even just store a string in the user's record - perhaps delimit the ids with a | or similar so they are easy to split out. Again I'd just overwrite the record.

I prefer the first method if you need to access the data the other way around - eg here 'find all users who've selected option x' - that's easy with the table solution. Also if you delete one of the selections in the future you can easily just remove all records with that id from the table.

答

change the table structure a bit

have a seperate table to store Selection Details ex:

selection_id selection_name
1            New Selection

and a another table to keep which user added which selection ex: table selections

user_id   selection_id
10          1

so updating selections means adding new records to the table and inserting new ones [simple sql functions can be used]

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

相关推荐