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.

#

mysql-php

It happens that I’m working on 3 websites simultaneously, so I need to work efficiently.  When it comes to writing php for data inserts and updates on mysql tables (big and small), time is consumed typing out all those variables, and single quotes.  My solution around this?  A class, xSql.  It makes use of the sql catalog feature.

I created 2 primary functions – an insert and update. All is required is to put field names, and values an array. Then the functions do the work, by deciding what the field type is, inserting the single quotes where necessary, and building the insert or update sql. Once the sql statement is constructed, mysql_query is executed.

There are 2 select functions as well. One that grabs all records based on a table name and where field, and a function that grabs specific fields from a table based on a list of fields, and a single or multiple where fields.
And that’s it!

It has afforded me great speed in assembling database operations in my php scripts.  Now on to automatic form generation and validation!