CODEWITHSUNDEEP
phpmysqlsqlpdo
Alberto Rossi
Alberto Rossi

Reputation: 1800

DELETE statement doesn't delete rows

You can see the table that I have here:

enter image description 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?

Upvotes: 0

Views: 229

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157870

$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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269683

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.

Upvotes: 6

Ashish Ranade
Ashish Ranade

Reputation: 605

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();

   }

Upvotes: -1

Related Questions