Deleting multiple rows from mysql table in php using checkboxes

[ggw]

Hi,

I am having difficulty deleting rows in my table using check boxes. I have all the check boxes displaying for each row and a delete button below the table. However when i click on the delete button it isnt deleting the checked rows, i have displayed the code below that i am using;

 

 <?php 
	  



$result = mysql_query("SELECT * FROM contact ORDER BY msg_id ASC");

echo "<table border='1'>
<tr>
<th>Delete</th>
<th>Message ID</th>
<th>Name</th>
<th>Email</th>
<th>Message</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  ?>
   <td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $row['del_id']; ?>"></td>
   <?php
  echo "<td>" . $row['msg_id'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['email'] . "</td>";
  echo "<td>" . $row['msg'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
?>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>


<?php
// Check if delete button active, start this 
if(isset($_GET['delete']))	{

for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM contact WHERE id=".$_GET['del_id'];
$result = mysql_query($sql);
}

// if successful redirect to delete_multiple.php 
if($result){

}
}
mysql_close();
?>

 

Please help

Thank you

[micah1701]

is this code within form tags?

I noticed you're using $_GET and not $_POST, is that the specified method in your form?

 

what if you use $_POST instead of GET?

 

[Jessica]

For the checkbox name, you'll want to have an array.

 

<input type="checkbox" name="delete_these[]" value="your id goes here" />

 

Then you can do

$ids = implode(', ', $_GET['delete_these']);

$sql = "DELETE FROM table WHERE id IN($ids)";

[ggw]

I have put it in form tags it is now refreshing the page once the delete button is clicked, however it is still not deleting the items selected. I have put that code in and it is still not working 

[Drummin]

Did you implement the suggestion made by jesirose?  Maybe showing current page (form included) would shed some light on things.

[ggw]

I think they have been implemented correctly

 

<form>
          <?php 
	  



$result = mysql_query("SELECT * FROM contact ORDER BY msg_id ASC");

echo "<table border='1'>
<tr>
<th>Delete</th>
<th>Message ID</th>
<th>Name</th>
<th>Email</th>
<th>Message</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  ?>
   <td align="center" bgcolor="#FFFFFF"><input name="delete_these[]" type="checkbox" id="checkbox[]" value="<?php echo $row['del_id']; ?>"></td>
   <?php
  echo "<td>" . $row['msg_id'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['email'] . "</td>";
  echo "<td>" . $row['msg'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
?>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>


<?php
// Check if delete button active, start this 
if(isset($_GET['delete']))	{

for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$ids = implode(', ', $_GET['delete_these']);
$sql = "DELETE FROM contact WHERE msg_id IN($ids)";
$result = mysql_query($sql);
}

// if successful redirect to delete_multiple.php 
if($result){

}
}
mysql_close();
?>
</form>

[Jessica]

replace these two lines

$ids = implode(', ', $_GET['delete_these']);

$sql = "DELETE FROM contact WHERE msg_id IN($ids)";

 

with

print_r($_GET['delete_these']);

$ids = implode(', ', $_GET['delete_these']);

$sql = "DELETE FROM contact WHERE msg_id IN($ids)";

echo "<br />SQL: $sql<br />";

 

I'm curious what it says.

 

Edit: Just noticed, you need to do the deleting before you select and display, otherwise you won't see your changes until the next page view.

Also make your form valid with a method and action. http://www.htmlhelp.com/reference/html40/forms/form.html

[ggw]

I have made the changes to the code and put the delete statements before the select and display however nothing has changed  :-\

[Jessica]

That's not possible.

[ggw]

Nothing is being deleted and nothing is printing out, it is just refreshing the page

[PFMaBiSmAd]

The $count variable is not being set to anything in the posted code, so it will be a little hard for the for(){} loop to do anything.

[ggw]

Sorry it didn't say anything because my server has not been refreshing, I have added this code in so the variable is set

$count=mysql_num_rows($result)

 

It now says (when selecting 2 check boxes to delete);

Array ( [0] => [1] => )

SQL: DELETE FROM contact WHERE del_id IN(, )

Array ( [0] => [1] => )

SQL: DELETE FROM contact WHERE del_id IN(, )

Array ( [0] => [1] => )

SQL: DELETE FROM contact WHERE del_id IN(, )

Array ( [0] => [1] => )

SQL: DELETE FROM contact WHERE del_id IN(, )

 

 

I think it would of said before if my server had been refreshing

sorry

[DavidAM]

<input name="delete_these[]" type="checkbox" id="checkbox[]" value="<?php echo $row['del_id']; ?>">

 

Is there really a column named del_id in your table? I think that should be the msg_id (the primary key on the table). Because it looks like your $_GET['delete_these'] array is full of empty values.

 

[ggw]

Thank you!

I had done this previously and tested but the server must not of been updating

 

Thanks everyone for their help

[Drummin]

Assuming as DavidAM pointed out you DO have a primary column in your table called del_id then you could do this.  Note processing really should be done above <html> and output sent to browser.

<?php
// Check if delete button active, start this 
if(isset($_POST['delete']))	{
$ids = implode(', ', $_POST['delete_these']);
$sql = "DELETE FROM table WHERE id IN($ids)";
$result = mysql_query($sql);

if($result){
header("location: delete_multiple.php");
exit;
}
}
?>
<html>
<head>
<title></title>
</head>
<body>
<?php
$result = mysql_query("SELECT * FROM contact ORDER BY msg_id ASC");
echo "<form action=\"\" method=\"post\">";
echo "<table border='1'>
<tr>
<th>Delete</th>
<th>Message ID</th>
<th>Name</th>
<th>Email</th>
<th>Message</th>  
</tr>";
while($row = mysql_fetch_array($result))
  {
echo "<tr>";
echo "<td align=\"center\" bgcolor=\"#FFFFFF\"><input name=\"delete_these[]\" type=\"checkbox\" id=\"checkbox[]\" value=\"{$row['del_id']}\"></td>";
echo "<td>" . $row['msg_id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['msg'] . "</td>";
echo "</tr>";
}
echo "<tr>";
echo '<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>';
echo "</tr>":  
echo "</table>";
echo "</form>";
?> 
</body>
</html>

[Psycho]

You do not want to (and shouldn't be) running multiple queries for the DELETE operations. If you are passing the IDs to be deleted in an array you simply need to validate/sanitize those values and then run ONE delete query

 

Example

//Assign all the ids to a variable. The array_map() with Intval assures
//they are integers. The array_filter() removes invalid values
$deleteIDs = array_filter(array_map('intval', $_POST['delete_ids']));

//Implode delete IDs into string for query
$deleteIDsSQL = implode(', ', $deleteIDs);
//Create one query to delete all records at once
$query = "DELETE FROM table_name WHERE ID IN ({$deleteIDsSQL})";