PDO multiple checkboxs inserting or delete

Question

I need help to link between three tables by checkboxe:

features table ( id, title, sulg )

posts table (id, title, slug, added )

posts_features ( fea_id, post_id )

<input type="text" name="title" value="$post->title">
<input type="text" name="slug" value="$post->slug">
// importing all features 
<input type="checkbox" name="featuresid[]" value="$features->id">

If checked ( insert ) if not exist.

foreach ($_POST['featuresid'] as $choice) {
$sql = $dbh->prepare("INSERT INTO posts_features (fea_id, post_id) VALUES ($choice, $id)");
$sql->execute();
}

and if un-checked ( delete ) from posts_features

$sql = $dbh->prepare("delete form posts_features where ........

Thanks in advance.

score 1 · Accepted Answer · answered Aug 29 '17 at 13:19

A checkbox doesn't $_POST if it's not checked, so you would not have a way to see (from the $_POST, anyway) which features where not checked.

There's several ways to do this, but without more information about your application, it's hard to make the "best" suggestion, but here's one method that will leverage $_POST:

Add an additional "hidden" input, with the corresponding $features->id, to set up the "existing" entries:

Note: I'm following your conventions in your code above to demonstrate this, even though they are clearly pseudo-code, and won't work properly.

<input type="checkbox" name="featuresid[]" value="$features->id">
<input type="hidden" name="existing[]" value="$features->id">

Then, you can leverage your loop like so:

foreach ($_POST['featuresid'] as $choice) {
    $sql = $dbh->prepare("INSERT INTO posts_features (fea_id, post_id) VALUES ($choice, $id)");
    $sql->execute();
}

// loop through ALL feature ids listed on the page
foreach( $_POST['existing'] AS $features_id ) {
    // if the feature id wasn't in the checkboxes, then delete
    if ( ! in_array( $features_id, $_POST['featuresid'] ) ) {
        $sql = $dbh->prepare("DELETE FROM posts_features WHERE ........");
    }
}

BeetleJuice · Answer 2 · 2017-08-29T13:22:13.927

Un-checked checkboxes are not sent to PHP. So as you iterate through $_POST['featuresid'], you will only see the checkboxes that were checked. This means that to delete unchecked features really means to delete all features that are not in the checked group.

First, insert the selected features: important don't execute DB queries in a loop; they will really slow down your script. Instead, insert all records at once. You should also use parameterized queries; never insert user-supplied values directly into your DB queries!

After the insert, delete those features that were not selected:

DELETE FROM posts_features WHERE fea_id NOT IN (?, ?, ?, ?)

Each of the ? corresponds to a value in $_POST['featuresid']

An alternative, if you want PHP to receive an explicit selected/unselected value for each feature is to use Yes/No radio buttons or dropdown list for each feature in the HTML.

Nice answer. I considered this, but was concerned that the list of checkboxes may be only a subset of all post_features, not the complete list. (For example, if paginating). — random_user_name, Aug 29 '17 at 13:20

PDO multiple checkboxs inserting or delete

2 Answers2