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(?, ?, ?);
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.
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.
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 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.
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 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.
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.
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.