Data Management

A simple geo-targeted MySQL database with GeoIP and PHP

MaxMind's GeoIP is a great tool for identifying the country of origin of your Web site's visitors. A common application for this kind of data is geo-targeted advertising. We'll show you how easy it is with a bit of PHP and MySQL.

MaxMind's GeoIP is a great tool for identifying the country of origin of your Web site's visitors. But once you have that information, what do you do with it? A common application for this kind of data is geo-targeted advertising.

Using GeoIP data

The basic steps are fairly straightforward—you read the remote IP address of your visitor, map it to a country code (using the GeoIP mapping database), and then use that country code to display an ad banner designed for that country. Listing A is a sample PHP script that shows these steps.

The script assumes that you have a MySQL database containing a list of ad banners, indexed by country code. Once the client's IP is retrieved via the special $REMOTE_ADDR variable, it can be mapped to a country code. We then use this country code in a query to retrieve a random banner from the list of banners "local" to that country.

Pay special attention to the SQL query—it first retrieves all records (banners) matching the country code returned by the geoip_country_code_by_addr() method, and then randomly selects one from the collection. The corresponding banner filename is then dynamically inserted into the HTML page. If you like, you can further link the banner to a clickthrough tracking script via the unique banner ID to generate statistics on how successful your advertising system is.

Other uses for geographic data

Targeted advertising is by far the most popular application of geographical databases. However, you can also use this information to precisely target users for other types of content: weather forecasts, stock prices, news headlines, etc.

Or you can use this data in other ways, such as redirecting users to content in their local language, restricting/granting access to users based on their location, or automatically adjusting shopping cart displays to use local currencies. I'll leave it to you to find creative applications of this technology. Have fun!

0 comments