empty value after mysql update php

Question

i made a lot of research around here and Google but i cannot find an answer to this problem.

I update a field in a MySQL database with following code:

public function registerPubKey() {
    
    $stmt = $this->cn->prepare('UPDATE sb_user SET pubkey= ? WHERE email= ?');
    $exres = $stmt->execute(array($this->info["pubkey"], $this->info["email"]));

    if ($exres == false) {
        $resultArray["result"] = "Error registering public key";
        echo json_encode($resultArray);
        exit;
    }
    
    $resultArray["result"] = "success";
    echo json_encode($resultArray);
}

I'm sure that all works except that the field in the database is empty. I dumped the private variable $info and it contains the pubkey (pubkey is a base64 string).

I noticed that if I change the update query with an INSERT, the value is inserted correctly!

Answer 1

It's likely because you're trying to UPDATE non existent rows. Try adding a ON DUPLICATE KEY before. See INSERT ... ON DUPLICATE KEY UPDATE Syntax. UPDATE returns nothing if the row does not exist.