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
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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");
}
}Method 2
** 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', '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="9af8f8afdaeaf2eafffbe9e3e9eeffeab4f9f5f7">[email protected]</a>'), (NULL, 'Jame', 'Campbell', '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="9ef4fff3fbdeedf1f3fbe9f6fbecfbb0fdf1f3">[email protected]</a>'), (NULL, 'Mark', 'Jackson', '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="533e32213813233b233632202a202736237d303c3e">[email protected]</a>'), (NULL, 'Linda', 'Travor', '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="076b6e69313247776f776266747e747362772964686a">[email protected]</a>'), (NULL, 'Joey', 'Ford', '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="deb8b1acbab2b1b79eadb1b3bba9b6bbacbbf0bdb1b3">[email protected]</a>'), (NULL, 'Sidney', 'Gibson', '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="4a2d23283925240a3a223a2f2b3933393e2f3a64292527">[email protected]</a>');`
Next, create a PHP (HTML) file called index.php:
** PHP & HTML Code **
`<h1>Deleting Multiple Records using PHP & MySQL</h1>
<p> </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"> </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>`All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0