[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 683
  • Last Modified:

combine misspelled user input fields in a DISTINCT query


the query: "SELECT DISTINCT `City` FROM `residential` ORDER BY `City`  ASC";

the above query returns distinct values as is should but the city field in the database is user defined and sometimes misspelled witch causes a second, third, and froth value for that city.
then when trying to use that misspelled city to get the ares of town form the database only the area of town from that misspelled entry are displayed.

query for area of town: "SELECT DISTINCT `Area` FROM `residential` WHERE `City` LIKE \"%$city%\" ORDER BY `Area`  ASC";

Is there any way to combine the distinct results that are similar? Or am i going about this the wrong way?
0
Cybervanes
Asked:
Cybervanes
  • 2
2 Solutions
 
rockiroadsCommented:
fuzzy matching may help you here, possibly use of soundex, bear in mind, it can be very slow.
Further info on that here http://stackoverflow.com/questions/369755/how-do-i-do-a-fuzzy-match-of-company-names-in-mysql-with-php-for-auto-complete
0
 
CybervanesAuthor Commented:
example of results:
citys= Any City |Alamosa|Allison|Antonito|Arboles|Austin|Aztec|Battlement Mesa|Bayfield|Bedrock|Blanca|Cahone|Capulin|Cedaredge|Center|Chama|Chromo|Cimarron|Clifton|Collbran|Colona|Conejos|Cortez|Cortez,|Cortz|CORY|Crawford|Creede|Crested Butte South|Crestone|DE BEQUE|DeBeque|DEL|Del Norte|Delta|Dol|Dolores|Douglas Pass Area|Dove Creek|Duango|Duramgo|Durango|Durango,|Eagle|ECKERT|Farmington|Forbes Park|Fort Garland|Fruita|FRUTIA|FT GARLAND|Ft. Garland|Gand Junction|Garcia|GARND JUNCTION|Gateway|Glade Park|Grand Jct|Grand Jucntion|Grand Junciton|Grand Junctio|Grand Junction|Grand Junction,|Grand Junction, CO|Grand Junctionb|Grand Juntion|Gunnison|Hersperus|Hesperus|Hooper|Hotchkiss|Ignacio|Ignaico|Jasper|La Garita|La Jara|LaGarita|Lake City|Lake Powell|Lewis|Loma|MACK|Manassa|Mancos|Marble|Maybell|Meeker|MESA|Middle Mesa|Moffat|MOLINA|Monte Vista|Monticello|Montose|Montroe|Montrose|Montrose (Colona)|Montrose,|Montrpse|Mosca|Mountain Village|Mt Crested Butte|Naturita|New Castle|Norwood|NUCLA|Olathe|Our|OURAY|Ouray County|Oxford|Pagosa|PAGOSA SPRING|Pagosa Springs|Palisade|Palisade, CO|Palisdae|Paonia|PARACHUTE|PARADOX|Placerville|Pleasant View|PleasantView|Powderhorn|Rangely|redvale|Rico|Ridgway|Ridway|RIFLE|Saguache|San Acacio|San Luis|Sanford|Silt|Silverthorne|Silverton|Somerset|South Fork|Taos|TBD|Telluride|Tierra Amarilla|Villa Grove|Weson|Whitewater|Yellow Jacket|
0
 
CybervanesAuthor Commented:
although rockiroads: solution's probably would have worked i decided to fix the spelling when the database is populated.

 i have attached my code.
// data9 is the city field
// MUST MAKE THE STRING CAP FIRST LETTER IN EACH WORD FIRST
$data[9] = strtolower($data[9]);
$data[9] = ucwords($data[9]);
// THEN CHACK IS AGENST ARRY OF CORRECTLY SPELLED CITYS
 
 
// input misspelled word
$input = $data[9];
 
// array of words to check against
$words = array("Grand Junction","Fruita","Alamosa","Allison","Antonito","Antonito","Austin","Aztec","Arboles","Battlement Mesa","Bayfield","Bedrock","Blanca","Cahone","Capulin","Cedaredge","Center","Chama","Chromo","Cimarron","Clifton","Collbran","Colona","Conejos","Cortez","Crawford","Crested Butte","Crestone","Creede","DeBeque","Del Norte","Delta","Dolores","Douglas Pass Area","Dove Creek","Durango","Eagle","Eckert","Farmington","Forbes Park","Fort Garland","Garcia","Gateway","Glade Park","Gunnison","Hesperus","Hesperus","Hotchkiss","Hooper","Ignacio","Jasper","LaGarita","La Jara","Lake City","Lake Powell","Lewis","Loma","Mack","Manassa","Mancos","Marble","Maybell","Meeker","Mesa","Middle Mesa","Moffat","Molina","Monte Vista","Monticello","Montrose","Montrose (Colona)","Mosca","Mountain Village","Mt Crested Butte","Naturita","New Castle","Norwood","Nucla","Olathe","Ouray","Oxford","Pagosa Springs","Palisade","Paonia","Parachute","Paradox","Placerville","Pleasant View","Powderhorn","Rangely","Redvale","Rico","Ridgway","Rifle","Saguache","San Acacio","San Luis","Sanford","Silt","Silverthorne","Silverton","Somerset","South Fork","Taos","Telluride","Tierra Amarilla","Villa Grove","Weson","Whitewater","Yellow Jacket");
 
// no shortest distance found, yet
$shortest = -1;
 
// loop through words to find the closest
foreach ($words as $word) {
 
    // calculate the distance between the input word,
    // and the current word
    $lev = levenshtein($input, $word);
 
    // check for an exact match
    if ($lev == 0) {
 
        // closest word is this one (exact match)
        $closest = $word;
        $shortest = 0;
 
        // break out of the loop; we've found an exact match
        break;
    }
 
    // if this distance is less than the next found shortest
    // distance, OR if a next shortest word has not yet been found
    if ($lev <= $shortest || $shortest < 0) {
        // set the closest match, and shortest distance
        $closest  = $word;
        $shortest = $lev;
    }
}
 
//echo "Input word: $input  --  ";
if ($shortest == 0) {
    //echo "Exact match found: $closest <br/>";
} else {
    //echo " <font color=\"blue\" size=\"+1\">Did you mean: $closest? </font><br/>";
	$data[9] = $closest;
	$errors .= "changed MLS#" . $data[0] . " city from " . $input . " to " . $closest . "<br/>";
}

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now