4

I have the following MySQL query:

SELECT title, description 
FROM some_table 
WHERE MATCH (title,description) AGAINST ('+denver (REGEXP "[[:<:]]colorado[s]*[[:>:]]")' IN BOOLEAN MODE);

the "regexp" here looks for a "complete word" colorado (with or without the ending "s").

I want to actually select only those rows that have ("denver") AND ("colorado" or "colorados"). But I cannot put a "+" for the REGEXP. I tried but got 0 results, although there are rows in the table that match the requirement.

Any ideas on how I can get the "+" to work within against using a REGEXP? I am constructing this from within a PHP script where "denver" and "colorado" are values of variables I use to construct the select statement.

My PHP/MySQL script would look somewhat like this:

SELECT title, description 
FROM some_table 
WHERE MATCH (title,description) AGAINST ('+$var1 (REGEXP "[[:<:]]$var2[s]*[[:>:]]")' IN BOOLEAN MODE);
Nick
  • 138,499
  • 22
  • 57
  • 95
Sameer
  • 401
  • 2
  • 6
  • 10

1 Answers1

3

I don't think it's possible to combine regular expressions and MATCH ... IN BOOLEAN MODE. You need to use the syntax for writing boolean expressions.

Try something like this:

SELECT title, description
FROM some_table
WHERE MATCH (title,description)
      AGAINST ('+denver +(colorado colorados)' IN BOOLEAN MODE);
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Thanks Mark. But that would also pick up rows with "denver abcolorado" or even "denver whatever 123colorado890". My requirement is pick only the complete word found in $var2 and its plural. I may need to construct that query in PHP probably. Please let me know if you have any other ideas? Thanks again. – Sameer May 24 '12 at 04:45
  • 1
    @Sameer: Are you sure? Have you tested it? See this fiddle: http://sqlfiddle.com/#!2/d971f/1 – Mark Byers May 24 '12 at 04:57
  • Amazing! Honestly, had not tried it thinking it would pick up the other rows too. I admit I have a lot more to learn! Thanks a bunch Mark! – Sameer May 24 '12 at 05:10