Reputation: 497
I am trying to record IP addresses of users on my site, and I want to have the following flow when the user logs on
if (new ip address detected)
enter in to db
else if (ip address has already been recorded)
do nothing
So that I am not recording every single time a user logs on, just if it is from a new IP address. I thought that I had the correct code, but duplicates are still entering the DB. Can anyone see where I am going wrong?
public function insertIpAddress($user_id, $user_ip) {
return $this->dbHandler->DbQuery("INSERT into user_ip (user_id, ip_address)
VALUES (?,?) ON DUPLICATE KEY UPDATE `user_id` = `user_id`",
array($user_id,$user_ip));
}
I also tried ON DUPLICATE KEY UPDATE ip_address
= ip_address
, but that didn't work.
My DB looks like this:
and it's coming out like this:
ip_address user_id
10.245.1.38 378
10.245.1.38 378
Upvotes: 1
Views: 45
Reputation: 10114
It doesn't appear that you have a unique index for those two columns. You can add a unique index to your table with the following:
ALTER TABLE user_ip
ADD UNIQUE `unique_user_ip_address_user_id`(`ip_address`, `user_id`);
This creates a unique constraint for the specific combination of IP address and user ID.
As a suggestion, you could add a third column to your user_ip
table to track when the login was attempted and update that field to the current timestamp if there is a duplicate key.
Upvotes: 5