...

View Full Version : Search for LIKE names.



iLLin
04-06-2007, 08:49 PM
Ok basically I have a form with city, state, zip.. etc. When someone puts in there city name and upon submitting the form, I want to compare that city name with what I have in my database. To check for spelling and upperlower case info. Basically I will make there city name input match mine in the database.

Here is my dilema. How would I go about finding LIKE occurances in my database. For instance:

city: St. Piere
user input city: StPiere.

Another example
user input city: S.Piere or Piere or Saint Perrei... w/e

I can use there zipcode assuming they input that correctly to find the city, but zips can be in more than one city. Just trying to figure out how I can find city names that are similiar to what the user inputted.

Thanks

nikkiH
04-06-2007, 10:06 PM
Well, SQL Server has SOUNDEX.
http://msdn2.microsoft.com/en-us/library/aa259235(SQL.80).aspx

Check your MySQL version and see if there is a similar function for your dbms.

iLLin
04-06-2007, 10:14 PM
Yea I found that function and I'm also reading up on MATCH. Thanks!

iLLin
04-06-2007, 10:27 PM
Ok this is what I came up with.



$query = "SELECT city_name FROM zip_data WHERE SOUNDEX(city_name) = SOUNDEX('".$city."')";


Is there a better way? Different way? This seems to work but it returns more results than I want. But I will filter them more with zip and state to help with the matching.

iLLin
04-06-2007, 11:22 PM
Ok let me break down my cities and what my issue is right now. I have a zipcode table that contains city, state, etc... information in it. Well it also has city_type. What is city_type you ask? Well its broken down by 3 letters. D = "Default name for City", A = "Acceptable Name for city", and N = "Non-acceptable". Now I will use a city I am close to as an example.

City Name = "Alexander City" //it is in Alabama

This is my query and output



$query = "SELECT zipcode, state_abbr, city_name FROM zip_data WHERE SOUNDEX(city_name) = SOUNDEX('".$city."') AND state_abbr = '".$state."' AND city_type = 'D'";
$result = $db->query($query, true);

$short = -1;
while($row = $db->fetchByAssoc($result)) {
$lev = levenshtein($row['city_name'], $city);
if($lev == 0) {
$word = $city;
}
echo $row['city_name'].":::".$row['state_abbr'].":::".$row['zipcode']."<br />";
}


when I search for say "Alex City" (An Acceptable Name) "Alexander City" does not come up in my search. Now I can find "Alex City" if I remove the city_type filter. But I want to only use default names. I would hate to have to requery the database for each match to get the Default Name.

Anyone got any ideas?

nikkiH
04-09-2007, 06:27 PM
I would hate to have to requery the database for each match to get the Default Name.

Your database should be normalized.
Your current architecture is the issue.

Okay, there has to be at least one more column here. An ID that is unique per city. I'll call it city_id. It identifies Alex City as being the same city as Alexander City. If you were to make normalized tables, this would be a primary key column. For now you can just add it to this table, but it's bad DB design.
So, if you do that, you can make this type of select.



SELECT zipcode, state_abbr, city_name,
(SELECT city_name from zip_data z2 where z2.city_id=z1.city_id and z2.city_type='D') AS default_city
FROM zip_data z1
WHERE SOUNDEX(city_name) = SOUNDEX('city') AND state_abbr = 'state'



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum