CODEWITHSUNDEEP
phpmysqlsql
Jamie Turner
Jamie Turner

Reputation: 579

Check for duplicate on one row, if unique check on second

I'm trying to create a keyword system for my site but I have hit a bump in the road.

The database table has five rows DATE | USERID | AMOUNT | KEYWORD | ID

When inserting a row into the database it needs to check if the keyword already exists for that userid, if so the amount increments, if not it's inserted with a default value of 1.

So far I have: 

  $stmt= $conn->prepare("INSERT INTO `keywords` (keyword, userId, id) VALUES(?,?,?) ON DUPLICATE KEY UPDATE `amount` = `amount` + 1");

How do I check if there is a duplicate of the userid value and increment or insert the keyword based on the result?

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269733

Your query is:

INSERT INTO `keywords`(keyword, userId, id)
    VALUES(?,?,?)
    ON DUPLICATE KEY UPDATE `amount` = `amount` + 1;

That looks like what you want to do, with possibly one small change:

INSERT INTO `keywords`(keyword, userId, id)
    VALUES(?,?,?)
    ON DUPLICATE KEY UPDATE `amount` = coalesce(`amount`, 0) + 1;

(This is not needed if amount has a default value other than NULL.)

The other thing you need is a constraint that says that keyword is unique. So, add a unique index:

create unique index idx_keywords_keyword on keywords(keyword)

Upvotes: 1

Related Questions