Collect IP Address background information for WebLog

0

We collect information about all visist to our weblog. We know for example the IP-address for every visitor. It would be interesting to also have the Country for these visitors – so we know who we are catering for. I have created a table WP_VISITORS:

mysql> desc wp_visitors;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| ip               | varchar(15)      | YES  |     | NULL    |       |
| first_visit      | datetime         | YES  |     | NULL    |       |
| number_of_visits | int(10) unsigned | YES  |     | NULL    |       |
| country          | varchar(50)      | YES  |     | NULL    |       |
| continent        | varchar(20)      | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+

in the WordPressDB database. Now I want to set up a daily Java action that will update Country and Continent for the top 100 visitors among the records that have not yet had there country set. This Java action can make use of the IP WebService described in this post: How to lookup information based on IP address – e.g. for Website-visitors

For development, it is more convenient to have the wp_visitors table with test-data available in my local (Oracle) database. I export the table from the WordPressDB database on our Linux-server using this statement:

mysqldump -p --where='number_of_visits>50' wordpressDB wp_visitors > visitordata.txt

The resulting visitordata.txt file can easily be transfered to my laptop computer.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.