[SOLVED] New code and still no success - deleting MySQL records with checkboxes

[greencoin]

ok - originally I found some code online that added "delete" checkboxes on each row of my query and posted the string to itself when the delete button was pressed. From there it was supposed to delete the record(s) and refresh the page with the updated table. WRONG! The page would load but when I checked the boxes and pressed delete, nothing happened.

 

Well I found some new code that's similar and needed very little modification. But again, I can't get the records to delete. Page loads, boxes check, button pressed and nothing. Page reloads and the records are still there. Checking the Mysql table confirms this. Code is below, please advise.!

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"> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Cid</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Date</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>City</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['cid']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['cid']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['customer']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['date']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['city']; ?></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 cid='$del_id'";
$result = mysql_query($sql);
}

// if successful redirect to delete_multiple.php 
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete.php\">";
}
}
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

 

Thanks in advance, ~Rich

[GingerRobot]

Try:

<?php
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);
$delete = $_POST['delete'];//unless register_globals is on, you'll need to retreve the variables form the post superglobal array
$checkbox = $_POST['checkbox'];

?>
<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"> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Cid</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Date</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>City</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['cid']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['cid']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['customer']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['date']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['city']; ?></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++){
if($checkbox[$i]){
$sql = mysql_query("DELETE FROM $tbl_name WHERE cid='$i'")or die(mysql_error());
}
}
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete.php\">";
}
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

 

Firstly, i expect you needed to get the variables from the post superglobal array. Second, you need to check if each of the checkboxes have been ticked:

if($checkbox[$i]){

$sql = mysql_query("DELETE FROM $tbl_name WHERE cid='$i'")or die(mysql_error());

}

and third, you needed cid to equal $i, as that is the id which you are checking within the loop.

[greencoin]

Page loads fine but does not delete the selected records from the database.   ~Rich

[greencoin]

Forgot to mention, this page is loaded in an IFRAME - will that cause problems? I assumed header posting remains the same even though the browser only shows the main frame's header. Am I wrong? ~Rich

[GingerRobot]

Cant see why the page being in an iframe will cause any issues. Try some debugging on the delete query:

 

<?php
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);
$delete = $_POST['delete'];//unless register_globals is on, you'll need to retreve the variables form the post superglobal array
$checkbox = $_POST['checkbox'];

?>
<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"> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Cid</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Date</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>City</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['cid']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['cid']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['customer']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['date']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['city']; ?></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(isset($delete)){
for($i=0;$i<$count;$i++){
if($checkbox[$i]){
$sql = "DELETE FROM $tbl_name WHERE cid='$i'";
mysql_query($sql) or die(mysql_error());
echo '<br />SQL: '.$sql;
}
}
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete.php\">";
}
mysql_close();
?>
</table>
</form>
</td>
</tr>

 

You should get a line of code printed for each of the checked boxes showing whats being put into the query.

[greencoin]

$debugging = very cool!

 

here's what it gave me;

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1" - line 1 being "<?php" so it has to be something else...

 

MySQL is ver 4.1 run on godaddy servers. what're the most common mistakes associated with this message? thanks ~Rich

[greencoin]

$debugging = very cool!

 

here's what it gave me;

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1" - line 1 being "<?php" so it has to be something else...

 

MySQL is ver 4.1 run on godaddy servers. what're the most common mistakes associated with this message? thanks ~Rich

 

wait a min - line 1 would be the first line of the delete query right? And now that I take a closer look  I think it deleted the first record and is not touching the others. That would tell me it's only passing a value of "1"... am working the problem ~Rich

[GingerRobot]

Ok, if you modify this line:

mysql_query($sql) or die(mysql_error());

to:

mysql_query($sql) or die(mysql_error().'<br />SQL:'.$sql);

 

That way you can see what values are being passed into the query.

[greencoin]

Here's what I get;

 

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1SQL:1"

 

the cid is now 10 and 11 as 1-9 has been deleted (manually) but it's still only passing "0" ~Rich

 

 

[greencoin]

OK, I've found out my checkboxes aren't passing any values to the $checkbox variable. What could be causing this? ~Rich

 

 

[greencoin]

Figured it out...it didn't like the

if($checkbox[$i]){
$sql = "DELETE FROM $tbl_name WHERE cid='$i'"; 

 

Thanks for your help GingerRobot! ~Rich

 

<?
// 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 cid='$del_id'";
$result = mysql_query($sql);
}

// if successful redirect to delete_multiple.php 
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete.php\">";
}
}
mysql_close();
?>