0

I have a popup rotator that count unic user's ip per day(NO duplicate ip is allowed for user in same date)

$userip = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
$date = date('Y-m-d');
$result = mysql_query("SELECT `ip` FROM  `popupip` where ip = '$userip' AND userid = $secid AND date='$date'");
$num = mysql_num_rows($result);
    if ($num > 0) {
    // **it is duplicate Do not insert it in popupip table**
    }else{
    // **it is not duplicate ip insert it in popupip table**
    }

above code is an example.I know full code.

but when I look to my phpmyadmin popupip table there is a few duplicate ip for user (the exact same ip address for the user on the same date)

how is it possible?

extra information: in popupip userid is int(11) , date is "date type like 2014-05-30" and ip is varchar. this page may opens "as fast as possible at the same time" by popup pages. is there a relation between openning a page fast at same time by a user and duplicate ip creation? is there a bug in MySQL? (maybe a big bug!!!!)

محمد علی پور فتحکوهی
  • 47
  • 6
  • bad idea IP !=person, 1 person can have many IP's. one IP could be thousands of different people –  May 29 '14 at 20:25

2 Answers2

2

Yes, it is possible. It's a classical case of a race condition.

The quick explanation:

There is a chance that 2 requests simultaneously pass the first check, get $num == 0 and both insert a new row.

To eliminate it you need to create a UNIQUE constraint that covers (user_id, ip, date) columns

The long explanation:

Community
  • 1
  • 1
zerkms
  • 249,484
  • 69
  • 436
  • 539
2

If multiple requests are coming from the same ip at the same time, it is possible that a few of them are executing the select query before any of them executes the insert query (resulting in multiple inserts with the same ip and date).

A solution for this is to create a UNIQUE index with ip, user_id and date (you can easily do this in phpMyAdmin by running the query below):

ALTER TABLE `popupip` ADD UNIQUE (`ip`, `user_id`, `date`);

After that you can optimize your code and simply execute an "INSERT IGNORE" query that only inserts if the combination (ip, user_id, date) doesn't already exist in the table:

mysql_query("INSERT IGNORE INTO `popupip` (`ip`, `user_id`, `date`) VALUES ('$userip', $secid, '$date');");

With this solution you don't need to do the extra "SELECT" query, or the "if" to check if there are results.

Ricardo Nunes
  • 131
  • 4