CODEWITHSUNDEEP
phpmysqlon-duplicate-key
user1180888
user1180888

Reputation: 497

On Duplicate key proper use? Not working. MySQL / PHP

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:

enter image description here

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

Answers (1)

Chris Forrence
Chris Forrence

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

Related Questions