CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Kyle Hudson
Kyle Hudson

Reputation: 898

pdo prepared statement doesnt update

This doesnt seem to update the record. Can anyone see why?

    try {
        $status = 'OK';
        $messId = 179981;
        #die(var_dump($messId, $this->campaignId, $this->userId, $status));
        $stmt = $this->dbh->prepare("UPDATE tbl_inbound_responses SET status = :status, sent = '1' WHERE fk_userId = :userId AND fk_campaignId = :campId AND pk_messageId = :messId") or die("Prepare Error");
        $stmt->bindParam(':userId', $this->userId);
        $stmt->bindParam(':campId', $this->campaignId);
        $stmt->bindParam(':messId', $messId);
        $stmt->bindParam(':status', $status);
        $stmt->execute();

        #var_dump($stmt->debugDumpParams(), $stmt->errorInfo());

        if($err = $stmt->errorInfo()) {
            if($err[0] != '00000') {
                var_dump($stmt->debugDumpParams());
            }
        }               

    }
    catch(PDOException $e) {
        die($e->getMessage());
    }

The script also has 

$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

set and 

$stmt->debugDumpParams();

Reports:

SQL: [120] UPDATE tbl_inbound_responses SET status = :status, sent = '1' WHERE fk_userId = :userId AND fk_campaignId = :campId AND pk_messageId = :messId
Params: 4
Key: Name: [7] :userId paramno=-1 name=[7] ":userId" is_param=1 param_type=2
Key: Name: [7] :campId paramno=-1 name=[7] ":campId" is_param=1 param_type=2
Key: Name: [7] :messId paramno=-1 name=[7] ":messId" is_param=1 param_type=2
Key: Name: [7] :status paramno=-1 name=[7] ":status" is_param=1 param_type=2
NULL array(3) { [0]=> string(5) "00000" [1]=> NULL [2]=> NULL }

Cheers.

EDIT:

I don't believe it is the same as:

PHP PDO Update prepared statement problem or PHP PDO Prepared statement query not updating record

Upvotes: 1

Views: 3105

Answers (2)

Hamza Azerhouni
Hamza Azerhouni

Reputation: 26

problem in your query ! use "," instead of "AND" :

$stmt = $this->dbh->prepare("UPDATE tbl_inbound_responses SET status = :status, sent = 1 WHERE fk_userId = :userId, fk_campaignId = :campId, pk_messageId = :messId") or die("Prepare Error");

Upvotes: 0

mario
mario

Reputation: 145482

If you don't receive an exception, then there's nothing wrong with the SQL query or how PDO mangled it. The most likely cause are problems with the parameters. So try to debug it. Replace your ->bindParam calls with just:

    $params = array(
         ':userId' => $this->userId,
         ':campId' => $this->campaignId,
         ':messId' => $messId,
         ':status' => $status,
    );
    var_dump($params);
    $stmt->execute($params);

This might give a hint. If it fails, try ? enumerated params. In any case, rerun the same query with raw 'string values' in the SQL for testing (in any query tool of your choosing).

Upvotes: 2

Related Questions