CODEWITHSUNDEEP
phpmysql
Srikanth Naidu
Srikanth Naidu

Reputation: 787

Deleting Multiple records in php My sql

 <?php


$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="toybox"; // Database name 
$tbl_name="Emp"; // 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);

// echo $count;

?>

<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)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['EmpId']; ?>"></td>
 <td bgcolor="#FFFFFF"><?php echo $rows['EmpId']; ?></td>
 <td bgcolor="#FFFFFF"><?php echo $rows['FirstName']; ?></td>
 <td bgcolor="#FFFFFF"><?php echo $rows['LastName']; ?></td>
 <td bgcolor="#FFFFFF"><?php 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>

<?php
// 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 " Record have been deleted";
 }
}
mysql_close();
?>

</table>
</form>
</td>
</tr>
</table>

Upvotes: 1

Views: 5108

Answers (2)

Manish Shukla
Manish Shukla

Reputation: 11

Working Code .. Consider Point 1 2 and 3

<?php
// Make a MySQL Connection
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("funconnect") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM members") 
or die(mysql_error());  
$count=mysql_num_rows($result);

echo "<form name='sendmail' method='post' action='memberList.php'><table border='1'>";
echo "<tr> <th>Select</th> <th>Name</th> </tr>";
// keeps getting the next row until there are no more to get
$countSn = 0;
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    $chkname = "checkbox".$countSn; #Point 1- Create defferent name for checkboxex like checkbox0, checkbox1 
    echo "<tr><td><input type='checkbox' name=".$chkname." value=".$row['m_id']." /></td>";

    echo "</td><td>"; 
    echo $row['m_name'];
    echo "</td></tr>"; 
    $countSn++;
} 
echo '<tr><td colspan=2><input name="delete" type="submit" id="delete" value="Delete"></td></tr></table></form>';
$delete=$_POST['delete'];
$checkbox=$_POST['checkbox'];
// Check if delete button active, start this
if($delete){
for($i=0;$i<$count;$i++){
    $cname = "checkbox".$i;# Point 2- Create check box name like checkbox0, checkbox1
    $checkbox=$_POST[$cname]; #Point 3 - Retrieve data against name
    echo $i."===".$checkbox."<br />";
    //echo $del_id;
//$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();
?>

Upvotes: 1

Sampson
Sampson

Reputation: 268344

Rather than doing a query for each iteration of your delete-loop, I would build all of the indexes up into a string, and use something like the following:

DELETE FROM tableName 
WHERE id IN (1,2,12,53)

Also, your submit button won't come through as $delete, but instead $_POST["delete"]. And with your connection:

mysql_connect("$host", "$username", "$password")

You really ought not use variables like strings (generally) - this should be written as:

mysql_connect($host, $username, $password)

Furthermore, you've got a few more problems in and around your delete-logic. For instance, I pointed out already that your <input type='submit' name='delete' /> button will be known as $_POST["delete"] once it is registered on the server. Likewise, your checkboxes, having an id value of checkbox[] will simply be known as $_POST["checkbox"] on the server.

Also, your $count variable, used in your delete-logic, is based on the earlier query that selected all of the records to show them. It does not reflect the number of checkboxes to be deleted, it reflects the number of records that were shown. As such, your for loop should not be based on it:

for ($i = 0; $i < count($_POST["checkbox"]); $i++)
{
  // delete $_POST["checkbox"][$i];
}

And again, I would suggest you build a string of values and run a single query instead of multiple.

Upvotes: 5

Related Questions