CODEWITHSUNDEEP
phpmysqlpdo
Pooya
Pooya

Reputation: 1558

how to combine ON DUPLICATE KEY UPDATE with WHERE

I'm trying to use INSERT ON DUPLICATE UPDATE but my question is how can I use WHERE in this query?

function add_link($id,$title,$url,$txt,$keyword,$category,$credit,$description,$active)
{
    $query = $this->dbh->prepare("INSERT INTO links ( id,  userId,  title,  url,  txt,  keyWord,  category,  credit,  description,  active,  status) VALUES 
                                                    (:id, :userId, :title, :url, :txt, :keyWord, :category, :credit, :description, :active, :status)
                                                    ON DUPLICATE KEY UPDATE 
    title=:title, url=:url, txt=:txt, keyWord=:keyWord, category=:category, credit=:credit, description=:description, active=:active, status=:status");
    $query->bindValue(":id",((int)$id==0)?NULL:((int)$id));
    $query->bindValue(":userId",$this->user['id']);
    $query->bindValue(":title",trim($title));
    $query->bindValue(":url",trim($url));
    $query->bindValue(":txt",trim($txt));
    $query->bindValue(":keyWord",trim($keyword));
    $query->bindValue(":category",(int)$category);
    $query->bindValue(":credit",(int)$credit);
    $query->bindValue(":description",trim($description));
    $query->bindValue(":active",(int)$active);
    $query->bindValue(":status",0);
    try{
        $query->execute();
        $this->error(0);
    }catch (PDOException $ex){
        $this->error(104); $this->errStr = $ex->getMessage(); return false;
    }
}

if you check my query you will see the insert query is fine, and the update is working too, but when the update query executes, I want to check userId with active user and if it's the owner of record then refresh fields.

I tried this at the end of query string:

WHERE userId=:userId

but it didn't work.

any solution?

Upvotes: 0

Views: 70

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191749

If userId is already a unique key, you don't need a WHERE clause. ON DUPLICATE KEY UPDATE will only update the row that has the duplciate key already, so you don't have to handle this condition on your own.

Upvotes: 1

Related Questions