Collect IP Address background information for WebLog


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.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on community events and conferences such as JavaOne, Oracle Code and Oracle OpenWorld.

Comments are closed.