Delete multiple rows with PDO and checkboxes using prepared statements?

Question

I have this code,

$q = $dbc -> prepare ("SELECT * FROM tasks ORDER BY date_time LIMIT 0, 15");
$q -> execute();

echo '<form action="/adminpanel?tab=process" method="post">
          <input type="hidden" name="deletetask" />';

while ($todo = $q -> fetch(PDO::FETCH_ASSOC)) {

  echo '<div id="update"><div><strong>' 
           . $todo['date_time'] . '</strong><span>' . $todo['type'] 
           . '</span></div><p>' . $todo['message'] 
           . '</p><input class="checkbox" name="deletetask" value="' 
           . $todo['date_time'] . '" type="checkbox" /></div>';
}

echo '<input type="submit" value="Delete Tasks" /></form>';

Now everything works as expected apart from one thing and I haven't really found any answers on the internet. My while loop will have always more than one row, and will almost always want more than one deleting from it to.

As this script stands the form does work but it will only delete the last checkbox that was clicked. I understand why it is doing this, but I don't understand how to overcome this problem I am using PDO and prepared statements.

The code shoud be `DELETE FROM table WHERE id IN(?,?,?...?)` — xdazz, Aug 29 '11 at 08:27
Well - change it! Debug it, rewrite it, decompose it. Are you programmer or what? — Your Common Sense, Aug 29 '11 at 08:39
The code does not work if your just copy it. I mean you should use the SQL IN clause. — xdazz, Aug 29 '11 at 08:45
@xdazz I didn't copy and paste it, I didn't even try it, I knew it would not work as my form values are displayed in a while loop making the name the same, so in php the $_POST variable would get over ridden with the new value. — carlgcoder, Aug 29 '11 at 08:54

score 3 · Answer 1 · answered Aug 29 '11 at 08:56

You are assigning the same name="deletetask" for every checkbox. So, when you submit your form, you receive only last selected deletetask value. So, your mistake is here

<input class="checkbox" name="deletetask" value=

Should be

<input class="checkbox" name="deletetask[]" value=

So you need to rename deletetask to deletetask[] so your checkboxes is sent as an array and than do something like

$todelete = $_POST['deletetask']; 
//or $_GET, if you are submitting form through get. But I would recommend you using POST
$stmt = $pdo->prepare("DELETE FROM table WHERE id = ?");
foreach ($todelete as $id)
    $stmt->execute($id);

Thank you will try this now :) finally someone who understood my comment. — carlgcoder, Aug 29 '11 at 08:58

score 1 · Answer 2 · edited Jul 04 '15 at 13:59

Here is a simple way to do multiple deletes from your database:

HTML

<input type="checkbox" name="del[]" value="your id">
<input type="checkbox" name="del[]" value="your id">
<input type="checkbox" name="del[]" value="your id">
<input type="checkbox" name="del[]" value="your id">

PHP

$ids = array();
foreach ($_POST['del'] as $pval) {
  $ids[] = (int)$pval;
}
$ids = implode(',',$ids);
$query = $db->prepare("DELETE FROM `pages` WHERE `pages_id` IN ( $ids )");
$query->execute();

Delete multiple rows with PDO and checkboxes using prepared statements?

2 Answers2

Linked