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

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/`"

From stackoverflow

• 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."[[:>:]]'";
  

  alex : For some reason I find the mySQL docs confusing...

  alex : Thanks for that, Chad. +1

  Chad Birch : Really? I'm a pretty big fan of the mysql docs overall. The search function isn't great, but most pages are usually written pretty well, once I find the right one.

  alex : Yeah, I'm perhaps just a bit odd... the PHP or jQuery docs on the other hand... love 'em