Delete multiple mysql rows using check boxes in php

Question

I've tried asking this question before and I was rudely told I've posted too much code. So this time around I will do my best to only post relevant code.

So, I am trying to delete multiple rows using check boxes. I've looked up sooo many tutorials on it, and most use a for each loop. I haven't really figured out the difference between a while and for each loop. So, this is my front end php code:

$sql = "SELECT * FROM appointments WHERE date = '".$date."' ORDER BY appttime";

$result = mysqli_query($transport, $sql);

echo "<h2 align='center'>Schedule for $raw_date</h2>";

echo "<table border='0' style='width: 100%; margin: auto; border-width: 1px'><tr><th>Resident Name</th><th>APT #</th><th>Appt. Time</th><th>Location Phone</th><th>Location Name</th><th>Address</th><th>City</th><th>Zip</th><th>Bus or Car</th><th>Escort Name</th><th>Transfer</th><th>Comments</th><th>Dparting Times</th><th>Delete</th></tr>";

?>
<form name="update_times" method="post" action="depart_t.php">
<?php
$i=0;

while($row = mysqli_fetch_array($result))
  {
  echo "<input type='hidden' name='id[$i]' value='" . $row['id'] . "'>";
  echo "<tr>";
  echo "<td align='center'><input name='resident[$i]' style='width: 80px' type='text' value='" . htmlspecialchars($row['r_name'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='room_n[$i]' style='width: 40px' type='text' value='" . htmlspecialchars($row['room'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='appt_time[$i]' style='width: 55px' type='text' value='" . date("g:i A", strtotime($row['appttime'])) . "' /></td>";  
  echo "<td align='center'><input name='appt_phone[$i]' style='width: 65px' type='text' value='" . htmlspecialchars($row['apptphone'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='name_l[$i]' style='width: 80px' type='text' value='" . htmlspecialchars($row['l_name'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='address[$i]' style='width: 90px' type='text' value='" . htmlspecialchars($row['address'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='city[$i]' style='width: 70px' type='text' value='" . htmlspecialchars($row['city'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='zip[$i]' style='width: 50px' type='text' value='" . $row['zip'] . "' /></td>";
  echo "<td align='center'><input name='buscar[$i]' style='width: 30px' type='text' value='" . htmlspecialchars($row['buscar'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='e_name[$i]' style='width: 60px' type='text' value='" . htmlspecialchars($row['escort_name'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='transfer[$i]' style='width: 40px' type='text' value='" . htmlspecialchars($row['transfer'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='comments[$i]' style='width: 80px' type='text' value='" . htmlspecialchars($row['comments'], ENT_QUOTES) . "' /></td>";
  echo "<td align='center'><input name='out[$i]' style='width: 70px' type='text' value='" . date("g:i A", strtotime($row['depart'])) . "' /></td>";
  echo "<td align='center'><input name='delete[$i]' type='checkbox' value='" . $row['id'] . "' /></td>";
  echo "</tr>";
  ++$i;  
  }

echo "</table>";


?>
<br>
<input type="submit" value="Update" style="float:left; margin:5px" onclick="return confirm('Are you sure you want to proceed?')" /></form>
<form name="print" action="printer_f.php" method="post"><input name="p_friendly" type="submit" value="Printer Friendly View" style="float:left; margin:5px" /></form><form name="delete" action="delete.php" method="post"><input name="delete" type="submit" value="Delete Selected" onclick="return confirm('Are you sure you want to delete these entrys?')" style="float:left; margin:5px" /></form>

This is my delete code:

$size = count($_POST['delete']);
$i=0;
while ($i < $size)
    {
        $id = $_POST['id'][$i];
        $sql = "DELETE FROM appointments WHERE id = $id";
        echo $sql;
        mysqli_query($transport, $sql) or die('Error: ' .mysqli_error($transport));

        ++$i;

}

With this code, all that ever happens is, the first row gets deleted no matter which one I select. I've tried using an if statement before it to check if a checkbox is actually checked, but that didn't seem to work either. So I am at a loss. Thank you for the help!

$id = $_POST['id'][$i] is setting $id to the value of $_POST['id'][0]. You should be iterating through $_POST['delete'] instead. Try using var_dump() to echo out the values you're getting from the form - it'll make it clearer what your form is passing. — andrewsi
– andrewsi, Commented Jul 9, 2014 at 17:46
Also - consider using a prepared statement for your DELETE. What would happen if a miscreant passed an id of 1 OR 1=1? — andrewsi
– andrewsi, Commented Jul 9, 2014 at 17:48

Barmar · Accepted Answer · 2014-07-09 20:39:26Z

4

Use this:

$stmt = mysqli_prepare($transport, "DELETE FROM appointments WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $id);
foreach ($_POST['delete'] as $id) {
    mysqli_stmt_execute($stmt);
}

The foreach statement is better because the only delete[i] elements that are posted are the ones that are checked, so there will be gaps in the sequence. This code also shows how to use prepared statements instead of variable substitution.

The other problem you have is that the delete checkboxes conflict with this submit button:

<input name="delete" type="submit" value="Delete Selected" onclick="return confirm('Are you sure you want to delete these entrys?')" style="float:left; margin:5px" />

When you use that submit button, it sets $_POST['delete'] to Delete Selected, which overrides all the checkboxes. Give one of them a different name, and change the code that reads from that $_POST field.

edited Jul 9, 2014 at 20:39

answered Jul 9, 2014 at 17:49

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Barmar Over a year ago

Do you see the expected values if you do var_dump($_POST['delete'])?

salimagic Over a year ago

Nope, I just get this: string(15) "Delete Selected"

salimagic Over a year ago

The strange part about all of this is that I have the same exact statement for UPDATE, to update the fields, and that works 100% just fine. So I don't know if I just need to add something else because they're checkboxes and not textboxes.

Barmar Over a year ago

It sounds like you have another input with name="delete" value="Delete Selected", and it's conflicting with the delete checkboxes. Maybe it's a submit button or hidden input?

salimagic Over a year ago

I had a <form name="delete" ... but just changed it to delete_this and it's still giving me the same error.

|

Collectives™ on Stack Overflow

Delete multiple mysql rows using check boxes in php

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related