SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Storing User's IP addresses efficiently

    I'm looking for suggestions on how to design a table to save user's IP addresses. I will be saving the last 20-50 IP addresses used. I will not be storing duplicates (unless that is the most efficient way to go here), but I want to record how many times each IP address was used. (So if I check to see if an IP address already exists, and I see that it does, I might want to somehow indicate the number of times I have tried to update the database with this IP address for this user).

    I will use a php script to insert the ip addresses and to check and see if I am inserting a duplicate one, but I am concerned about table design here and how I should store the data efficiently. Here are some concerns:
    Currently I have a table with three columns. Column 1 for the id of a user, Column 2 for a single ip, and Column 3 for the date it was used. Each time a user logs in I check to see if that user's current IP already exists as associated with the user in this table. But I am not sure how to expand the table further (and store the data efficiently) how many times a user has used a certain IP.

    I am probably overlooking something basic here. Any thoughts?

    At the end of the day I want a php script that displays data like this:

    //
    User Name
    1. IP Address 1 (used X many times) (last used)
    2. IP Address 2 (used X many times)
    //

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,909
    Mentioned
    95 Post(s)
    Tagged
    0 Thread(s)
    You'll also need to take into account that there are two IP addressing systems now in use, IPv4 and IPv6 which have different formats for how the IP address is displayed. Unless your dealing with an internal network you'll get some users using IPv4 and some using IPv6
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    Non-Member Max Height's Avatar
    Join Date
    Dec 2011
    Posts
    303
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    One option:

    You could have a table with

    userID
    ipAddress
    fequency
    lastDateUsed

    Then when a user logs in, run a query to see if the ip address currently used by the user exists in the table for that user. If it does, increment the frequencey value for that ip address by 1 and update the lastDateUsed. If the ip address does not exist then insert a new record in the table for that user's current ip address.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Max Height View Post
    Then when a user logs in, run a query to see if the ip address currently used by the user exists in the table for that user. If it does, increment the frequencey value for that ip address by 1 and update the lastDateUsed. If the ip address does not exist then insert a new record in the table for that user's current ip address.
    running a SELECT query and then deciding whether you should use UPDATE or INSERT as a second query is inefficient (two queries)

    just use INSERT ON DUPLICATE KEY UPDATE syntax (one query)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member Max Height's Avatar
    Join Date
    Dec 2011
    Posts
    303
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    yep, that's a better way

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the suggestions. I checked out the INSERT ON DUPLICATE KEY UPDATE syntax but I am not sure I have an appropriate key here, as called for my the mysql manual (http://dev.mysql.com/doc/refman/5.0/...duplicate.html)

    Here's the syntax I came up with, which is likely wrong (I haven't used ON DUPLICATE KEY UPDATE before):

    INSERT INTO ips (userid, ipadress, lastusedtime) VALUES ('{$userID}', '{$currentIP}', '{$lastUsedTime}')
    ON DUPLICATE KEY UPDATE frequency = frequency+1;

    (I set the frequency column as not null and it's default value to 1.)

    The problem is that none of these rows are unique: the userID is used multiple times (one for each ip address), ipaddresses can be the same (people might log in from the same location), lastusedtime might be the same.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    ALTER TABLE ips ADD UNIQUE ( userid , ipaddress )

    it's the combination that you want to be unique, and this composite key will trigger the ON DUPLICATE which is exactly what you want ("see if the ip address currently used by the user exists in the table for that user")
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm still having a bit of difficult implementing what you recommended. For a reason unknown to me, instead of incrementing the frequency by 1, it increments it by 2, when using the insert on duplicate syntax above.


    Here is my show create table output, if it helps:

    Code:
    CREATE TABLE `ips` (
    
      `userid` varchar(45) NOT NULL DEFAULT '0',
    
      `ip` varchar(45) NOT NULL DEFAULT '0',
    
      `ip_date` varchar(45) DEFAULT NULL,
    
      `frequency` varchar(45) NOT NULL DEFAULT '1',
    
      PRIMARY KEY (`userid`,`ip`),
    
      UNIQUE KEY `userid_UNIQUE` (`userid`),
    
      UNIQUE KEY `ip_UNIQUE` (`ip`)
    
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    Any thoughts on why this might be the case?
    I am using the same code I posted above, modified for my new column names.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    could you take a moment and explain why you have those three unique indexes (counting the PK, which is also unique)

    as it stands, each userid can exist in the table only once, which kind of goes against what you said in post #1, suggestions on how to design a table to save user's IP addresses, because that means each user can only ever have one ip

    also, since each ip can only exist in the table once, that means that no users can be on a shared ip (like through a company firewall)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you take a moment and explain why you have those three unique indexes (counting the PK, which is also unique)

    as it stands, each userid can exist in the table only once, which kind of goes against what you said in post #1, suggestions on how to design a table to save user's IP addresses, because that means each user can only ever have one ip

    also, since each ip can only exist in the table once, that means that no users can be on a shared ip (like through a company firewall)
    That's a big error on my part. No column should be unique. As you said earlier(or at least how I interpreted it), the userid/loginip combination should be unique. What I said in post 1 is really what I am after. I am just designing this table badly. I thought I was implementing your "ALTER TABLE ips ADD UNIQUE ( userid , ipaddress )" but I was not.

  11. #11
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you take a moment and explain why you have those three unique indexes (counting the PK, which is also unique)

    as it stands, each userid can exist in the table only once, which kind of goes against what you said in post #1, suggestions on how to design a table to save user's IP addresses, because that means each user can only ever have one ip

    also, since each ip can only exist in the table once, that means that no users can be on a shared ip (like through a company firewall)
    I think I fixed it, but it may contain errors. I am not so great at mysql - your comments would be appreciated.

    Code:
    CREATE TABLE `ips` (
    
      `userid` varchar(45) NOT NULL DEFAULT '0',
    
      `ip` varchar(45) NOT NULL DEFAULT '0',
    
      `lastlogintime` varchar(45) DEFAULT NULL,
    
      `frequency` varchar(45) NOT NULL DEFAULT '1',
    
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    I ran the "ALTER TABLE ips ADD UNIQUE ( userid , ipaddress )" statement but it added a composite index, not a composite key.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ShinVe View Post
    I ran the "ALTER TABLE ips ADD UNIQUE ( userid , ipaddress )" statement but it added a composite index, not a composite key.
    key = index

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    key = index

    Okay, so that is out of the way. Turns out I am still getting 2 added to frequency each time my script is run. I only run this query once, and I also tested it in isolation as well. I am at a big loss here.

    Here's my query again for the record:

    $sql= "INSERT INTO ips (userid, ip, lastlogintime) VALUES ('{$userid}', '{$ip}', '{$lastlogintime}')
    ON DUPLICATE KEY UPDATE frequency = frequency + 1";

    Perhaps there are two duplicate keys problems, and so it is updating frequency twice? That's my best guess.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you tested it outside of php?

    did you remove the extra indexes?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A long awaited update. I found my error: My error handling was executing the statement twice. I copied my error handling every time I ran a test to try to figure this problem out (so that it might display the error), but it was actually causing the error.
    Thanks for your help.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    thanks for the update
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Member
    Join Date
    Mar 2012
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My suggestion was you should store the raw record as per visit regardless of the uniqueness of ip, userid or whatever. Once you have the raw data, the rest is up to you to crunch the needed information. The verification shall not in forefront as it may slow down your website once your record grow in size.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •