pdo delete not working

Question

<?php
$db = new PDO('mysql:host=localhost;dbname=wordpress', 'root','');
//---------prepare
$delete3 = $db->prepare("DELETE FROM wp_term_relationships WHERE object_id=:id");
$delete2 = $db->prepare("DELETE FROM wp_posts WHERE ID=:id");
$delete = $db->prepare("DELETE FROM wp_postmeta WHERE post_id=:id");;
$select = $db->prepare("SELECT post_id FROM reference WHERE x_id=?");
$delete->bindParam(':id', $id, PDO::PARAM_STR);
$delete2->bindParam(':id', $id, PDO::PARAM_STR);
$delete3->bindParam(':id', $id, PDO::PARAM_STR);
//----------
echo 'conected-----';
{$delfeed = 'LOTS OF NUMBERS';}
$array = explode(',',$delfeed);
foreach($array as $deadman){
   $select->execute(array($deadman));
   $row = $select->Fetch(PDO::FETCH_ASSOC);
   $id = $row['post_id'];
      if ($id){
        echo "$id"."\n";
        $delete->execute();
        $delete2->execute();
        $delete3->execute();
}}
echo 'done!';
?>

its a simple delete script but it doesnt delete, it does print the right $id's witch means is working till there but delete goes bananas,double checked table names ,colums... tryied working with question mark place holders insted of bind parameter but nothing UPDATE: its stuck on the first item of the array in the foreach

score 2 · Accepted Answer · edited May 23 '17 at 12:27

The issue is that you are setting the $id variable before it is ever created.

<?php
$db = new PDO('mysql:host=localhost;dbname=wordpress', 'root','');

$delete3 = $db->prepare("DELETE FROM wp_term_relationships WHERE object_id=?");
$delete2 = $db->prepare("DELETE FROM wp_posts WHERE ID=?");
$delete = $db->prepare("DELETE FROM wp_postmeta WHERE post_id=?");
$select = $db->prepare("SELECT post_id FROM reference WHERE x_id=?");

foreach($array as $deadman){
   $select->execute(array($deadman));
   $row = $select->Fetch(PDO::FETCH_ASSOC);
   $id = $row['post_id'];
   if ($id) {
      echo "$id"."\n";
      $data = array($id);
      $delete->execute($data);
      $delete2->execute($data);
      $delete3->execute($data);
   }
   $id++;
}
echo 'done!';
?>

I did updated your code a little bit, since some bits make no sense, such as $delfeed being an empty string, increasing the $id variable even though it gets overwritten, and having random curly brackets placed about. But as you can see, inside the check for if($id), I bind the variables there instead.

Instead of using my method, you can specify the variable $id at the top of your document as being a value of 0; Then, after all of your prepare statement, you can use the function bindParam instead of bindValue.

$id = 0;

// Prepare Statements

$delete->bindParam(':id', $id, PDO::PARAM_STR);
$delete2->bindParam(':id', $id, PDO::PARAM_STR);
$delete3->bindParam(':id', $id, PDO::PARAM_STR);

// Rest of Original Code With No Changes

The reason this works, is because bindParam passes by reference at the time the query executes. See PHP bindParam Document as well as this answer.

Edit: Solution 2 Fixing Foreach Issue

<?php
$db = new PDO('mysql:host=localhost;dbname=wordpress', 'root','');

$delete3 = $db->prepare("DELETE FROM wp_term_relationships WHERE object_id=:id");
$delete2 = $db->prepare("DELETE FROM wp_posts WHERE ID=:id");

// Had a double semicolon trailing the function. Removed one of them
$delete = $db->prepare("DELETE FROM wp_postmeta WHERE post_id=:id");
$select = $db->prepare("SELECT post_id FROM reference WHERE x_id=?");

$id = 0; // Define Variable BEFORE bindParam

$delete->bindParam(':id', $id, PDO::PARAM_STR);
$delete2->bindParam(':id', $id, PDO::PARAM_STR);
$delete3->bindParam(':id', $id, PDO::PARAM_STR);


$delfeed = ''; // Why Have Curly Braces around this?

$array = explode(',',$delfeed);

foreach($array as $deadman) {
   $select->execute(array($deadman));
   $row = $select->Fetch(PDO::FETCH_ASSOC);
   $id = $row['post_id'];
   if ($id){
      echo "$id"."\n";
      $delete->execute();
      $delete2->execute();
      $delete3->execute();
   }
}
echo 'done!';
?>

If you haven't, check the very bottom of the post that I added about using `bindParam`. It may make updating the code easier. — David, Jul 20 '14 at 07:06
good ! but now its stuck in the foreach, $delfeed is full of numbers i just take them out so code doesnt be too long — user3610512, Jul 20 '14 at 07:24
My answer isn't suppose to be a complete copy/paste solution... answers should never be a copy/paste solution. I removed some aspects of your code since parts of it made no sense and it was very hard to read, and so the solution could more easily be seen. Because of the code I removed, the loop may no longer work properly. If you want, you can post your updated code to the original answer and I will take another look at it. Please try formatting your code though. — David, Jul 20 '14 at 07:27
still stuck, after new modifications ,im lost here man...i have to point out that the code will try to select some stuff that does not exist anymore — user3610512, Jul 20 '14 at 07:44
When you say you are stuck in the foreach loop, what do you mean? If you have LOTS of numbers, it's going to take a while for the code to execute. Set the first thing inside of the `foreach` loop to `echo` the variable `deadman` so you can keep track of how far the progress is. Other than that I don't know. I tested my posted code and it works fine. — David, Jul 20 '14 at 07:47

pdo delete not working

1 Answers1