Update record only if another column matches?
I am trying to develop a simple tracking system. What I'm trying to do is either insert a new record or update the record if it matches the same campaign. I want to insert a new post if the user starts in a new campaign. Here is my current request which works great.
INSERT INTO `tracking` (`email`,`ip`,`referrer`,`campaign`,`timestamp`)
VALUES ('$campaign[0]','$ip','$referrer','$campaign[1]','$timestamp')
ON DUPLICATE KEY UPDATE `last_timestamp` = '$timestamp'
My goal: if joe@bob.net starts campaign1 then it will INSERT record. If it tries campaign 1 again, it just updates the timestamp. Now when joe@bob.net launches campaign2, it inserts a completely new post.
So, basically I am trying to get it into INSERT only when the user starts a new campaign. Otherwise, I want it to update the timestamp.
Any ideas or advice that I really appreciate!
In case I'm missing something, all you need is a unique key placed in the "email" and "campaign" columns in your table:
ALTER TABLE tracking
ADD A UNIQUE KEY uk_email_campaign
( email
, campaign
);
Just add a unique key (email, campaign) -
ALTER TABLE `tracking`
ADD UNIQUE INDEX `UQ_email_campaign` (`email`, `campaign`);