PDO Delete query doesn't work

Question

i have this code that don't want work, and i can't understand why...

$DeleteT=$con->prepare("DELETE Examples FROM USERS WHERE Email=:Email AND Pass=:Pass");
$DeleteT->bindParam(':Pass', $Pass);
$DeleteT->bindParam(':Email', $Email);
$DeleteT->execute(array(
      ':Email' => $Email,
      ':Pass' => $Pass,
));

I have already tried to search for example of delete query, but i still can't understand why, my value don't want to be delete from database, i have also check if the name of the table and the name of the database match, and they do.

**Never store plain text passwords!** Please use ***PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html)*** to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). ***It is not necessary to [escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Aug 24 '17 at 15:14
You're binding first, then executing with the same parameters again? — Qirel, Aug 24 '17 at 15:15
You either bind the parameters OR execute the array, not both. — Jay Blanchard, Aug 24 '17 at 15:15

score 4 · Accepted Answer · edited Jul 20 '18 at 15:24

You are currently binding the variables twice - once through bindParam(), then once again as the argument to execute(). You need to bind the variable once, either by bindParam() or by execute(), but not both at the same time.

Also, the syntax for deleting is DELETE FROM..., not DELETE column FROM....

So it'll either look like this, binding them through execute()

$DeleteT = $con->prepare("DELETE FROM USERS WHERE Email=:Email AND Pass=:Pass");
$DeleteT->execute(array(
  ':Email' => $Email,
  ':Pass' => $Pass
));

or, if you want to use bindParam()...

$DeleteT = $con->prepare("DELETE FROM USERS WHERE Email=:Email AND Pass=:Pass");
$DeleteT->bindParam(':Pass', $Pass);
$DeleteT->bindParam(':Email', $Email);
$DeleteT->execute();

Either is fine for this purpose, choose which one you like most. PDOStatement::bindParam() has additional options should you need it (there's additional information on that in the documentation linked below), but for the purpose of binding two strings, either way is applicable.

Update:
As per the comments, you wish to just empty out the Examples column. This means that you want to UPDATE the row, not DELETE it. The example below would set the Example column to an empty string (assuming that it's a text or varchar column, and accept strings). If you wish to set it to NULL or another value, modify it as such.

$DeleteT = $con->prepare("UPDATE USERS SET Examples=:example WHERE Email=:Email AND Pass=:Pass");
$DeleteT->execute(array(
  ':example' => '', 
  ':Email' => $Email,
  ':Pass' => $Pass
));

SECURITY NOTE:
You should NEVER store passwords in plain-text, or by using poor methods of hashing (such as md5(), sha1(), etc). It's simply not secure! PHP has built-in functions which you should use to handle storing of passwords, see the password_hash() function which is a lot more secure!

I delete the bindParam, but it still don't work, anyway thank you, for your time! — Martino Pistis, Aug 24 '17 at 15:23
Then you'll need to figure out if there's any errors coming from MySQL. Set PDO to throw exceptions on errors, by adding `$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` after creating the PDO connection. Then check your logs (or enable error-reporting if you haven't already). — Qirel, Aug 24 '17 at 15:25
@A.Rossi There was also a syntax error in the query, see my updated answer. — Qirel, Aug 24 '17 at 15:28
But i want, only delete the Examples, columns, how can i do it? — Martino Pistis, Aug 24 '17 at 15:29
What do you mean by delete that column? Do you want to set it to `NULL`, set to an empty string, or what? `DELETE` removes an entire row. — Qirel, Aug 24 '17 at 15:29
I want to set empty the Example row, of the USERS table where email=:email and pass=:pass — Martino Pistis, Aug 24 '17 at 15:31
See my revised answer about that.That column i's a string, right? And not a number or anything? — Qirel, Aug 24 '17 at 15:32

Masivuye Cokile · Answer 2 · 2017-08-24T15:18:28.783

1

You are binding on the wrong variable you need to bind on $Delete instead of $DeleteT

This is what you need

$DeleteT=$con->prepare("DELETE Examples FROM USERS WHERE Email=:Email AND Pass=:Pass")->execute(array(':Email' => $Email,':Pass' => $Pass));

PS : Note the extra , on ':Pass' => $Pass,)); remove it. Also don't store password in plain text, use password_hash() and password_verify()

edited Aug 24 '17 at 15:18

answered Aug 24 '17 at 15:12

Masivuye Cokile

4,754
3
19
34

Sorry i wrongly type the code, but it still don't work. Anyway tank you for the answer! – Martino Pistis Aug 24 '17 at 15:16
1

write the correct code then, otherwise we won't knw the issue @A.Rossi – Masivuye Cokile Aug 24 '17 at 15:17

Nigel Ren · Answer 3 · 2017-08-24T15:27:02.957

1

Your syntax for the delete isn't standard, should be delete from tableName ...

    $DeleteT=$con->prepare("DELETE FROM USERS WHERE Email=:Email AND Pass=:Pass");
    $DeleteT->execute(array(
      ':Email' => $Email,
      ':Pass' => $Pass
    ));

edited Aug 24 '17 at 15:27

answered Aug 24 '17 at 15:21

Nigel Ren

56,122
11
43
55

PDO Delete query doesn't work

3 Answers3