DELETE multiple rows from mysql DB WHERE value IN array using prepared statements

Question

I need to delete multiple rows from a Mysql DB where value is IN array. This is what I have so far. It works when sizeof($deleteArray) = 1. I have discovered that if $values = "content, content, content", it will be interpreted as one variable, which I guess makes sense. How do I create a bind-param statement in this situation?

if(sizeof($deleteArray) > 0){
    $query = "";
    $string = "";
    $parameters = "";
    for($i = 0; $i < sizeof($deleteArray); $i++){
        if($i == 0){
            $parameters= $deleteArray[$i];
            $query = "DELETE FROM usernames WHERE value IN (?";
        } else {
            $parameters= $values . ", " . $deleteArray[$i];
            $query = $query . ", ?";
        }
        $string = $string . "s";
    }
    $query = $query . ")";      

    include "databaseLogin.php";
    $stmt = $conn->prepare($query);
    $stmt->bind_param($string, $parameters);
    $stmt->execute();
    $stmt->close();
    $conn->close();
}

Thanks, that looks like what I want; however, I used the accepted answer and continue to get a warning stating "mysqli_stmt::execute() expects exactly 0 parameters, 1 given". I didn't think I was suppose to provide a value when calling execute(). — James, Apr 19 '20 at 05:25
When in doubt, [read the manual](https://www.php.net/manual/en/pdostatement.execute.php). Check [Example #3](https://www.php.net/manual/en/pdostatement.execute.php#example-1101), which uses placeholders like yours. — Don't Panic, Apr 19 '20 at 07:44
I got it working. This was my first intro to PDO and I wasn't connecting to DB correctly. Thanks again. — James, Apr 19 '20 at 17:39

score 0 · Answer 1 · answered Apr 19 '20 at 05:47

Instead using a loop you can create the query using implode() and array_fill() to set all ? marks based on elements in array and then execute without binding parameters;

if(sizeof($deleteArray) > 0){
    // Create query string
    $query = 'DELETE FROM usernames WHERE value IN (';
    // Add all needed ? creating a new array with array_fill()
    $query .= implode(', ', array_fil(0, count($deleteArray), '?'));
    // Close query string
    $query .= ')';

    include "databaseLogin.php";
    $stmt = $conn->prepare($query);
    // Execute without binding parameters
    $stmt->execute($deleteArray);
    $stmt->close();
    $conn->close();
}

DELETE multiple rows from mysql DB WHERE value IN array using prepared statements

1 Answers1