只有当另一个列匹配时才更新记录?

I'm trying to develop a simple tracking system. What I'm trying to do is either insert the new record or update the record IF it matches the same campaign. I want to insert a new record if the user is triggered on a new campaign. Here is my current query that work fine.

我正在开发一个简单的跟踪系统。我要做的是插入新的记录或者更新记录，如果它匹配相同的活动。如果用户在一个新的战役中被触发，我想插入一个新的记录。下面是我的当前查询，它可以正常工作。

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 is if joe@bob.net triggers campaign1, then it will INSERT the record. If he tries campaign 1 again, it just updates the timestamp. Now when joe@bob.net triggers campaign2, it inserts an entirely new record.

我的目标是如果joe@bob.net触发战役n1，那么它将插入记录。如果他再次尝试竞选1，它只更新时间戳。现在，当joe@bob.net触发campaign n2时，它插入了一个全新的记录。

So basically I'm trying to get it to INSERT only when the user triggers a new campaign. Otherwise, I want it to update the timestamp.

基本上，我只是想让它只在用户触发新活动时插入。否则，我希望它更新时间戳。

Any ideas or advice I'd really appreciate it!

任何想法或建议，我将非常感谢!

2 个解决方案

#1

Unless I'm missing something, all you need is a unique key put on the 'email' and 'campaign' columns on your table:

除非我漏掉了什么，你只需要在你桌子上的“email”和“campaign”栏目上放一个唯一的键:

ALTER TABLE tracking ADD UNIQUE KEY uk_email_campaign (email, campaign);

修改表跟踪添加唯一的uk_email_campaign (email, campaign);

#2

Just add a unique key on (email, campaign) -

只要在(电子邮件，活动)上添加一个唯一的键

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

#1