Page 1 of 1

Deleting multiple rows with checkboxes...with a twist

Posted: Mon Jun 28, 2004 10:29 am
by FreeBaGeL
I've looked through some tutorials on how to delete multiple rows in a database with checkboxes displayed on a website, and have the following general code:

Code: Select all

<input type="checkbox" name="del[]" value="$recordid">

// in another file:
foreach ($del as $row_id) {
mysql_query("DELETE FROM table WHERE id = '$row_id'");
}
Here's the catch however. The above code assumes that the first row has an ID of 1, the second an ID of 2, etc. However, my rows are defined in a grading database by the students ID number, so they could be an arbitrary number. For instance, right now, the first row has a student ID of 13, but that could change to any number at any given time. How would I go about doing this?

I tried:
<input type="checkbox" name="$Student_ID" value="$recordid">

But that didn't work either.

Posted: Mon Jun 28, 2004 10:48 am
by Grim...
How about:

Code: Select all

<?php
$result=mysql_query("SELECT * FROM ".$tp."table ORDER BY id");
while ( $a_row = mysql_fetch_object( $result ) )
{
	print "<input type="checkbox" name="del[]" value="$a_row->id"><br>";
}

// another page

foreach ($del as $row_id) { 
mysql_query("DELETE FROM table WHERE id = '$row_id'"); 
} 

?>
I'm not sure if that will work, but it's probably along the right lines.

Posted: Mon Jun 28, 2004 10:48 am
by EricS
Your code:

Code: Select all

<input type="checkbox" name="del[]" value="$recordid">
If you setting the value of $recordid to value of id from each row in the database, it will not simply number 1 - whatever. It will delete each row that is checked.

Posted: Mon Jun 28, 2004 11:57 am
by pickle
Your code would work, you just need to make $recordid the id of whatever the student's current row is. So, this should work (pretty much just like ~Grim...'s)

Code: Select all

<?php
$result = mysql_query("SELECT student_name,row_id FROM the_table ORDER BY student_name");
while($row = mysql_fetch_assoc($result))
{
   echo <<<CHECKBOX
<input type = "checkbox" name = "del[]" value = "$row[row_id">$row[student_name]
CHECKBOX;

}

//the other page
foreach($del as $row_id)
{
  myql_query("DELETE FROM the_table WHERE row_id = '$row_id'");
}

?>

Posted: Mon Jun 28, 2004 12:28 pm
by EricS
Missing a backet in CHECKBOX?

Code: Select all

$result = mysql_query("SELECT student_name,row_id FROM the_table ORDER BY student_name"); 
while($row = mysql_fetch_assoc($result)) 
{ 
   echo <<<CHECKBOX 
<input type = "checkbox" name = "del[]" value = "$row[row_id]">$row[student_name] 
CHECKBOX; 
} 

//the other page 
foreach($del as $row_id) 
{ 
  myql_query("DELETE FROM the_table WHERE row_id = '$row_id'"); 
}

Posted: Mon Jun 28, 2004 12:31 pm
by John Cartwright
This is the exact code I use

Code: Select all

<?php
  				foreach($_POST as $key=>$val)
					{ 
    					if(substr($key, 0, 6) == 'delete')
							{ 
      						$del = trim(substr($key, 6)); 

	  						if(!empty($del))
								{
        						$todel[] = $del; 
	  							} 
    						} 
  					} 
		 
  		if(!empty($todel))
			{ 
    		$delstring = join(',', $todel); 
    		mysql_query("DELETE FROM users WHERE id IN($delstring)") or die(mysql_error()); 
  			} 

		
		$res = mysql_query("SELECT id,user FROM users ORDER BY id") or die(mysql_error()); 
		$options = array(); 
		
		while($row = mysql_fetch_assoc($res))
			{ 
  			$options[] = $row; 
			} 
?>
and my form has somehting like this. Notice that each checkbox is withen a loop and the id is appended from the db.

Code: Select all

<?php
echo "<input name='delete".$id."' type='checkbox' value='1'></td>
<input type='submit' name='delete' class='aleft' value='delete'>";
?>

Posted: Mon Jun 28, 2004 1:37 pm
by EricS
Here is one thing that is not quite right. Your code:

Code: Select all

foreach($_POST as $key=>$val) 
               { 
                   if(substr($key, 0, 6) == 'delete') 
                     { 
                        $del = trim(substr($key, 6)); 

                       if(!empty($del)) 
                        { 
                          $todel[] = $del; 
                          } 
                      } 
                 } 
       
        if(!empty($todel)) 
         { 
          $delstring = join(',', $todel); 
          mysql_query("DELETE FROM users WHERE id IN($delstring)") or die(mysql_error()); 
           }
First you should initialize $todel as an empty array. Then use the sizeof() function instead of empty() to check it. My code:

Code: Select all

$todel = array();
     foreach($_POST as $key=>$val) 
               { 
                   if(substr($key, 0, 6) == 'delete') 
                     { 
                        $del = trim(substr($key, 6)); 

                       if(!empty($del)) 
                        { 
                          $todel[] = $del; 
                          } 
                      } 
                 } 
       
        if(sizeof($todel) > 0) 
         { 
          $delstring = join(',', $todel); 
          mysql_query("DELETE FROM users WHERE id IN($delstring)") or die(mysql_error()); 
           }
Next isolate and print the SQL to make sure it's coming out as you expected.

Your code:

Code: Select all

mysql_query("DELETE FROM users WHERE id IN($delstring)") or die(mysql_error());
My Code:

Code: Select all

$sql = "DELETE FROM users WHERE id IN($delstring)";
print $sql;
mysql_query($sql) or die(mysql_error());