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.

If you’re looking for a hosting company, I’m here to say, while there are many offering low monthly fees, some of which I’ve used over the years, only one that I’ve found, is reliable, has a 99.99% availability, and is immediately customer support responsive.

Dream Host

The company?  DreamHost.com .  I’ve been with them almost a year. Never had a problem. If I have had a question, they responded with an hour or so. And for what I pay a year, I get unlimited bandwidth and disk space, and unlimited domains and sub-domains. It can’t be beat.

They offer great quick installs for many of the top php-applications, as well as a simple, and advanced install for wordpress.

I highly recommend this company.

Having a day off today. Jim and everyone coming for dinner. Have a good weekend everyone!

Saturday In The Park

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.