Page 1 of 1

[SOLVED] stuck on deleting items from table

Posted: Sun Jun 20, 2004 2:49 pm
by Archy
Right, I have a page which loops through all of the items in a table. I then put a checkbox next to them called delete$id within the same loop. Of course, when fully executed, it will turn into delete1, delete2, delete3, etc, i have checked and can confirm it works.

When a user checks the box, it indicates that we wants it deleted. The user can check multiple checkboxes so make it easier on deleting mass items. I can make it so that one checkbox is deleted easily, but more than that, and it does not work.

To submit the form, it goes to another page, which then loops through all of the items in the database, and then looks at the previous page's variables to see if the checkbox is checked, if it is then it is deleted from the table. Below is what I have made, and is what I would like the final code to look like.

Page with checkboxes...

Code: Select all

<?php
ob_start();
include('header.php'); // connect ($conn) is in the header.php file

$sql_pages = "select * from custom_pages";
$rs_pages = mysql_query($sql_pages, $conn);


while($row = mysql_fetch_array($rs_pages)){
$id = $row['id'];
$name = $row['name'];
$address = $row['address'];
$content = $row['content'];
$order = $row['order'];

echo "<tr>";
echo "<td><font size="2">$order</font></td>";
echo "<td><font size="2">$name</font></td>";
echo "<td><font size="2">$address</font></td>";
echo "<td><a href="?area=manage_p&edit=$name"><font size="2">EDIT</font></a></td>";
echo "<td width="72"><input type="checkbox" name="delete$id" value="ON"></td></tr>";

}
echo "</table>";

// send the form
echo "<br><form action="admin_d_p.php?area=$area" method="post"><input type="submit" value="Delete Pages"></form>";
}

?>
The page that handles the code...

Code: Select all

<?php
ob_start();

include('header.php'); // connect ($conn) is in the header.php file

$sql= "SELECT * FROM custom_pages";
$rs= mysql_query($sql, $conn);

while($row = mysql_fetch_array($rs)){
$id = $row['id'];

     if($delete == "ON"){
          $sql = "DELETE FROM custom_pages WHERE id='$id'";
          $rs = mysql_query($sql, $conn) or die(mysql_error());
     }
}

header("Location:index.php?area=$area");

?>
If anyone knows what I could do, or how I could go about making it work, then I would be thankful.

Thanks.


feyd|use

Code: Select all

tags when posting code[/color]

Posted: Sun Jun 20, 2004 3:30 pm
by markl999
I wouldn't loop through all the rows in the db to see if they need deleting, that's pretty inefficient. Might be easier to do something like:

Code: Select all

foreach($_POST as $key=>$val){
    if(substr($key, 0, 6) == 'delete'){
       $todel[] = substr($key, 6);
    }
}
//$todel will then be an array of id's, like 1,3,5,7 etc..

if(!empty($todel)){
    $delstring = join(',', $todel);
    $sql = "DELETE FROM custom_pages WHERE id IN('$delstring')";
    mysql_query($sql, $conn) or die(mysql_error());
}

Posted: Sun Jun 20, 2004 4:22 pm
by Archy
Uhm, I am kinda new to PHP, would you maybe mind going over that in a bit more detail please :?

Thanks.

Posted: Sun Jun 20, 2004 4:34 pm
by markl999
Well, it sounds like your delete page will be getting values like delete1, delete2, delete5, delete8 etc..etc (all in the $_POST array) where 1,2,5 and 8 are the id's you want to delete from the database.
The query to do this would be DELETE FROM custom_pages WHERE id IN ('1,2,5,8') ... this basically just deletes the row where the id is 1,2,5 or 8. So the first thing to do is to get the id's which means removing the 'delete' bit of 'delete2' (for example), which is what this bit does:

Code: Select all

foreach($_POST as $key=>$val){
    if(substr($key, 0, 6) == 'delete'){
       $todel[] = substr($key, 6);
    }
}
It loops over the posted values and first checks if it is delete*, if so, then the second substr() gets the bit after 'delete', so if it finds delete7, then the substr will get the 7 bit and add it to an array (todel), so basically $todel ends up as an array of all the id's to delete. As the query needs the values in the format 1,2,5,8 then we use the join() command to convert the $todel array into this format, 1,2,5,8 ... then we just run the query.

A bit hard to explain (and i'm not sure i've explained it well) but i hope it makes at bit of sense ;)

Posted: Sun Jun 20, 2004 4:57 pm
by Archy
Uhm, yea, I understand it a lot more thanks :)

Still a bit hazy, but I think I can get it going with what you gave me, thank you very much.

Posted: Mon Jun 21, 2004 12:56 pm
by Archy
I managed to get it to work, but it only deletes one, not multiple amounts from the database. Do you know what may be causing this?

P.s. I was able to copy it into the code exactly how it was, and it worked :P but only deleting one row from the table.

Posted: Mon Jun 21, 2004 4:24 pm
by John Cartwright

Code: Select all

<?php
if (isset($_POST["deletelink"])){
		
		foreach($_POST as $key=>$val){ 
			if(substr($key, 0, 6) == 'delete'){ 
       			$todel[] = substr($key, 6); 
    			} 
		}
		 
			if(!empty($todel))
				{ 
				$delstring = join(',', $todel); 
		 		mysql_query("DELETE FROM users WHERE id IN('$delstring')") or die(mysql_error()); 
				} 
}			
?>
I

Is what I'm using and I have a few questions...

1) Read the above post
2) 2nd is no matter if i press any checkboxes

Code: Select all

<?php
	if(!empty($todel))
?>
The contents of that is always executed.

3) Can you explain this query ?

Code: Select all

<?php
IN('$delstring')?>

Posted: Mon Jun 21, 2004 4:44 pm
by markl999
Okie, i initially just typed it out off the top of my head, so i just decided to test it and there's a bug :o
$sql = "DELETE FROM custom_pages WHERE id IN('$delstring')";
should be:
$sql = "DELETE FROM custom_pages WHERE id IN($delstring)";

Also, if you have a submit button named 'delete' then this causes a slight problem (in that a null value is added to the list of id's to be deleted), so here's my tidied up working example that i used.

Code: Select all

<?php
$db = mysql_connect('localhost', 'user', 'password') or die(mysql_error());
mysql_select_db('test') or die(mysql_error());
if(!empty($_POST['delete'])){ //if we pressed the delete submit button
  foreach($_POST as $key=>$val){
    if(substr($key, 0, 6) == 'delete'){
      $del = trim(substr($key, 6));
      if(!empty($del)){ //this bit ensure the delete submit button itself is ignored
        $todel[] = $del;
      }
    }
  }
  if(!empty($todel)){
    $delstring = join(',', $todel);
    $sql = "DELETE FROM custom_pages WHERE id IN($delstring)";
    mysql_query($sql) or die(mysql_error());
  }
}
//all the below is just for the test form that i used
$sql = "SELECT id,name FROM custom_pages ORDER BY id";
$res = mysql_query($sql) or die(mysql_error());
$options = array();
while($row = mysql_fetch_assoc($res)){
  $options[] = $row;
}
?>
<form action="" method="post">
<?php
foreach($options as $option){
  $optval = 'delete'.$option['itemID'];
  echo '<input type="checkbox" name="'.$optval.'" value="1">&nbsp;'.$option['name'].'<br />';
}
?>
<input type="submit" name="delete" value="delete" />
</form>

Posted: Mon Jun 21, 2004 5:19 pm
by Archy
Thanks a lot mate, I really appreciate this. I have managed to get the script working perfectly, cant believe it was only the ' ' needed taking out, I should have noticed :?

Thanks a lot :)