DELETE statement doesn't delete rows

Question

You can see the table that I have here:

I filled it with random values for now because I just want to check if the code works. The code I am using is the following:

try {

    $tempdel = $pdo->prepare("DELETE FROM splatoon_players WHERE id = 1;
    DELETE FROM splatoon_players WHERE id = 2;
    DELETE FROM splatoon_players WHERE id = 3;
    DELETE FROM splatoon_players WHERE id = 4;
    DELETE FROM splatoon_players WHERE id = 5;
    DELETE FROM splatoon_players WHERE id = 6;
    DELETE FROM splatoon_players WHERE id = 7;
    DELETE FROM splatoon_players WHERE id = 8;");
    $tempdel->execute();

   } catch (PDOException $e) {

    $error = "Unable to execute the query. ". $e->getMessage();
    exit();

   }

I have spent almost an hour to find out where's the error but I cannot get it. I am using the PDO class of course.

If I execute all the 8 queries in the phpmyadmin shell they work perfectly but running a *.php script with the code above they don't work.

When I say that "they don't work", I simply mean that those rows are still in the table instead of being deleted. The deletion doesn't happen. Any idea?

have you `commit` ed the changes after deleting? – Vamsi Prabhala Aug 20 '15 at 12:34 — Vamsi Prabhala, Aug 20 '15 at 12:34
You can only execute one query at a time. – chris85 Aug 20 '15 at 12:35 — chris85, Aug 20 '15 at 12:35

score 6 · Accepted Answer · edited May 23 '17 at 12:22

6

NOTE: I think I missed the execute the first time around. Hmmm . . . Preparing a statement doesn't execute it. So, you code is only doing string manipulations and setting up data structures in the application. It is not running the code.

In any case, execute executes one statement, typically. See this question.

But you should express this as one query:

DELETE FROM splatoon_players WHERE id in (1, 2, 3, 4, 5, 6, 7, 8);

One query is more efficient.

edited May 23 '17 at 12:22

Community

1
1

answered Aug 20 '15 at 12:35

Gordon Linoff

1,242,037
58
646
786

Doesn't the execute(); work with multiple queries then? – Alberto Rossi Aug 20 '15 at 12:36

score 1 · Answer 2 · answered Aug 20 '15 at 12:46

$ids = array(1,2,3,4,5,6,7,8);
$stmt = $pdo->prepare("DELETE FROM splatoon_players WHERE id = ?");
foreach ($ids as $id)
{
    $stmt->execute(array($id));
}

or

$ids = array(1,2,3,4,5,6,7,8);
$in  = str_repeat('?,', count($ids) - 1) . '?';
$sql = "DELETE FROM splatoon_players WHERE id IN ($in)";
$db->prepare($sql)->execute($ids);

is all the code you need.
Do not use try-catch for the basic error reporting

score -1 · Answer 3 · answered Aug 20 '15 at 12:41

Try using this

try {
        $ids = array('1','2','3','4','5','6','7','8');

        foreach ($ids as $id) {
            $tempdel = $pdo->prepare("DELETE FROM splatoon_players WHERE id = ". $id ."");
            $tempdel->execute();
        }
   } catch (PDOException $e) {

    $error = "Unable to execute the query. ". $e->getMessage();
    exit();

   }

DELETE statement doesn't delete rows

3 Answers3