Search Marketing

   
   

Recording IP Addresses in a Database Table as an Integer

Fri May 02, 2008 9:55 pm
<<     >>
Comments: 0 Views: 1626

If you have a website application and wish to record tracking information about your visitors, recording an IP address is one of the main ways to begin.

First you would need to add a new field to an existing database table or create a new table.

mysql> select * from Tracking;
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
+----+--------+---------------------+

Since we do not have an IP Address field, we will add one.

mysql> alter table Tracking add IP int;
Query OK, 5 rows affected (0.17 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from Tracking;
+----+--------+---------------------+------+
| ID | Name | Date | IP |
+----+--------+---------------------+------+
| 1 | Joe | 2001-02-12 00:00:00 | NULL |
| 2 | Jack | 2003-03-15 00:00:00 | NULL |
| 3 | John | 1980-05-15 00:00:00 | NULL |
| 4 | James | 1994-09-21 00:00:00 | NULL |
| 5 | George | 1996-12-11 00:00:00 | NULL |
+----+--------+---------------------+------+

Once we have a place to record the address we want to make sure the IP data from our program gets in the table properly. You could have used a char length, but it would save space by converting the IP address to an integer instead.

mysql> explain Tracking;
+-------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(100) | YES | | NULL | |
| Date | timestamp | NO | | CURRENT_TIMESTAMP | |
| IP | int(11) | YES | | NULL | |
+-------+-----------+------+-----+-------------------+----------------+

If you wanted to convert, and insert your IP data into the table. Use the following command.

mysql> insert into Tracking values(6,'Paul',NOW(),INET_ATON('127.0.0.1'));
Query OK, 1 row affected (0.41 sec)

mysql> select * from Tracking;
+----+--------+---------------------+------------+
| ID | Name | Date | IP |
+----+--------+---------------------+------------+
| 1 | Joe | 2001-02-12 00:00:00 | NULL |
| 2 | Jack | 2003-03-15 00:00:00 | NULL |
| 3 | John | 1980-05-15 00:00:00 | NULL |
| 4 | James | 1994-09-21 00:00:00 | NULL |
| 5 | George | 1996-12-11 00:00:00 | NULL |
| 6 | Paul | 2007-10-09 12:54:58 | 2130706433 |
+----+--------+---------------------+------------+

You can see that when we read out the table information that the IP was converted from a string to an integer.

To read out the IP data properly you would partially reverse the INET_ATON() function to INET_NTOA().

mysql> select ID,Name,Date,INET_NTOA(IP) from Tracking;
+----+--------+---------------------+---------------+
| ID | Name | Date | INET_NTOA(IP) |
+----+--------+---------------------+---------------+
| 1 | Joe | 2001-02-12 00:00:00 | NULL |
| 2 | Jack | 2003-03-15 00:00:00 | NULL |
| 3 | John | 1980-05-15 00:00:00 | NULL |
| 4 | James | 1994-09-21 00:00:00 | NULL |
| 5 | George | 1996-12-11 00:00:00 | NULL |
| 6 | Paul | 2007-10-09 12:54:58 | 127.0.0.1 |
+----+--------+---------------------+---------------+

Remember to backup your database, or copy the /var/lib/mysql source files to a safe location before attempting to utilize commands that may change the structure of your database.



Comments
Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (22 + 1)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc