Delete multiple rows with PDO and checkboxes

[davidolson]

<script>
	function setChecked(obj) 
		{
	
		var check = document.getElementsByName("sel_ids[]");
		for (var i=0; i<check.length; i++) 
		   {
		   check[i].checked = obj.checked;
		   }
	}
</script>

if (!empty($_POST['delete'])) {
$deleteIDs = array_filter(array_map('intval', $_POST['sel_ids']));

 

[ginerjm]

??

 

I repeat:

 

??

[davidolson]

to ginerjm Sorry. Lag in my computer.

 

Code does not work. It deletes only one row

<script>
    function setChecked(obj)
        {
    
        var check = document.getElementsByName("sel_ids[]");
        for (var i=0; i<check.length; i++)
         {
         check[i].checked = obj.checked;
         }
    }
</script>
<?php
if (!empty($_POST['delete'])) {
$deleteIDs = array_filter(array_map('intval', $_POST['sel_ids']));
$deleteIDsSQL = implode(', ', $deleteIDs);

$query = "DELETE FROM table_name WHERE `id` IN(:ids)";
$delete = $dbh->prepare($query);
$delete->bindParam(':ids', $deleteIDsSQL);
$success = $delete->execute();

}

print "
<input type=\"checkbox\" name=\"sel_ids[]\" value=\"{$my['id']}\">";
?>

 

[ginerjm]

Can't use a bound variable that has multiple values.  Read the manual

[Ch0cu3r]

bind_param cannot handle multiple values at the same time. Each individual id will need to be bound separately. You could do

// create placeholder for each id
$deleteIDsSQL = implode(',', array_fill(0, count($deleteIDs), '?'));
$query = "DELETE FROM table_name WHERE `id` IN($deleteIDsSQL)";

// now bind each id individually 
call_user_func_array(array($delete, 'bindparams'), $deleteIDs);
$success = $delete->execute();; 

However this should also be fine.

$deleteIDs = array_filter(array_map('intval', $_POST['sel_ids']));
$deleteIDsSQL = implode(', ', $deleteIDs);

$query = "DELETE FROM table_name WHERE `id` IN($deleteIDsSQL)";
$dbb->query($query);

Edited January 7, 2014 by Ch0cu3r

[davidolson]

 

bind_param cannot handle multiple values at the same time. Each individual id will need to be bound separately. You could do

// create placeholder for each id
$deleteIDsSQL = implode(',', array_fill(0, count($deleteIDs), '?'));
$query = "DELETE FROM table_name WHERE `id` IN($deleteIDsSQL)";

// now bind each id individually 
call_user_func_array(array($delete, 'bindparams'), $deleteIDs);
$success = $delete->execute();; 

 

PHP Warning:  call_user_func_array() expects parameter 1 to be a valid callback, class 'PDOStatement' does not have a method 'bindparams'

Edited January 7, 2014 by davidolson

[jazzman1]

Based on Ch0cu3r's example try to loop bindParam.

 

Example:

<?php

$deleteIDs = array(1,2,3,4); // it's a post data from checkboxes

$deleteIDsSQL = implode(',', array_fill(0, count($deleteIDs), ':id'));

$sql = "DELETE FROM table_name WHERE `id` IN($deleteIDsSQL)";

  $username = '';

  $password = '';

  $dbh = new PDO('mysql:dbname=dbName;host=hostAdress;charset=utf8', $username, $password);
 
  $stmt = $dbh->prepare($sql);
 
foreach ($deleteIDs as &$id) {
    
$stmt->bindParam(':id', $id, PDO::PARAM_INT);

}
$stmt->execute();

I don't have any problems with SELECT statement and bindParam in foreach, you shouldn't have a problem to delete multiple columns too.

 

Note,  the "&"  symbol to the value inside foreach.

Edited January 7, 2014 by jazzman1

[mac_gyver]

when binding the input data:

 

if using named place-holders, each one would need to be a unique name in the query so that each different id value will be assigned to its own place-holder.

 

if using ? place-holders, you would need to increment the parameter number, starting with 1, for each iteration of the loop.

[jazzman1]

I don't know this exception using named place-holders

 

He could create a set of random names for thouse named holders.

Edited January 7, 2014 by jazzman1

[kicken]

Since you're intval'ing all the IDs, you can just drop the list into the query. There is no need to mess around with trying to make a list of parameters then bind them.

 

$deleteIDs = array_map('intval', $_POST['sel_ids']);
$deleteIDsSQL = implode(', ', $deleteIDs);

$query = "DELETE FROM table_name WHERE `id` IN(".$deleteIDsSQL.")";

The intval will protect you from any SQL injection concerns. The only thing you need to ensure is that $deleteIDs contains at least one value, otherwise you'll get a syntax error. You can either check for this, or just add a value like 0 (typically unused) into the array.

 

eg:$deleteIDs = array_map('intval', $_POST['sel_ids']) + array(0);

Edited January 7, 2014 by kicken