#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    4
    Rep Power
    0

    Word search using set characters


    Hello,
    I have a DB that has English words. I want users to enter a string of characters and find only words containing those characters. Just like if they were to enter scrabble letters, it would let them know what words they can build off those letters.

    Example:
    User enters "sedal"
    Then query would return:
    deals
    deal
    sea
    lead
    etc...
    But would not return words that have some of those letters but letters that were not in the query too. Example metals

    Hope this makes sense.
    My last query was:
    WHERE (`word` LIKE '%".LETTER. "%') OR (`word` LIKE '%".ANOTHERLETTER. "%')
    but this returns words that have these letters and others too.

    Any advice would be helpful, this has me pretty stuck!
    I appreciate the help!
    J
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    use your application language (php or whatever) to generate the words
    Code:
    SELECT word
      FROM words
     WHERE word IN
         ( 's' 
         , 'e'
         , 'd'
         , 'a'
         , 'l'
         , 'se'
         , 'ed'
         , 'da'
         , 'al'
         , 'sd'
         , 'ea'
         , 'dl'
         , 'sa'
         , 'el'
         , ...
    for 5 letters entered, there are 5+20+60+120+120=325 possible words
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    use your application language (php or whatever) to generate the words
    Code:
    SELECT word
      FROM words
     WHERE word IN
         ( 's' 
         , 'e'
         , 'd'
         , 'a'
         , 'l'
         , 'se'
         , 'ed'
         , 'da'
         , 'al'
         , 'sd'
         , 'ea'
         , 'dl'
         , 'sa'
         , 'el'
         , ...
    for 5 letters entered, there are 5+20+60+120+120=325 possible words
    But these are not actual words? So you saying to search DB with 325 different mixes of the letters entered? What happens if it searches ea and something is ae? I apologize, I am not quite understanding what you are saying.
    Thanks for the reply
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by jbradley04
    What happens if it searches ea and something is ae?
    those are obviously not equal, are they

    only real words will be returned, no matter what nonsense combinations of letters are searched for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by r937

    only real words will be returned, no matter what nonsense combinations of letters are searched for
    Ok. so one more question. So you are saying to create an array of all the letters using php and use that in the SELECT query, correct?

    I appreciate your patience. I know enough to get myself in trouble...
    Thanks again!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by jbradley04
    So you are saying to create an array of all the letters using php ...
    i have no idea if an array is the way to go about it, because i don't do php, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    4
    Rep Power
    0
    OK, thanks for all your help!
    I will play around with it.

IMN logo majestic logo threadwatch logo seochat tools logo