How can I return records with a MySQL query using a regular expression?

Question

I have records in my database that are searched with user input, and initially, this was sufficient for finding the correct records.

"SELECT id FROM plants WHERE Flower LIKE '%" . $sanitizedUserInput . "%'"

This was working all well and good, but then things started to happen like, searching 'red' was getting plants with the characters 'red' in sequence in their Flower field, and not simply the whole word 'red'.

I was suggested to simply put a space either side of the user's input, but I know this will fail where the word is the first word in the field, or the last. I thought to myself, I should use a regular expression! To search the word where it has a word boundary either side.

Unfortunately, I've never used regexs in a database before. How do you construct a query to search a db with a regex? Hopefully, it's as easy as this:

"SELECT id FROM plants WHERE Flower REGEX `/\b" . $sanitizedUserInput . " \b/`"

Answer 1

Yes, it's pretty much that easy.

MySQL Reference Manual - Regular Expressions

The word boundary sequences in MySQL regular expressions are [[:<:]] and [[:>:]] so you're going to end up with something like:

$query = "SELECT id ".
            "FROM plants ".
            "WHERE Flower REGEXP '[[:<:]]".$sanitizedUserInput."[[:>:]]'";