Trying to pass tokens to WHERE clause

Anon

This code almost does what I want, but it should pull out the bandnames and metaphone keys for all the token matches in a string, not just the first one. Test it at: http://wamex.org/contentsp.html

$tok=strtok ($find," ");
$found=metaphone($tok);
while ($tok)
{
print "Query='$tok'<hr>";
$whereclause="WHERE metakey LIKE '$found'";
$tok = strtok (" ");
}
$sql=mysql_query ("
SELECT bandname, metakey
FROM bands, metakeys $whereclause
AND (bands.bandid=metakeys.bandid)
");

while ($myrow=mysql_fetch_array($sql))
{
print "$myrow[0], $tok, $found<p>";
}

dstockto

Do you mean you want it to grab band names if the token matches anywhere in the band name? If so, wrap the token in %'s. ie

... WHERE metakey LIKE '%$found$%'...

Dave

Anon

the desired steps are:
1. tokenize the query string
2. convert each token to its metaphone key
3. match the metaphone key to (x)bandnames (There is a relational table set up that stores metaphone keys for each band, linked to the band id) BILL FOX BAND has 3 metaphone keys, each linked to the same bandid.

Now when I do a query string like "band dc"
I only get matches for the metaphone of band (BNT), not BNT AND TK (metaphone of dc).

dstockto

So what exactly are the metaphones then? Are the metaphones for BILL FOX BAND, "BILL", "FOX" and "BAND"? Or are metaphones keywords that are associated with each band?

Anon

metaphone is a php function that returns a string of letters that approximates the pronunciation of the string fed to it. it is to be used when you dont know how to spell a word (better than the dictionary, right?)

$word="music";
$key=metaphone($word);
print "$key";
//returns MSK

the metaphones for "BILL FOX BAND", after the string has been split into each word separately, are BL, FKS, BNT. I want to search for any band names that have any of these metaphone equivalents. (all the band names have been split up and given a metaphone for each word in the name, and stored in a separate table. a band with 3 words in the name will have three metaphones and 3 of the same band id.)

But this metaphone thing is only an added feature of what Im trying to do. I basically want to search for each word in the input string separately, not the whole string.
"dc" gives me hits. "band" gives me hits.
But "dc band" gives me nothing. Now I hope you can see. Thanks for checking back.

dstockto

Perhaps you could look into mysql's SOUNDEX function. I couldn't really tell what metaphone is doing, and it didn't make as much sense as soundex. Soundex does something similar to what metaphone seems it should do.

Dave