Question about a query I thought would be a lot simpler!

[jbradley04]

Hello,

I am trying to run MySQL query that will take a bunch of letters and do a couple things.

First:

If they enter "drea" then I want to search for all words in my DB that contain those letters and only those letters.  So the result will be"

read

dear

ear

red

etc.....

 

Next I want to run another query that would have Exactly the letters in the word.

So the same query "drea" would only come up with:

read

dear

 

Any help would be greatly appreciated!

Thanks!

[Barand]

For your first requirement you need to construct a query with this WHERE clause

WHERE (col LIKE '%d%') OR (col LIKE '%r%') OR (col LIKE '%e%') OR (col LIKE '%a%')

For the second, replace the OR with AND

[jbradley04]

Thank you for your help!

That is a little beyond my knowledge so I appreciate you helping me out.  I would have never got that! 

Thanks again,

J

[jbradley04]

Quick question.  When I use the OR command it returns words that contain more than what I want.

For example:

If I search "ogf"

Right now it would come up with friend, hog, ogor, etc... because they contain at least one of those letters.  

I am looking for it to come back with ONLY words that contain the letters searched. 

So ogf would only come up with:

fog

of

 

But no other words that only contain one or all those letters but also have other letters too.  

 

Hope that makes sense!

Thanks

[mac_gyver]

i'm not sure you can do all of this in a query, but one fairly efficient way (over forming all the permutations of the target letters) would be -

 

1) use a query to fetch all words that have any of the target letters. you should be able to use a mysql REGEXP match to do this without building the list of (... like) OR (... like )... terms.

 

2) when retrieving the words from the query, remove all the target letters from the words and any result that is zero length was made up of only the target letters. any result that has a non-zero length after removing the target letters isn't a possible word.

[mac_gyver]

it turns out a REGEX in the query directly solves this -

SELECT word FROM words WHERE word REGEXP '^[drea]+$'

and if you throw in a length check, it will do your second assignment -

SELECT word FROM words WHERE word REGEXP '^[drea]+$' AND CHAR_LENGTH(word) = 4

Edited June 13, 2013 by mac_gyver

[jbradley04]

Well, that it is definitely the most progress I have made for speed! The problem is when I enter

 

abcdhlkd

 

blackball comes as a result.  This cannot be though because there is only one l

 

Any thoughts?

[jbradley04]

Sorry, I posted the above in the wrong spot...  Although it does apply to my problem too!  

Any answers on how to limit it to exactly the characters entered?

[mac_gyver]

if you didn't want duplicates, you should have mentioned that up front as the method to accomplish that is different. you cannot sneak up on a problem in programming as computers don't like sneaky programmers.

Edited June 14, 2013 by mac_gyver

[kicken]

Any thoughts?

After you query for possible matches, do a little post processing to ensure that letters are not used more than they are allowed. Create a function that given a word and list of letters will return true/false depending on if the word can be created with those letters. Then, as you loop the results, check each word:

function IsValidWord($word, $letters){
  //...Implement this function
  //...array_count_values and str_split may be helpful
}

$validWords=array();
while ($row=$query->fetch()){
   if (IsValidWord($row['word'], $searchLetters)){
      $validWords[] = $row;
   }
}

[mac_gyver]

i would use count_chars() with mode = 3 and test if the length of the original word is the same length as the string count_chars($word,3) returns.