[SOLVED] stuck on deleting items from table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Archy
Forum Contributor
Posts: 129
Joined: Fri Jun 18, 2004 2:25 pm
Location: USA

[SOLVED] stuck on deleting items from table

Post 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]
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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());
}
Archy
Forum Contributor
Posts: 129
Joined: Fri Jun 18, 2004 2:25 pm
Location: USA

Post by Archy »

Uhm, I am kinda new to PHP, would you maybe mind going over that in a bit more detail please :?

Thanks.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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 ;)
Archy
Forum Contributor
Posts: 129
Joined: Fri Jun 18, 2004 2:25 pm
Location: USA

Post 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.
Archy
Forum Contributor
Posts: 129
Joined: Fri Jun 18, 2004 2:25 pm
Location: USA

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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')?>
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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>
Archy
Forum Contributor
Posts: 129
Joined: Fri Jun 18, 2004 2:25 pm
Location: USA

Post 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 :)
Post Reply