Here is my code -
<?php
$host = "localhost"; // Host name
$username = ""; // Mysql username
$password = ""; // Mysql password
$db_name = "test"; // Database name
$tbl_name = "test_mysql"; // Table name
// Connect to server and select databse.
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)) {
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox"
id="checkbox[]" value="<? echo $rows['id']; ?>">
</td>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete"
value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
if ($delete) {
for ($i = 0; $i < $count; $i++) {
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}
// if successful redirect to delete_multiple.php
if ($result) {
echo "<meta http-equiv="refresh" content="0;URL = delete_multiple . php">";
}
}
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>
What I want is that data rows of the table, whose checkboxes are checked, to be Deleted from the Database on click of Delete button.
I have tried these but it doesn't work well.
Please suggest me regarding the above code how to delete the selected rows from DB, onclick of Delete button.
Theoretically it should work, but I have not tested
<?php
$host = 'localhost'; // Host name
$username = ''; // Mysql username
$password = ''; // Mysql password
$db_name = 'test'; // Database name
$tbl_name = 'test_mysql'; // Table name
// Connect to server and select databse.
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);
?>
<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)): ?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="need_delete[<? echo $rows['id']; ?>]" type="checkbox" id="checkbox[<? echo $rows['id']; ?>]" value="<? echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><? echo htmlspecialchars($rows['name']); ?></td>
<td bgcolor="#FFFFFF"><? echo htmlspecialchars($rows['lastname']); ?></td>
<td bgcolor="#FFFFFF"><? echo htmlspecialchars($rows['email']); ?></td>
</tr>
<?php endwhile; ?>
<tr>
<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 ( ! empty($_POST['delete'])) {
foreach ($_POST['need_delete'] as $id => $value) {
$sql = 'DELETE FROM `'.$tbl_name.'` WHERE `id`='.(int)$id;
mysql_query($sql);
}
header('Location: delete_multiple.php'); exit();
}
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>
Tested version:
Change lines 45-55 to
if($_POST['delete']){
$i = 0;
while(list($key, $val) = each($_POST['checkbox'])) {
$sql = "DELETE FROM $tbl_name WHERE id='$val'";
mysql_query($sql);
$i += mysql_affected_rows();
}
// if successful redirect to delete_multiple.php
if($i > 0){
echo '<meta http-equiv="refresh" content="0;URL=delete_multiple.php">';
}
}
The file should start with "<?php" and it is considered bad style to mix "<?" and "<?php" in the same file (lines 31-35 and line 43).
EDIT: forgot the $_POST[''] from $delete on line 45. If you have the register_globals ini-directive on, it does not matter (It's still dangerous and bad style though).