Deleting multiple rows using checkboxes, PHP and MySQL

Question

As the title suggests I want to delete multiple rows from my database. To accomplish this I have two files, a front end file that generates a table that shows the files a user may delete which are chosen using checkboxes.

The back end file is to process the selected checkboxes and use an SQL statement to delete the chosen files.

The problem I am having is passing the id of a selected file from the front end to the back. The code for both files are below:

Front End

//Build Table Query
$query="SELECT * FROM documents";
$result= mysqli_query($con, $query) or die("Invalid query");

$count = mysqli_affected_rows($con); 

?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="deletefilesback.php">
<table width="800" border="0" cellpadding="3" cellspacing="2" bgcolor="#CCCCCC">
<tr>
<td colspan="5" bgcolor="#FFFFFF" align="center"><strong>Delete Multiple         Files</strong></td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Title</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Description</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>File Location</strong></td>
</tr>
<?php
while($row = mysqli_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox"  id="checkbox[]" value="<?php echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><?php echo $row['id']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $row['title']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $row['description']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $row['doc_link']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit"   id="delete" value="Delete Files"></td>
</tr>
</table>
</form>
</td>
</tr>
</table>

Back End

$delete = $_POST['checkbox'];

//Then do what you want with the selected items://
foreach ($delete as $id) {

$query="DELETE FROM documents WHERE id = '".$id."'";
$result= mysqli_query($con, $query) or die("Invalid query");

}
//Show that the items have been successfully removed.//
if (mysqli_affected_rows($con) > 0) {
echo '<p>The selected items have been successfully deleted.</p>';
} else {
echo '<p>An error has occurred while processing your request</p>';
}
?>

As a note, once this is working I will be using the unlink function to delete the file on the server using the doc_link part of the table on the front end. Thanks

When I use $result = mysql_query($query) or die(mysql_error()); I just get a blank page. — Maverick Johnson
– Maverick Johnson, Commented Apr 13, 2012 at 18:16

Sandeep Rajoria · Accepted Answer · 2012-04-13 17:54:50Z

4

in html page do it like this

<input name="checkbox[<?php echo $row['id']?>]"

and in the back end do like this

foreach ($delete as $id => $val) {
    if($val=='checked'){
        $query="DELETE FROM documents WHERE id = '".$id."'";
        $result= mysqli_query($con, $query) or die("Invalid query");
    }
}

answered Apr 13, 2012 at 17:54

Sandeep Rajoria

1,2498 silver badges14 bronze badges

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

2 Comments

Maverick Johnson Over a year ago

If I echo $id it does show the correct ID number, but the row still wont delete, but if I hardcode the id number into the sql it works. Any ideas what that problem is?

Maverick Johnson Over a year ago

Got it,all I had to do was remove the IF statement, and now it works. Thanks

Leo McKenzie III · Accepted Answer · 2012-06-27 17:00:41Z

** MYSQL Code ** Description: This code initially creates the database to use. In MySQL terminal:

`CREATE database `tester`;`

Now, create a table for that database:

`USE tester;`

`CREATE TABLE `test_mysql` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(80) NOT NULL,
`lastname` VARCHAR(80) NOT NULL,
`e_mail` VARCHAR(100) NOT NULL)
ENGINE = MYISAM;`

Now, insert some records into your newly-created table:

`INSERT INTO `test_mysql`
(`id`, `name`, `lastname`, `e_mail`)
VALUES
(NULL, 'Billly', 'Blueton', '[email protected]'),
(NULL, 'Jame', 'Campbell', '[email protected]'),
(NULL, 'Mark', 'Jackson', '[email protected]'),
(NULL, 'Linda', 'Travor', '[email protected]'),
(NULL, 'Joey', 'Ford', '[email protected]'),
(NULL, 'Sidney', 'Gibson', '[email protected]');`

Next, create a PHP (HTML) file called index.php:

** PHP & HTML Code **

`<h1>Deleting Multiple Records using PHP &amp; MySQL</h1>
<p>&nbsp;</p>
<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="tester"; // Changed database name
$tbl_name="test_mysql";
mysql_connect($host, $username, $password)or die("cannot connect");
mysql_select_db($db_name)or die("cannot select DB");
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr><td><form name="form1" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"><tr>
<td bgcolor="#FFFFFF">&nbsp;</td>
<td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>           </tr>
<tr><td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td></tr>
<?php while($rows=mysql_fetch_array($result, MYSQL_ASSOC)){ ?>

<tr><td align="center" bgcolor="#FFFFFF">
<input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>">
</td><td bgcolor="#FFFFFF"><?php echo $rows['id']; ?></td><td bgcolor="#FFFFFF">
<?php echo $rows['name']; ?></td><td bgcolor="#FFFFFF"><?php echo $rows['lastname']; ?>    </td>
<td bgcolor="#FFFFFF"><?php echo $rows['e_mail']; ?>
</td></tr>
<?php } ?>
<tr><td colspan="5" align="center" bgcolor="#FFFFFF">
<input name="delete" type="submit" id="delete" value="Delete"></td></tr>
<?php

if(isset($_POST['delete'])){
$checkbox = $_POST['checkbox'];
for($i=0;$i<count($_POST['checkbox']);$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
print $sql;
$result = mysql_query($sql);}

if($result){echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";}}
mysql_close();
?>
</table></form></td></tr></table>
<p>Record count: <?php echo number_format($count) ?></p>`

In my last post, please disregard leading and trailing tick marks at beginning and ending of code. I thought that it was required to submit my code. Leo
Just click on the "edit" link and remove them. Easy as pie (and easier than commenting...). It should give you your first batch, by the way.

Collectives™ on Stack Overflow

Deleting multiple rows using checkboxes, PHP and MySQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related