mysql search exact word with $ in word

Question

I've been trying to find EXACT words and my research isn't helping me.

Solutions I've found:

$query .= "WHERE text REGEXP '[[:<:]]($word)[[:>:]]'";
// OR
$query .= "WHERE MATCH(text) AGAINST('$word') ";

but neither are returning my matches.

I'm searching for stock symbols in my db (eg $aapl). And using LIKE '%$word%' will return $bac if you search for $ba. Help is much appreciated.

Answer 1

Hi,

You will want to use MySql Full Text Search functions to accomplish what you describe above.

Enjoy!

Answer 2

MATCH AGAINST doesn't work exactly how you'd think it would.

This can be summarized by Casey Fulton as "...FULLTEXT searches only [return] anything if the number of results is less than 50% of the total table size..."

So instead, I'm searching for all the LIKEs and then filtering out exact words by a REGEX. I'm doing this to cut down on the process load for REGEX.

The following is my solution:

$likerows = "(SELECT * FROM `tweets` WHERE text LIKE '%$q%') AS likerows ";
$regexrows = "(SELECT * FROM $likerows WHERE text REGEXP('^.* $q .*$')) AS regexrows ";
$query = "SELECT * FROM $regexrows ";

I put spaces around the $q because I want to match a sentence-structured word (which is preceeded and followed by a space).

Also, I'm searching for "words" that start with $. This was an issue and here's what I did to solve that problem:

if(substr($q, 0, 1) == '$') $q = '\\\\' . $q; // you have to escape the backslash that escapes the $ -- it's nuts.

Best of luck.