Delete checked rows from Database on Click of button

[MSK7]

Hello all,

There is my table in database which contains persons info.

A tabular view ("form1") is a Preview table Similar to original Database table, it retrieves the data from database & show the data recently inserted on Submit.

here is some code




<?php

$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password




// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("mydbase")or die("cannot select DB");


$sql="SELECT * FROM mytbl";
$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="600" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF">&nbsp;</td>
<td colspan="4" bgcolor="#FFFFFF"><strong>My Total Submitted Data</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>Age</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>City</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Country</strong></td>

</tr>
<?
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['Age']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['City']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['Country']; ?></td>

</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>

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

// Check if delete button active, start this

if(isset($_POST['delete']))
{
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM mytbl WHERE id='$del_id' ";
$result = mysql_query($sql) or die(mysql_error());
if(mysql_affected_rows($result) > 0) echo 'Selected data rows Deleted';
}
}

mysql_close();
?>



what i want is when any one select the check box in front of each data rows of "form1" table

& click on "Delete" button displayed below on "form1" ,

that particular selected rows to be deleted from the database &

only remaining data rows in database to be shown in "form1" table.

I have tried some sql queries of 'DELETE from table' but it doesn't work,

So please suggest me code/sql that can be used ahead to delete the selected rows from Database .

Thanks & Regards.

[thetestingsite]

Change this:



if(isset($_POST['delete']))
{
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM mytbl WHERE id='$del_id' ";
$result = mysql_query($sql) or die(mysql_error());
if(mysql_affected_rows($result) > 0) echo 'Selected data rows Deleted';
}
}


to:



if(isset($_POST['delete'])) {
if (count($_POST['checkbox']) > 0) {
foreach ($_POST['checkbox'] as $del_id) {
$sql = "DELETE FROM mytbl WHERE id=' " . $del_id . " ' ";
$result = mysql_query($sql) or die(mysql_error());
if(mysql_affected_rows($result) > 0) echo 'Selected data rows Deleted';
}
}
}


Not tested, but should work. Hope this helps.