Prepared statements for DELETE more than one ID (checkbox deleting)

I’m moving from old MySQL over to MySQLi. Right now I’m doing some prepared statements for DELETE and I ran into a problem.

I have a database that displays a list of items. And I have checkboxes to be able to DELETE the items of selected checkboxes instead of deleting them one at a time.

This worked in MySQL with the code below, but I’m trying to get it to work with prepared statement in MySQLi and can’t seem to get it to work. Instead of all the selected checkboxes being deleted, it’s only the first of the checked one that is being deleted.

Wonder if someone can see what I’m doing wrong

EXAMPLE OF OLD CODE without prepared statement


//***REMOVE VIA CHECKBOX***

if(isset($_POST['submit'])){
$delcount = count($_POST['variable']);

   if($delcount > 0){
$idlist = implode(',',$_POST['variable']);

$query = "DELETE FROM audio WHERE audio_id IN($idlist)";

   
$result = mysql_query($query);
	
	//Check whether the query was successful or not
	if($result) {
		header("location: audio-display.php");

		exit();

	}else {
		die("Query failed");


	}


}
}

______________________________________

TRYING TO DO THE SAME WITH PREPARED STATEMENT BELOW

_______________________________________

//***REMOVE VIA CHECKBOX***

if(isset($_POST['submit'])){
$delcount = count($_POST['variable']);

   if($delcount > 0){
$idlist = implode(',',$_POST['variable']);


/* Create the prepared statement */
if ($stmt = $con->prepare("DELETE FROM audio WHERE audio_id IN(?)")) {

/* Bind our params */
$stmt->bind_param('i', $idlist);


/* Execute the prepared Statement */
$stmt->execute();


/* Close the statement */
$stmt->close();

header("location: audio.php");

		exit();

} else {
/* Error */
printf("Error: %s\n", $con->error);
}


}

}

you’re treating an SQL construct (list of items) as single data item (placeholder).

generally, the placeholder (?) represents an atomic data item (e.g. a single string or number) enforcing content-code separation (and thus making SQL Injection impossible).

for constructs like the IN() function that accept multiple data items you need to create a placeholder for each data item.

-- for 3 IDs that would be:
DELETE FROM audio WHERE audio_id IN(?, ?, ?);

and

$params = $_POST['variable'];
array_unshift(array_fill(0, count($_POST['variable']), 'i'));
call_user_func_array(array($stmt, 'bind_param'), $params);
1 Like

To paraphrase Dormilich’s answer, SQL prepared statements cannot handle arrays. Which is a real pain.

There are however libraries which do support this sort of behavior. And since you are moving your code to prepared statements then you might want to check them out.

Doctrine 2’s Database Access Layer is a good example:
http://doctrine-dbal.readthedocs.org/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

public function test1()
{
$dbUrl = 'mysql://tests:tests@localhost/tests';

$config = new \Doctrine\DBAL\Configuration();
$connParams = 
[
  'url' => $dbUrl,
  'driverOptions' => [\PDO::ATTR_EMULATE_PREPARES => false],
];
$conn = \Doctrine\DBAL\DriverManager::getConnection($connParams, $config);

$ids = [1,2,4];
$rows = $conn->executeQuery(
    'SELECT * FROM users WHERE id IN(:ids);',
    ['ids' => $ids],
    ['ids' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
  )->fetchAll();

$this->assertEquals(3,count($rows));
}

Having to specify the PARAM_INT_ARRAY is still a bit of a pain but it’s better than dealing with arrays directly. Doctrine 2 also has some other goodies such as sql query builders and schema information.

Doesn’t PDO generally give you better binding options then MySQLi? I thought I read something about that just the other day.

Scott

PDO binds on a per-value basis. i.e. $stmt->bindValue($placeholder, $value, $type).

I wish I could find that article again…

Scott

Maybe this one?
https://blog.udemy.com/mysql-vs-mysqli/

It has:

(it was reposted at http://www.joomlafreaks.net/blog-item/mysql-vs-mysqli-is-the-improvement-right-for-you in February 2015)

PDO does indeed support named parameters which I like better than positional (?) parameters.

Sadly, PDO does not support array parameters which is where Doctrine DBAL comes in. The Doctrine DBAL connection is just a thin wrapper over the PDO connection object with a few helper functions added.

I found some solution online where they use a loop to get it to work

/* Create the prepared statement */
if ($stmt = $con->prepare("DELETE FROM audio WHERE audio_id = ?")) {

/* Bind our params */
$stmt->bind_param('i', $audio_id);

//Loop through the ids
foreach ($_POST['variable'] as $audio_id) {
    $stmt->execute();
}


/* Close the statement */
$stmt->close();

header("location: audio.php");

		exit();

} else {
/* Error */
printf("Error: %s\n", $con->error);
}

I found the article, but, as it seems, I misunderstood it.

And then I found this.

<?php
/* Execute a prepared statement using an array of values for an IN clause */
$params = array(1, 21, 63, 171);
/* Create a string for the parameter placeholders filled to the number of params */
$place_holders = implode(',', array_fill(0, count($params), '?'));

/*
    This prepares the statement with enough unnamed placeholders for every value
    in our $params array. The values of the $params array are then bound to the
    placeholders in the prepared statement when the statement is executed.
    This is not the same thing as using PDOStatement::bindParam() since this
    requires a reference to the variable. PDOStatement::execute() only binds
    by value instead.
*/
$sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);
?>

If you ask me, that is fairly straight forward and simple.

Scott

1 Like

I would think the bind_param would need to be IN the loop.

//Loop through the ids
foreach ($_POST['variable'] as $audio_id) {
    /* Bind our params */
    $stmt->bind_param('i', $audio_id);
    $stmt->execute();
}    

I’m not an expert, but it DOES work(I’ve tried it) with the bind_param outside the loop too. Why, I don’t know? Maybe it works with it inside the loop as well. Maybe someone can explain that?

It works because you are basically executing the prepared statement multiple times. The bind statement connects the $id to the place holder. Each time you execute the statement, the current value of $id is sent.

I’m not sure what you mean by this, but you can bind array values to placeholder arguments using PDO:

$postIds = array(1, 2, 3, 4);
$query = "DELETE FROM posts WHERE post_id IN (?, ?, ?, ?)";
$stmt = $pdo->prepare($query);
$stmt->execute($postIds);

You can even bind named $key => $value placeholders using PDO:

$values = array(
      ':title' => $_POST['title']
    , ':text' => $_POST['text']
);
$query = "INSERT INTO posts SET title = :title, text = :text";
$stmt = $pdo->prepare($query);
$stmt->execute($values);

Is this what you were referring to?

I didn’t realize $audio_id would be available above when it is defined down in the loop. I am guessing it is because it called in bind-param within prepare and execute even though is has not yet been defined from top to bottom.

Nope. Go back to the opening post.

The goal is ‘DELETE FROM posts WHERE post_id IN (?)’ and then pass in an array of ids. Doing it your way requires counting the number of ids and then building a place holder string. Not so bad for simple queries but becomes painful when you have multiple where clauses.

That is where Doctrine’s DBAL can come in handy.

Then you could just use myesain84’s solution mixed with my solution, couldn’t you?

<?php

$params = array(
                 ':in' => array(1, 21, 63, 171),
                 ':names' => array('bob', 'sally')
                );

$sth = $dbh->prepare("DELETE FROM posts WHERE id IN (:in) AND author IN (:names)");
$sth->execute($params);
?>

Not saying avoid using Doctrine DBAL, it does all this heavy lifting for you. It is just good to understand what it might be doing in the background.

Scott

Nope. Try your solution and see what happens. You will get an array to string conversion error message.

Out of the box, you cannot pass arrays as parameters using PDO.

This is untested but basically you need:

$ids = array(1, 21, 63, 171);
$names = array('bob', 'sally');
$params = array_merge($ids,$names);
$ids_placeholders = implode(',', array_fill(0, count($ids), '?'));
$names_placeholders = implode(',', array_fill(0, count($names), '?'));

$sql = "DELETE FROM posts WHERE id IN ($ids_placeholders) AND author IN ($names_placeholders)";
$sth = $dbh->prepare($sql);
$sth->execute($params);

Ahh… yes. Forgot I need the placeholders. Thanks.

Scott

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.