DELETE multiple rows in PDO

Question

I'm a rookie in PDO and I've done some search about the issue I'm facing and I wasn't able to find any answers about it. As you can see below, I have this function:

function deleteInfo($id){
    $pdo = connPDO();
    $deleteInfo = $pdo -> prepare("DELETE FROM game_locais_zumbis WHERE id_zumbi IN (:id)");
    $deleteInfo -> bindValue(":id", $id, PDO::PARAM_STR);
    $deleteInfo -> execute();
    $pdo = null;
}

After that, I have the following code:

while($row = $listInfo -> fetch(PDO::FETCH_ASSOC)){
    $ids[] = $row['ids'];
}
$ids = implode(',', $ids);
deleteInfo($ids);

When I echo my $ids, I get:

1,2,3,4,5

But the DELETE function is not deleting all those five rows in my db but only the first one, like "1". When I run that exactly same DELETE function in my db, replacing the ":id" with "1,2,3,4,5", it does work! Does anybody know what's my mistake here? I appreciate any help.

When using bind vars with an IN statement, each entry must be a separate bind variable.... you're trying to DELETE where `id_zumbi IN ('1,2,3,4,5')` (note the quotes that `bindValue()` wraps around a string) — Mark Baker, Aug 17 '14 at 22:38
The fact that you're fetching those ids from `$row['ids']`, it suggests you are getting them from a query. If it isn't the same table originally queried, you might just combine it with a subquery as: `DELETE FROM game_locais_zumbis WHERE id_zumbi IN (SELECT id FROM othertable WHERE somecondition = true)` — Michael Berkowski, Aug 17 '14 at 22:40
I didn't know bind vars get quotes. Interesting! I'm gonna try a workaround for that. Thanks! — user3854140, Aug 17 '14 at 22:51
@user3854140 It isn't that they get quotes exactly - it's that a bound param becomes a _substituted value_ in the executed query. Bound params are _not_ the same as concatenating together values into a SQL statement. The string 1,2,3,4,5 is seen by the query as a single string value since that's what is expected by the single placeholder `:id`. — Michael Berkowski, Aug 17 '14 at 23:02
Nice explanation! Now I can understand what's happening there... thanks! — user3854140, Aug 17 '14 at 23:25

score 6 · Answer 1 · answered Nov 04 '16 at 23:06

6

I would do this:

$query = "DELETE FROM game_locais_zumbis WHERE id_zumbi in (".str_repeat("?,", count($ids) - 1)."?)";
$stmt = $conn->prepare($query);
$stmt->execute($ids);

answered Nov 04 '16 at 23:06

Shawn

2,675
3
25
48

Havenard · Answer 2 · 2016-11-05T00:50:10.690

3

Unfortunately you can't bind an array of elements with prepared statements. You will have to build them in the query directly.

function deleteInfo($ids)
{
    $pdo = connPDO();

    if (!is_array($ids))
        $ids = array($ids); // if it is just one id not in an array, put it in an array so the rest of the code work for all cases

    $ids = array_map([$pdo, 'quote'], $ids); // filter elements for SQL injection

    $pdo->exec('DELETE FROM game_locais_zumbis WHERE id_zumbi IN (' . implode(', ', $ids) . ')');
}

Remember to pass the array to deleteInfo() instead of imploding it into a string.

edited Nov 05 '16 at 00:50

answered Aug 17 '14 at 22:54

Havenard

27,022
5
36
62

Alright, now I see how things work in PDO. I'm learning more with you all here, because I thought that bind vars were not quoted. I appreciate the code you made. Thank you very very much! – user3854140 Aug 17 '14 at 23:06

score 0 · Answer 3 · answered Jun 08 '21 at 23:15

This is how i have done it and it worked. I created an array and looped through it.

<?php
    // set a database connection

    $host = "localhost";
    $user ="root";
    $password = "";
    $db = "pdopost";

    //Set a DSN
    $dsn = 'mysql:host ='.$host . ';dbname='.$db;
    
    // Create a PDO instance
    $pdo = new PDO ($dsn, $user, $password);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

    
    $ids=['6', '7'];
        
    foreach($ids as $id){

    $sql = "DELETE FROM posts WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$id]);

    }

    echo 'Deleted in the database';
   
?>

Shouldn't it be enough to use only the excute in the loop? $sql = "DELETE FROM posts WHERE id = ?"; $stmt = $pdo->prepare($sql); foreach($ids as $id) { $stmt->execute([$id]); } — Alexander Dobernig, Nov 17 '22 at 12:59

DELETE multiple rows in PDO

3 Answers3