How can I return MySQL query records using regex?

Question

I have records in my database that are executed using user input, and initially this was enough to find the correct records.

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

It worked well and well, but then it all started as if the search for “red” got plants with red characters in series in the Flower field, and not just the whole word “red”.

I was asked to simply put a space on each side of the user input, but I know that this will not work if the word is the first word in the field or the last. I thought to myself, I have to use regex! To search for a word where it has a word boundary on both sides.

Unfortunately, I have never used regular expressions in a database before. How do you create a query to search for db with regular expression? Hope this is so simple:

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

Answer 1

Yes, that’s pretty easy.

MySQL Reference Guide - Regular Expressions

The boundary sequences of words in MySQL regular expressions are [[:<:]] and [[:>:]] , so you get something like:

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