I’m making available the MySql IP Geolocation Table we use in our projects. The cost will be $99.00. Anyone interested, contact me via this site.

To offer a web site search facility, the pattern matching function [ select * from table where title like %news%] is often used, only for a lack of knowledge of Mysqls’ natural language query feature. Like Google.com, the optimum is to provide your users with a search feature whereby users type anything, a single word, or a natural language phrase [e.g. iphone 3gs available in san francisco].  Here’s how to set it up in Mysql.

1. create your table
create table as always – example fields:  title varchar(80), description longtext.

2. add the full text index via SQL or using a tool like navicat
alter table add fulltext(title,longtext)

3. A tip: keep in mind, you can’t just add a single record and run a test search.  Several records must be present in the table for the keyword matching facility to work.

For more information, check out mysql.com reference documentation.

As I said yesterday, one of my projects requires geo-location whereby when the home page loads, the geo-location is determined, and a session variable is set for use by queries etc. This is similar functionality to craigslist.org. Go to tradefer.com to see it in action.

I finally found a database that is comprehensive. At this point, I’m ready to modify my php class to access the database. The first step is to convert the ip address to an ip number. Here’s the php code.

1. First tokenize the ip address via period (dot) delimiter, into an array.
$A = $partslist[0];
$B = $partslist[1];
$C = $partslist[2];
$D = $partslist[3];

2. Now run the formula
$r = 256*256*256;
$t = 256*256;

$X = ($A*$r) + $B * $t + $C * 256 + $D;

where $X is the calculated ip number

3. Now run your search against the database using a range.
select * from iptable where start_ip <= ” . $X . ” and end_ip >= ” . $X;

run the query and get your city, and state from the database table. and test the city against a regions table to determine what region the city belongs to, e.g., san francisco bay area.

Once I have it finished I’ll post the class, and a test page.

I’m working on 2 web projects, both with location-specific requirements.  Hesitant to pay for a solution, I searched for free solutions. Came up with a few free sites. One offered  a url query ip look up. Then  I fell into a site offering a free ip location database for download.  I thought the free database would work perfectly. So I got the table into mysql, built a php class,

IP Geolocatoin

iplocation, and tested here in the san francisco bay area. I had a buddy test in New Jersey. It worked at my place, but not in Jersey.

I finally decided to buy a database- I’m in the process of building the database, and modifying my php class. When I’m done, I’ll make the class available here. No charge, with hopes it’ll help someone with the same requirements.

#