SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Fetching rows that look kind of like search term

    Well, I'm developing a site for a local amateur theatre group, and on of the features in it is the ability to fetch their personalized rehearsal schedules. On the main page, there will be a form where you can enter some personal detail to get your schredule. From the beginning, I wanted to use the persons Personnummer (kind of like a Swedish equivalent of SSN) since everyone has one, and it is totally unique.

    However, their member database turned out to be crap. I mean, really crap. Most members didn't have a full personnummer, and many didn't even have that. So, I thought I could use phone numbers. No dice. They are a mess, and can't really be used (some fields contain both mobile and home phone number in one, and some contains area codes, and some not). There is pretty much nothing else even remotely consistent other than the names.

    So, how do I go about using names for fetching members? Preferably, I'd like to pick names that kind of look like the "searched" name. Case insensitive, can match partial names, and, if possible, ignore small spelling errors. (For instance, if the database says "Jonathan Reed" I'd like "Jonathan Reid" to match. Basically, I want a query that returns the row that is most like the search term.

    Is this at all possible, and if so, how? I'm using mySQL, by the way.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  2. #2
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A couple of things spring to mind .... (unless I have the wrong idea). Am I right in thinking that you want each individual user to be able to access their profile?

    1) Could you add a new field to the database that assigns every profile a unique ID (an AutoNumber field or something) and then use that to get some kind of handle on the records -- send out everyone's unique number that you've generated for them to their email or something?

    2) Could you create a drop-down of all the names in the database so that people select themselves and click "Go"? That way you get rid of the risk of typos when someone searches ...

    3) Could you do separate SQL queries and order the results according. So, say someone types 'Jonathon Reed' you'd search for any perfect matches first, then run a query that searches for a perfect match on the first name and maybe the first one or two letters of the second name (ie: 'Jonathon' AND 'Re%') and maybe a search for 'Jo%' and 'Reed' and finally 'Jo%' and 'Re%'. If they're typing their own name in I can't see them getting it wrong anyway, and if they do they're bound to turn up a result that way!

    There may be a better way to do this, but with my reasonably limitted knowledge of SQL, that's what I'd do ...


  3. #3
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    dunno about MySQL but in MS SQL Server there's something called soundex which, given to words/names, ranks how phonetically similar they appear.
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  4. #4
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by crowdozer
    dunno about MySQL but in MS SQL Server there's something called soundex which, given to words/names, ranks how phonetically similar they appear.
    This function exists in mySQL, too, and is very close to what I'm looking for. However, I need it to allow room for more "mistakes", if you catch my drift. Is there a way to do that?

    As for gregs suggestions - they are all very good, but only number 3 is viable (and I might just end up using it if I don't find any better solution), since I don't have email adresses, and I don't think the group is too keen on making their 150 members public.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  5. #5
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    And I'm not worried about the user making the typo - I'm worried about typos in the database (which I know for a fact there are plenty of!)
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  6. #6
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well at least it's only 150 users, so you're not going to stretch the server if you have to run a few extra queries. If you add a UniqueID field anyway then that would be a good way to track if a row came up in the previous query and not display it several times if it satisfies all of the queries .... It's a bit messy but it'll work.

    Yours,

    Bodgit & Scarpa!

  7. #7
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Actually, I just came across the perfect function for this. Too bad it's in PHP, not mySQL: Levenshtein().
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com


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
  •