CODEWITHSUNDEEP
phpmysqlregex
Fabian
Fabian

Reputation: 3495

MySQL search for part of regex

I have a MySql search that works fine with regexp. But it only finds full words.

E.g if I search for apples in the text "i really love apples" it matches. But if I search for apple I get no result.

This is what I've got so far:

$search = (ini_get('magic_quotes_gpc')) ? stripslashes($_GET['q']) : $_GET['q']; 
$search = mysql_real_escape_string($search); 
$search = strtoupper(preg_replace('/\s+/', '|', trim($_GET['q'])));

$regexp = "REGEXP '[[:<:]]($search)[[:>:]]'"; 
$query = "SELECT * FROM `food` WHERE UPPER(`fruits`) $regexp";

I'm really stuck and any help is greatly appreciated! Thank you!

Upvotes: 3

Views: 258

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

Yes, it's doing exactly what you ask, if you use the word-boundary patterns:

$regexp = "REGEXP '[[:<:]]($search)[[:>:]]'";

This means that if you search for apple it only matches if e is the end of the word. That's what the [[:>:]] pattern means.

You can extend the pattern a bit, to include extra alpha characters before the word bounary:

$regexp = "REGEXP '[[:<:]]($search)[[:alpha:]]*[[:>:]]'";

That would allow it to match apples, and also applesauce for example.

Upvotes: 3

Clive
Clive

Reputation: 36957

If you want to match a string anywhere in the field content you can just use a LIKE operator:

$query = "SELECT * FROM `food` WHERE UPPER(`fruits`) LIKE '%$search%'";

Depending on your table collation the strtoupper and UPPER statements might be redundant (generally if the collation has _ci at the end of it the comparison would be performed in a case insensitive manner).

Upvotes: 4

Related Questions