How to get and store visitors IP address in a MySQL database.

[kokowawa]

Hello there,

I have a PHP-based webpage using MySql. I want to store the IP-adresses of the visitors into DB and when it's a new visitor (ip) the start page will show an extra image (or pop up) that will never be shown again for the same IP.

Please help me, I've got the idea but not the code.

[Atli]

Hi, and welcome to The Scripts!

I've changed the title of this thread to better describe it's topic.
Using good, descriptive titles that follow the Posting Guidelines will increase your chances of getting you questions answered!

As for you question...

You can get the IP address for you visitors through the $_SERVER super-global:

$ipAddress = $_SERVER['REMOTE_ADDR'];
echo "Your IP is : $ipAddress";
 

To insert it into a MySQL database you can use the MySQL Functions, or better yet, the Improved MySQL Functions.
If you don't know how to use MySQL, check out this article.

I'm not sure, however, that storing the IP address would be the best way to go about this. Many ISP's keep changing their customer's IP addresses and there are ways in which a use can change his own IP address. Proxy servers can also cause problems if you rely much on using IP addresses.

You should think about using Cookies

[anvesh]

As per my knowledge you have to store ip address into different part of integer column. String operation is always against database performance. Simply, you can divide your full length of ip address into different small four part.

In future if analysis is require then also you can easily perform using different columns. I have already created full detailed explanation into my blog. please visit this url, this is really very interesting theory on storage of IP-Addresses.

[RonB]

Why are you posting to an 8yr old thread? The OP has not been back since posting that question.

Also, on a side note, your blog post recommending splitting and storing the IP in 4 separate tinyint fields is just as bad, if not worse, as storing it in a single varchar.

The best storage method is a single unsigned int field and use mysql's inet_aton() and inet_ntoa() functions as needed when converting.

[Atli]

I agree with you, RonB, on the 4x tinyint fields. I can see no practical reason for doing that.

However, while storing it as a uint vs string is better as far as storage space goes, in practice storing it as a string has some advantages.

• Generally IP addresses are used in their human-readable format, so storing it as a uint will require a great deal of conversion back from the uint format. That adds code complexity and a performance penalty (albeit a very small one.)
  
• Storing it in a string format can simplify searching for IP patterns, by simply doing: WHERE ip LIKE '192.168.%'. Stored as a uint, the same is of course possible, but again it adds unnecessary complexity.

Also, the uint storage space advantage isn't a major concern in most situations. Not unless you're storing a huge amount of data does it actually matter. The rule of thumb is to prioritize simplicity over optimization. No need to overcomplicate a situation for a negligible gain.

I would always suggest storing as a string unless you have a compelling reason not to.

[RonB]

I'd say that both types of storage have their advantages and disadvantages.

Lets take your 2 points.

1) Mysql has built-in functions to handle the conversion which removes the complexity issue and if there is a performance issue, which I have not experienced, it would be negligible. If that conversion was being done outside of mysql, then the complexity and performance issue would be a concern.

2) Storing it as a string and using a LIKE clause only works well when dealing with class full addresses (i.e., CIDR /8, /16, and /24). If you're working with any other subnet range, then the string approach is more difficult and cumbersome. Storing as an INT, I can easily deal with any range.

When stored as strings, how would you select the IP's in this network? 10.100.0.0/21

edit: I should clarify that question. How would you do it without having a group of OR LIKE clauses (1 for each /24 segment)?

In my case I'm dealing with hundreds of /21, /22, and 27 networks and storing IP's as strings would add a lot of complications. Storing them as INT added simplicity in my case.

[Atli]

I agree, that each has their advantages.

1) Whether the conversion is done in MySQL or the actual code isn't really the point. It'll just be adding the complexity on another layer. For straight up SQL queries it's not a big deal but consider, for example, if you are building this on top of a framework that uses an ORM, or some other type of DBAL. (Which is fairly standard in the enterprise space.) In that scenario, calling a MySQL function like that on a field during retrieval will most likely require a great deal of unnecessary clutter.

2) As for you /21 example, I'll also agree that in that case you're probably better of with an uint. However that is not a common scenario; it's an edge case. I doubt 99.9% of sites that record IP addresses - for whatever reason - will ever need to do a more complicated search than a straight up boolean and/or LIKE comparison can provide.

My main point here is: it's generally best to start with as simple a solution as you can get away with, and only add complexity as it becomes necessary. In my experience, when you do something more complex than the current situation requires, it ends up being a wasted effort.