delete multiple rows with checkbox- not working??

[argan328]

Hi,

I pulled this code from http://www.phpeasystep.com/mysqlview.php?id=8 but can't get it to actually delete the rows I mark the checkbox... I think the problem is that $delete has no value, I'm just not sure what it should store? The weird thing is when I assign $delete a value like $delete="I like ice cream" it launches into a never ending loop!

 

Can someone tell me what's wrong?    ...thanks in advance!

 

 

<?php
$host="xxxxxxxxxxxx"; // Host name
$username="xxx"; // Mysql username
$password="xxxxx"; // Mysql password
$db_name="xxxxxxx"; // 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>

<?php
// Check if delete button active, start this  ----- this is where i think the problem is with $delete
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>

[btherl]

Replace

 

$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);

 

with

 

$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
echo "About to execute $sql<br>";
$result = mysql_query($sql) or die("Query failed: $sql\n" . mysql_error());

 

and take a look at what it prints out.

 

If it's not what you expect, work backwards through the script printing out other values, such as $checkbox[$i], until you find the problm.

[argan328]

I appreciate the idea, I tried this and I get nothing printed out?

$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
echo "About to execute $sql<br>";
$result = mysql_query($sql) or die("Query failed: $sql\n" . mysql_error());

 

I also placed this at the end of the script after mysql_close();

 

if(isset($del_id))
{
echo "this variable exists";
}
else{
echo "this variable is empty";

}
?>

 

for $del_id, $checkbox[$i], $i I get "this variable is empty"...

 

also I noticed that $sql is defined twice, once near the top as

$sql="SELECT * FROM $tbl_name";

and once toward the bottom as

$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";

  is that a problem?

 

I think the undefined variables are the problem but don't know what they should be defined as... can anyone help?

Thanks

Argan

[Waldir]

change if($delete) to if($_POST['delete'])

[argan328]

that gave me an output that looks like this which pops up for a split second....

 

About to execute DELETE FROM test_mysql WHERE id=''

About to execute DELETE FROM test_mysql WHERE id=''

About to execute DELETE FROM test_mysql WHERE id=''

About to execute DELETE FROM test_mysql WHERE id=''

About to execute DELETE FROM test_mysql WHERE id=''

About to execute DELETE FROM test_mysql WHERE id=''

this variable exists

Delete multiple rows in mysql 

# Id Name Lastname Email

1 Billly Blueton bb5@phpeasystep.com

2 Jame Campbell jame@somewhere.com

3 Mark Jackson mark@phpeasystep.com

4 Linda Travor lin65@phpeasystep.com

5 Joey Ford fordloi@somewhere.com

6 Sidney Gibson gibson@phpeasystep.com

 

and then the top part disappears and I'm left with this:

 

this variable exists

Delete multiple rows in mysql 

# Id Name Lastname Email

1 Billly Blueton bb5@phpeasystep.com

2 Jame Campbell jame@somewhere.com

3 Mark Jackson mark@phpeasystep.com

4 Linda Travor lin65@phpeasystep.com

5 Joey Ford fordloi@somewhere.com

6 Sidney Gibson gibson@phpeasystep.com

 

...nothing is actually deleted

[richardw]

I spent the better part of yesterday working on the same problem. I did get it working, heres my code... please change accordingly ... Best,  Richard

 

<?php
$host="localhost"; // Host name 
$username="UNAME HERE"; // Mysql username 
$password="PWD HERE"; // Mysql password 
$db_name="DBNAME HERE"; // Database name 
$tbl_name="sidewalks"; // 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 ORDER BY ward,street_1,street_nmbr";
$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>Street NMB </strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Street</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Ward</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['street_nmbr']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['street_1']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['ward']; ?></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>

[argan328]

Thanks Richard, I will not be able to test it until back home Friday but appreciate the code.  It looks like the only difference is

$sql="SELECT * FROM $tbl_name ORDER BY ward,street_1,street_nmbr";

  looking forward to testing it.

 

Argan