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 7 of 7
  1. #1
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post

    loose mysql searching

    i want to figure out how to search my database loosely so if someone misspells a part of a name they are searching for it will still find similar results... kinda like how when you misspell something on Google and it says "Did you mean.... ?".... how would i go about doing this?

  • #2
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts

  • #3
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    mysql's SOUNDEX function is probably what you'll want

  • #4
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    im not familiar with SOUNDEX, could you give me an example query for the following instance

    I'm searching table `profiles` for `fullname` LIKE 'Joshua Johnston'.... but there's only a Joshua Johnson in the database... how would i get it to show Joshua Johnson for the above search?

  • #5
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    SELECT * FROM profiles WHERE fullname SOUNDS LIKE 'Joshua Johnston';


    You might have to do separate matches per word though, in which case you'd need to split the strings, or have separate firstname and surname fields...

  • #6
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    yea i guess i can do a search like this

    PHP Code:
    $name explode(" ",$_GET['q'],10);
    $firstname $name[0];
    $lastname $name[1];
    $sql mysql_query("SELECT * FROM `profiles` WHERE `firstname` SOUNDS LIKE '$firstname' AND `lastname` SOUNDS LIKE '$lastname'") or die(mysql_error()); 
    Last edited by boeing747fp; 07-01-2006 at 01:50 PM.

  • #7
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    ok it all works perfectly... Thanks!

    btw i also found this

    PHP Code:
    <?php
    // input misspelled word
    $input 'carrrot';

    // array of words to check against
    $words  = array('apple','pineapple','banana','orange',
                    
    'radish','carrot','pea','bean','potato');

    // 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\n";
    if (
    $shortest == 0) {
        echo 
    "Exact match found: $closest\n";
    } else {
        echo 
    "Did you mean: $closest?\n";
    }

    ?>


  •  

    Posting Permissions

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