Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Oct 2005
    Location
    Right Here
    Posts
    654
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Search for LIKE names.

    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

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Well, SQL Server has SOUNDEX.
    http://msdn2.microsoft.com/en-us/lib...5(SQL.80).aspx

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

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Regular Coder
    Join Date
    Oct 2005
    Location
    Right Here
    Posts
    654
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yea I found that function and I'm also reading up on MATCH. Thanks!

  • #4
    Regular Coder
    Join Date
    Oct 2005
    Location
    Right Here
    Posts
    654
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ok this is what I came up with.

    PHP Code:
        $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.

  • #5
    Regular Coder
    Join Date
    Oct 2005
    Location
    Right Here
    Posts
    654
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    PHP Code:

    $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($querytrue);

    $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?

  • #6
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    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.

    Code:
    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'
    Last edited by nikkiH; 04-09-2007 at 06:38 PM.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •