SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple search using MySQL's Fulltext Search?

    I am trying to research how to impliment MySQL 4.X's FTS (Fulltext Search) into my LAN-based invoicing system.

    GOAL: When cashier enters name of customer, instead of just entering the customers name and assigning a customer number ( cust_id ), I want to query the database first and make sure I'm not creating a duplicate entry. I also want to use FTS to allow my accountant to find customers based on firstname/lastname using various spelling variations in the instance that she does not know the correct spelling.

    In addition, my boss, who enters invoices from time to time, is a notorious fat-fingerer ( almost as bad as me ) and so spelling errors are common. I want to be able to query the database, and present him, and me for that matter, with a perspective list of customers already in the database. This functionality will present us with the choice of choosing a customer from a list, or go on to create a new entry.

    So if we type "Marey", the search will try to find an exact match, and if none is available, will search again to return (assuming similar names are already in the db) results like "Marie", "Mary", "Marcy", etc.

    I've been researching levenshtien(), soundex(), and metaphone() as well, but I'm not sure how to implement those functions. However, from what I understand (which may be totally wrong ) MySQL's FTS should be able to accomplish my goal natively. Since I only need to search simple strings as opposed to entire phrases, the solution should be rather simple, right? Could someone post some examples, or at least point me in the right direction? I've learned much in the last few days, but I'm still not sure how to actually do this.

    PS: Hope this was the right forum Semantically, I'm talking about MySQL, but if this post should have been somewhere else, I apologize
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  2. #2
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For the functionality of providing the user with similar names, you would have to store the soundex /metaphone keys of every name in your database, then calculate the soundex key of the name the user entered and compare this key to the keys stored in the database. When an identical key is found, output the word(s) that is/are associated to it.
    You don't want to use levenshtein, because it would require on the fly comparison of the entered name with the names in the database.

  3. #3
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your input Icheb.

    Are you implying that FTS is not applicable here? In which case I have a few other questions:

    you would have to store the soundex /metaphone keys of every name in your database, then calculate the soundex key of the name the user entered and compare this key to the keys stored in the database.
    So, for example, I would have a column "soundex" in the customers table that would hold the soundex() string of the customer's lastname. Would you recommend this proceedure:

    1. Find an identical match. I suppose I could use FTS for this. If no result is found,
    2. See if the string is part of another string, i.e. "Rich" in "Richard" and so forth ( using "LIKE" ). Then,
    3. Obtain a result set of all names in the database that start with that letter, and loop through the results, comparing the soundex string patterns with the search name soundex string pattern.
    4. Remove duplicate results, and output all the possible matches.
    Does this sound like a thorough search? Performance issues? Again, it sounds like maybe I should have posted in the php forum. Sorry !
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  4. #4
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You do not need fulltext search to compare the soundex strings because you will probably store those strings in a char or varchar column . You just find the entries with matching soundex strings.
    How and why do you want to compare the strings in step 3? You already get similar names with soundex, that's what it is there for.
    If you don't get results by comparing the soundex strings, you could of course try the levenshtein approach I discussed above. However, it will be a performance hit if done on a database of significant size.

  5. #5
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again. The reason I was going to use step 3 was to limit the size of the result set obtained by the query, by limiting the results to only those lastnames that started with the same letter as that of the needle.

    But, now I realize that such a search would be redundant and unnecessary. I really appreciate your help
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."



Bookmarks

Posting Permissions

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