Datatypes for different fields in a database

[erikla]

I am quite new to PHP and MySQL. I am trying to figure out what datatypes to use for different fields in a Database. For ID (Identification) I use INT, for  name I use VARCHAR and for people to write text messages, I use LONGTEXT. For date and time I use DATETIME.

 

What I am unsure about, however, is what type to use for an email adress and for an IP address? I have been watching videos at Youtube about the Datatype for IP addresses, but they don't seem always to agree. I hope someone can put me in the right direction on those two.

 

Regards,

 

Erik

[requinix]

An email is a string so it gets a string type.

 

An IP address... yeah. What are you going to do with it? As a string it gets stored as something you can easily read but it isn't suited very well for sophisticated searches; as a number ("127.0.0.1" is 2130706433) is great for exact or range searches but it needs formatting in code to get it into the normal A.B.C.D form useful for humans.

[erikla]

Hi requinix

 

Good question. A number of years ago I created a guestbook for my website, with a lot of help from a tutorial. This tutorial did have the IP address for the computer as a field in the database table. I think they added the IP address field in order to be able to lock out certain computers if they applied spam or so, but I am not sure. Now I just need to "repair" the code to make it work on my new Webhotel - since then some of the code has been deprecated and outdated. I still want the table to contain the IP address from contributors. From what you say, I guess a string type will be appropriate. I think I will still be able to compare string against string, right? So VARCHAR for email addresses as well as IP addresses?

 

Erik

Edited June 30, 2014 by erikla

[requinix]

Yup. You can still do exact searches for string IP addresses, of course, but you lose out on the ability to do a range search (such as "everybody using the IP addresses 127.0.0.1 - 127.0.255.255"). But if you don't need to do that then there's no problem.

 

[edit] Worth mentioning is that you can convert string number at any point in the future so what you choose now doesn't have to be set in stone.

Edited June 30, 2014 by requinix

[erikla]

Great! Now I think I understand the different views on the choice of datatype for IP addresses. Thanks a lot!

 

Erik