Page 1 of 2

Delete Multiple Rows using Checkbox Form

Posted: Mon Aug 07, 2006 2:36 pm
by richo
Hi,

I'm able to delete a row one at a time by assigning the primary key to the value of a checkbox and then submitting it to this code:

Code: Select all

if (isset($_POST['del'])) {

	$delgig = $_POST['delgig'];
	
	$delsql = 	"DELETE FROM gigdates WHERE " .
			"pkey='$delgig'" ;
	if (mysql_query($delsql)) {
	echo ("<p>Gig sucessfully delete</p>");
	} else {
		echo("<p>Error deleting gig: " . mysql_error() . "</p>");
	}
}
But i would like the user to be able to delete more than one row at a time.

Is this possible and if so, how would i do it?

Posted: Mon Aug 07, 2006 5:14 pm
by Ambush Commander
Your HTML will look like this:

Code: Select all

<input type="checkbox" name="delgig[gigname1]" />
<input type="checkbox" name="delgig[gigname2]" />
<input type="checkbox" name="delgig[gigname3]" />
Figure out the rest yourself. ;-)

Posted: Mon Aug 07, 2006 6:54 pm
by Ollie Saunders
You'll probably need this:

Code: Select all

$ids = implode(',', $arrayOfIds);
$sql = "DELETE FROM gigdates WHERE pkey IN ($ids)";

Posted: Tue Aug 08, 2006 3:16 am
by richo
Already got the HTML for the checkboxes (using the pkey as their value) thanks ambush commander.

Ole, i will try your technique out when i get home tonight!

Could you explain the implode thing for me?

Posted: Tue Aug 08, 2006 5:43 am
by s.dot
Implode will get the array of ids into a comma delimited list to be ready to send to your SQL query. Manual page for implode().

Posted: Tue Aug 08, 2006 5:52 am
by Ollie Saunders

Code: Select all

$a = array('some', 'stuff', 'in', 'array');
echo implode(',', $a);
outputs

Code: Select all

some,stuff,in,array

Posted: Tue Aug 08, 2006 6:43 am
by richo
Cheers guys, sounds like a good technique,

many thanks for the help.

Posted: Tue Aug 08, 2006 7:04 am
by sweatje
hopefully no one spoofs your form and changes one of the values to "1) or 1=1 --"

Posted: Tue Aug 08, 2006 7:31 am
by Jenk

Code: Select all

<form action="" method="post">
<input type="checkbox" name="check[]" value="1" />
<input type="checkbox" name="check[]" value="2" />
<input type="checkbox" name="check[]" value="3" />
<input type="checkbox" name="check[]" value="4" />
<input type="checkbox" name="check[]" value="5" />
<input type="checkbox" name="check[]" value="6" />
<input type="submit" />
</form>

Code: Select all

foreach ($_POST['check'] as $val) {
    mysql_query("DELETE FROM `table` WHERE `col` = '" . mysql_real_escape_string($val) . "'");
}


or

Code: Select all

$str = "DELETE FROM `table` WHERE `col` IN (";

foreach ($_POST['check'] as $val) {
    $str .= "'" . mysql_real_escape_string($val) . "', ";
}

$str .= ")";

mysql_query($str);

Posted: Tue Aug 08, 2006 7:37 am
by Ollie Saunders
sweatje wrote:hopefully no one spoofs your form and changes one of the values to "1) or 1=1 --"
Yeah I don't tell them about the security issues so if I need to hack into their web site later I can :D :twisted:

Posted: Tue Aug 08, 2006 2:28 pm
by richo
Jenk, thanks for the code tips, the technique looks really nice, i'm going to give it a try :)

Posted: Tue Aug 08, 2006 2:42 pm
by richo
Big thanks Jenk, i used the foreach one and it worked perfectly! :)

Posted: Tue Aug 08, 2006 3:02 pm
by sweatje
I don't use mysql directly myself, but I would guess something along the lines of:

Code: Select all

mysql_query("DELETE FROM `table` WHERE `col` IN ("
  .implode(',', array_map('mysql_real_escape_string',$_POST['check'])).")");

Error for if no check boxes have been ticked

Posted: Sat Aug 12, 2006 11:06 am
by richo
To bring this topic back up, i have been try to detect if the user doesn't tick any of the checkboxes and presses submit and then consequently write out an error.

I know how to do this for inputs but i can't seem to work out how to do this for the checkboxes.

Here is the delete statement:

Code: Select all

if (isset($_POST['del'])) {

	foreach ($_POST['check'] as $delgig) {
    mysql_query("DELETE FROM gigdates WHERE pkey = '" . mysql_real_escape_string($delgig) . "'");
	}
	
}
And here is the form if anyone needs that:

Code: Select all

<form action="" method="post">
		<ul>
			<li><a href= " ../news.php " >The Vine, Leeds</a><input name="check[]" type="checkbox" value="1" /></li> 
			<li>18th April 2006</li> 
			<li><a href= " ../news.php " >Gig Two</a><input name="check[]" type="checkbox" value="6" /></li> 
			<li>25 August 2006</li> 
		</ul>
	<input type="submit" name="del" value="Delete Gig">
	</form>

Posted: Sat Aug 12, 2006 11:21 am
by s.dot
Something like this is clean and should do the trick efficiently. ;)

Code: Select all

if(!empty($_POST['check'])
{

   //some checkboxes were checked
   $to_be_deleted = array();

   foreach($_POST['check'] AS $delgig)
   {
      $to_be_deleted[] = mysql_real_escape_string($delgig);
   }

   $to_be_deleted = implode(',',$to_be_deleted);

   mysql_query("DELETE FROM gigdates WHERE pkey IN($to_be_deleted)") or die(mysql_error());
        
} else
{
   //no checkboxes were checked
   echo 'Please select some checkboxes.';
}