Clever Geek Handbook

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!

+3


source to share


2 answers


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

);

+2


source


Just add a unique key (email, campaign) -



ALTER TABLE `tracking`
    ADD UNIQUE INDEX `UQ_email_campaign` (`email`, `campaign`);
+2


source






More articles:


All Articles