Delete Multiple Rows using Checkbox Form

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Delete Multiple Rows using Checkbox Form

Post 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?
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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. ;-)
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

You'll probably need this:

Code: Select all

$ids = implode(',', $arrayOfIds);
$sql = "DELETE FROM gigdates WHERE pkey IN ($ids)";
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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().
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Code: Select all

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

Code: Select all

some,stuff,in,array
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post by richo »

Cheers guys, sounds like a good technique,

many thanks for the help.
User avatar
sweatje
Forum Contributor
Posts: 277
Joined: Wed Jun 29, 2005 10:04 pm
Location: Iowa, USA

Post by sweatje »

hopefully no one spoofs your form and changes one of the values to "1) or 1=1 --"
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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);
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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:
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post by richo »

Jenk, thanks for the code tips, the technique looks really nice, i'm going to give it a try :)
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post by richo »

Big thanks Jenk, i used the foreach one and it worked perfectly! :)
User avatar
sweatje
Forum Contributor
Posts: 277
Joined: Wed Jun 29, 2005 10:04 pm
Location: Iowa, USA

Post 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'])).")");
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Error for if no check boxes have been ticked

Post 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>
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.';
}
Last edited by s.dot on Sat Aug 12, 2006 11:54 am, edited 1 time in total.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply