Deleting multiple rows from mysql with checkbox

[bear24]

Hi all,

I am currently doing on this project and i am struck at this section - which is deleting multiple rows from mysql with checkbox.

The code i am using is

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password="root"; // Mysql password
$db_name="advert"; // 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">&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['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($_POST['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>

The delete dont seem to work. Each time i click on the delete, it didnt delete my records. Where is the error from? Please help!

Thanks a lot.

Regards,
bear

[petr.pavel]

Helo Bear,
I guess the immediate problem is that you reference $checkbox instead of $_POST. You use $_POST so you probably work in an environment with register_globals off.

There is a few other things that I would like to draw your attention to:
* I guess you wanted to use count($checkbox) in your for statement, instead of $count which is a # of your db records. Not that it wouldn't work like you have it now but it makes more sense to use count($checkbox) when you in fact want to traverse $checkbox.

* remove id="checkbox[]" - id must be unique so you can't have it the same for all checkboxes. Also, you don't need it at all if you don't reference the items with JavaScript or CSS.

* it's better to name variables in a way that suggest what information they carry. E.g. $deleteCarsIds[] for an array that holds ids of cars that should be deleted. Variable name $checkbox doesn't suggest anything. Also if you named your variables well you would probably choose $carsDbCount and then it would probably hint to you that using $carsDbCount while traversing $deleteCarsIds isn't right.

* <meta http-equi... works only if it's placed into HEAD part of your document. If it works in your browser then you really cannot believe it will work in other browsers as well.

* either use <?php or <? but choose one and use it consistently

Good luck

[bear24]

Hi,

I am new to php. This is sort of my first time dealing with codes. Moreover, this code is gotten from so of the website. So sorry! I dont really understand what you mean. I am so sorry!

Bear

[nikesh.yadav]

Hi,

I am new to php. This is sort of my first time dealing with codes. Moreover, this code is gotten from so of the website. So sorry! I dont really understand what you mean. I am so sorry!

Bear

great .....................

[Shanti C]

i have modified your code..
and its working now:

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password="1234"; // Mysql password
$db_name="opulent_online1"; // Database name
$tbl_name="users"; // 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");
if($_SERVER['REQUEST_METHOD']=='POST')
{
if(isset($_POST['check_compare']))
{
$sql = "DELETE FROM $tbl_name WHERE find_in_set(uid,'".$_POST['check_compare']."')";
$result = mysql_query($sql);
}
}

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>
<?
// Check if delete button active, start this



?>
<script>
function comparision(){
		d=document.form1;
		var total="";
		if(!d.c.length){
			if(d.c.checked) {
				d.check_compare.value=d.check_compare.value+d.c.value+',';
				return true;
			} else {
				alert("Please select check Box");
				return false;
			}		
		}
		for(var i=0; i < d.c.length; i++){
			if(d.c[i].checked) {
				total +=d.c[i].value + "\n";
				d.check_compare.value=d.check_compare.value+d.c[i].value+',';
			}
		}
		if(d.check_compare.value=="") {
		 alert("Please select atleast one check Box");
		 return false;
		}
	}
</script>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="deleterows.php"onSubmit="return comparision()">
		<input type="hidden" name="check_compare">
<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 type="checkbox" name="c" value="<?=$rows['uid'];?>" id="c"/>
</td>
<td bgcolor="#FFFFFF"><? echo $rows['uid']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['utype']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['uemail']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF">
  
 <input name="compbutton" type="submit" class="Button" value="Compare" /></td>
</tr>

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

[bear24]

I do like to know if i need 2 differ php in order for it to work? In order for the php code to work, i modify some of the parts to suit to my database and so on. But it seems that it still dont work.

The following is what i change:

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password="root"; // Mysql password <- i change
$db_name="advert"; // Database name <- i change
$tbl_name="test_mysql"; // Table name <- i change
// 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");
if($_SERVER['REQUEST_METHOD']=='POST')
{
if(isset($_POST['check_compare']))
{
$sql = "DELETE FROM $tbl_name WHERE find_in_set(id,'".$_POST['check_compare']."')";
$result = mysql_query($sql); //<- i change uid to id
}
}

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>
<?
// Check if delete button active, start this



?>
<script>
function comparision(){
		d=document.form1;
		var total="";
		if(!d.c.length){
			if(d.c.checked) {
				d.check_compare.value=d.check_compare.value+d.c.value+',';
				return true;
			} else {
				alert("Please select check Box");
				return false;
			}		
		}
		for(var i=0; i < d.c.length; i++){
			if(d.c[i].checked) {
				total +=d.c[i].value + "\n";
				d.check_compare.value=d.check_compare.value+d.c[i].value+',';
			}
		}
		if(d.check_compare.value=="") {
		 alert("Please select atleast one check Box");
		 return false;
		}
	}
</script>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="delete_multiple.php"onSubmit="return comparision()"> //<- i change the action file name 
		<input type="hidden" name="check_compare">
<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> //<- i change
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>//
<td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>//<- i change
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>//<- i change
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF">
<input type="checkbox" name="c" value="<?=$rows['id'];?>" id="c"/>
</td>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td> //<- i change to read my db
<td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td> 
<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td> //<- i change to read my db
<td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>//<- i change to read my db
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF">
  
 <input name="compbutton" type="submit" class="Button" value="Compare" /></td>
</tr>

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

I am so sorry that i am so dumb! Do you mind teaching me what are the necessary things i need to change?

[bear24]

I have come out with a new code

<?php
			//mysql connection
			$dbconn = mysql_connect("localhost","root","root") or die();
			mysql_select_db("advert") or die();
			
			$sqlquery = "SELECT * FROM test_mysql"; // query on table
			$sqlresult = mysql_query($sqlquery, $dbconn);
			$count = mysql_num_rows($sqlresult); // count query result			
			
		?>
<table width="400" border="1" cellspacing="1" cellpadding="0">
<tr><td>	<form method="post" action="delete_multiple.php">
<table width="400" border="1" 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($row = mysql_fetch_array($sqlresult)){ ?>

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

<?php } ?>

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

<?php
		//mysql connection here
		
		if($_POST['delete']) // from button name="delete"
		{
			$checkbox = $_POST['checkbox']; //from name="checkbox[]"
			$countCheck = count($_POST['checkbox']);
			
			for($i=0;$i<$countCheck;$i++)
			{
				$del_id  = $checkbox[$i];
				$sql = "delete from test_mysql where id = $del_id";
				$result = mysql_query($sql, $dbconn);
			}
				if($result)
				{
					echo "successful delete";
				}
				else
				{
					echo "Error: ".mysql_error();
				}
		}
	?></table>
</form></td></tr></table>

Now it can delete but my data dont show. I have attached the result of the code. SigH!

[MVied]

This:

<?php while($row = mysql_fetch_array($sqlresult)){ ?>

Should be this:

<?php while($rows = mysql_fetch_assoc($sqlresult)){ ?>

Your variable was named $row and you called it later in the loop as $rows. I've always used mysql_fetch_assoc for loops like this, but I'm not sure if that makes a difference or not.

[bear24]

ok. Thanks. Thanks a lot!