How can I prevent the creation of duplicate IP in a table?

Question

I'm using MySQL myisam and some php code to prevent the creation of duplicate ip in popupip table

$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 it is 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!!!!)

"how to prevent duplicate ip to be created in table", before inserting the data to the table, check first if there's already a value on database, like if (ip_numrow == 0) { //Insert SQL Data } — Wesley Brian Lachenal, May 26 '14 at 09:37
I have chacked it. look again the above code. but everday a few duplicated ip created. It is strange — محمد علی پور فتحکوهی, May 26 '14 at 10:10
By 'duplicated ip per user' do you mean 1) the exact same ip address for the user on the same date. Or do you mean 2) there are two entries for the same user on the same date but there are different ip addresses? You code checks for (1) but will not prevent (2). — Ryan Vincent, May 27 '14 at 02:22
I mean (1) the exact same ip address for the user on the same date — محمد علی پور فتحکوهی, May 27 '14 at 07:36
Your insert cannot be using the exact same fields. the most likely thing that is different is that the `date` field has the time in it as well as the date i.e. you used now() rather than the date field you calculated here. And, is the 'userid' column numeric? — Ryan Vincent, May 27 '14 at 08:24
my `userid` is int(11) , my `date` is date types like 2014-05-29 — محمد علی پور فتحکوهی, May 29 '14 at 08:55
possible duplicate of [is it possible creation of duplicate ip in this code?](http://stackoverflow.com/questions/23942184/is-it-possible-creation-of-duplicate-ip-in-this-code) — b.b3rn4rd, Jun 03 '14 at 03:44

Wesley Brian Lachenal · Answer 1 · 2014-05-27T01:07:54.910

Here's an overview of what's gonna be.

Note: I'm not saying this is the best way, but I'll try to help.

First, Delete all your data in popupip so that would remove all your duplicates.

Then to start of, get the client's ip. $client_ip = get_ip();

function get_ip() {

    $ipaddress = '';
    if (getenv('HTTP_CLIENT_IP'))
        $ipaddress = getenv('HTTP_CLIENT_IP');
    else if(getenv('HTTP_X_FORWARDED'))
        $ipaddress = getenv('HTTP_X_FORWARDED');
    else if(getenv('HTTP_FORWARDED_FOR'))
        $ipaddress = getenv('HTTP_FORWARDED_FOR');
    else if(getenv('HTTP_FORWARDED'))
       $ipaddress = getenv('HTTP_FORWARDED');
    else if(getenv('REMOTE_ADDR'))
        $ipaddress = getenv('REMOTE_ADDR');
    else
        $ipaddress = 'Unknown IP';
    return $ipaddress;
}

so the $client_ip has the value of the user's IP. (obviously)

So to determine if the user has already the IP in the database.

$query = $this->db->prepare("SELECT ip_address FROM accounts WHERE user_id = :user_id AND client_ip = :client_ip");
$query->execute(array(':user_id' => $user_id, ':client_ip' => $client_ip));


// No IP in the table
if ($query->rowCount() == 0) {
    //Put INSERT SQL here.
}

thank you . Can you please explain the possiblility of creation of duplicated ip in my code? as I understood, your emphasis was on getting users ip. you mean that $userip = mysql_real_escape_string($_SERVER['REMOTE_ADDR']); may cuase the problem? — محمد علی پور فتحکوهی, May 26 '14 at 10:17
I mean the exact same ip address for the user on the same "date", I need date — محمد علی پور فتحکوهی, May 27 '14 at 07:37

score 0 · Answer 2 · answered May 26 '14 at 08:59

0

you should try using DISTINCT to avoid duplicates in select query,

SELECT DISTINCT `ip` FROM  `popupip` WHERE ip = '$userip' 
AND userid = $secid 
AND DATE='$tarikh'

answered May 26 '14 at 08:59

ravikumar

893
1
8
12

How duplicate ip have been created? how can you explain it? – محمد علی پور فتحکوهی May 26 '14 at 09:12
"How duplicate ip have been created?"... i din get what you asking, but what i understood from your question("how to prevent duplicate ip to view a page?" ) is to avoid duplicates so i used DISTINCT in my answer to avoid the duplicates. – ravikumar May 26 '14 at 09:21
How can I prevent the creation of duplicate IP in popupip table – محمد علی پور فتحکوهی May 26 '14 at 09:29
sorry @محمدعلیپورفتحکوهی for now i dont have any idea about how to prevent the creation of duplicate IP. – ravikumar May 26 '14 at 09:33

score 0 · Answer 3 · answered May 26 '14 at 09:33

0

You should add UNIQUE index to the table. Here is sytnax:

ALTER TABLE yourTableNAme ADD UNIQUE (
`yourColumnName`
)

answered May 26 '14 at 09:33

usef_ksa

1,669
3
23
40

but php code determine that it isn't duplicate ip.I wrote $num = mysql_num_rows($result); to find out . – محمد علی پور فتحکوهی May 26 '14 at 09:40
ALTER TABLE is not a good idea because next day that ip can view page again. – محمد علی پور فتحکوهی May 26 '14 at 10:02
You should run the query only once not every time you visit the page. – usef_ksa May 26 '14 at 11:47

score 0 · Answer 4 · answered May 26 '14 at 10:27

0

As in your code ,I think you first check for an ip that already exist in the table for prevent duplication using this query

SELECT ip FROM popupip where ip = '$userip' AND userid = $secid AND date='$tarikh';

But this query will fail if two userid(users) comes from same ip adress

For eg:

SELECTipFROMpopupip` where ip = '127.0.0.1' AND userid = 1 AND date='26-05-2014';

returns 0 rows and inserted ip.
SELECT ip FROM popupip where ip = '127.0.0.1' AND userid = 2 AND date='26-05-2014';

also returns 0 rows and inserted ip.

So first check ip only,{without any AND or OR} like

SELECT ip FROM popupip where ip = '$userip';

If this query returns 0 insert new row else avoid insertion.

Hope this helps.

answered May 26 '14 at 10:27

Nidhin

1,818
22
23

there is duplicate ip in SELECT `ip` FROM `popupip` where ip = '$userip' AND userid = 1 AND date=''26-05-2014' for eg : two 127.0.0.1 are the result of above query. – محمد علی پور فتحکوهی May 26 '14 at 10:41
this page may open as fast as possible several time by popup pages. is there a relation between openning fast a page at same time by a user and duplicate ip creation? – محمد علی پور فتحکوهی May 26 '14 at 12:25
How you identify a user from moving one page to another?Using session? – Nidhin May 27 '14 at 05:40
NO , user may directly access to this page, it's a popup code. – محمد علی پور فتحکوهی May 27 '14 at 07:40

How can I prevent the creation of duplicate IP in a table?

4 Answers4