PHP PDO Related: Update SQL Statement not Updating the content of Database

Question

I am trying to implement update statement using prepared statement in php script but it appears that it is not update record in the database and am not sure why and so would appreciate if you can share some insights.

Code

$query = "UPDATE DatTable SET DF_PARTY_ID = :party_id,
          DF_PARTY_CODE = :party_code,
          DF_CONNECTION_ID = :connection_id WHERE DF_PARTY_ID = ':party_id'";
$stmt = $this->connection->prepare($query);
$stmt->bindValue(':party_id', $data[0], PDO::PARAM_INT);
$stmt->bindValue(':party_code', $data[1], PDO::PARAM_INT);
$stmt->bindValue(':connection_id', $data[2], PDO::PARAM_INT);
$stmt->execute();

Inspiring Solution leading to this approach. Any Suggestions ?

Answer 1

Make sure that the party_id you are trying to update exists in the database.

Also, if your table is InnoDB, make sure that you have autocommit on or issue an explicit commit after the update is made.

Answer 2

instead of guessing, basic error handling must be implemented

$arr = $stmt->errorInfo();
print_r($arr);

Answer 3

Might not help, but why are you only binding 3 variables, when there are 4? I can't say that I have experience doing this in PHP, but in Perl and Oracle it would throw an error. I'd try binding the 2 SETs and the 1 WHERE, and removing the first assignment, and see if that works.

Answer 4

I'm not sure if you want to do what you're trying to do.

Your UPDATE statement basically says update the key and two values based on the NEW value, since party_id is in the SET and WHERE clauses.

You may want to change your prepared statement to this:

UPDATE DatTable SET DF_PARTY_ID = :party_id, DF_PARTY_CODE = :party_code, DF_CONNECTION_ID = :connection_id WHERE DF_PARTY_ID = ':old_party_id'

bind your NEW party_id value to :party_id and the CURRENT one to :old_party_id