Select words based on letters

[sol01]

I would like to search a DB Dictionary words list based on letters. Like say I have:

apple
ball
balls
cake
gun
car
onion

and I submit the letters "laslb" I should get "balls" from my list. I shouldn't get the word "ball" either.

I am working on an educational word game, anyone care to point me in the right direction??

[nnichols]

You will need to use REGEXP.

If you have "laslb" you will need to do something like this

You will need to turn the entered word into an array -

array (l => 2, a => 1, s => 1, b => 1)

using something like -

$length = strlen($word);
$i = 0;
while ($i < $length) {
$word_array[] = substr($word, $i, 1);
$i++;
}

$word_array = array_count_values($word_array);

• so each letter is represented only once but you know the number of instances of each letter.

Then you will need to dynamically build the REGEXP so that you end up with something like this -

word REGEXP "l{2} a{1} s{1} b{1}"

This will only match "llasb" as it stands but it should be enough to get you started in the right direction. You will need to read up on REGEXP and figure out how to do it.

If I figure out the REGEXP I'll post again.

Sorry I can't help more 😉

[nnichols]

Try this -

$length = strlen($word);
$i = 0;
while ($i < $length) {
$word_array[] = substr($word, $i, 1);
$i++;
}

$word_array = array_count_values($word_array);
echo 'Word array is - ';
print_r ($word_array);

// build REGEXP query
$sql = 'SELECT word FROM dictionary WHERE LENGTH(word) = ' . $length;

foreach ($word_array as $letter => $n) {
if ($n == 1) {
	$sql .= " AND word REGEXP '$letter'";
	}
elseif ($n == 2) {
	$sql .= " AND word REGEXP '$letter.*$letter'";
	}
elseif ($n == 3) {
	$sql .= " AND word REGEXP '$letter.*$letter.*$letter'";
	}
}

I ran this code against a mini dictionary table and it works.

Let me know how you get on. 😉

[[deleted]]

Regexp can probably do it, but dictionaries are usually tens fo thousands of words, doing all that with a regexp will not be very quick, expecially not in mysql.

If you want to do this as a standard feature, I suggest you 'cheat'.

Add a new column and fill it with a sorted version of the word. So 'balls' woulc become 'ablls'. Then do the same with the letters you are searching for, so 'laslb' also becomes 'ablls'

Now the query is a simple matter of WHERE sorted_word = 'ablls'