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?
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]