I'm having an issue searching the data in my database. I can search on individual keywords just fine, and I can search an "exact string" using quotes, but it doesn't work if I combine a keyword and an exact string. If i search for something like "apartment complex" burglar, I would expect it to narrow the results to only those with both "apartment complex" and "burglar" in them (like in a Google search), yet it gives me records with "apartment complex" OR "burglar" and expands the results.

The query it runs looks like this...

SELECT * FROM risks WHERE MATCH (description,taxstate,county,city) AGAINST ('"apartment complex" burglar')

What am I doing wrong?

Thank you!

Jim

    The double-quotes aren't meaningful to MySQL without the boolean modifier. I think what you want is a boolean-mode search.

    A valid expression for the results you want would be:

    SELECT * FROM risks WHERE MATCH (description,taxstate,county,city) 
AGAINST ('"apartment complex" burglar' IN BOOLEAN MODE)

      Thanks for the quick reply. I'm inserting this via PHP and am using this statement which is a part of a larger search query statement:

      $q = $q . "MATCH (description,taxstate,county,city) AGAINST ('" . stripslashes (str_replace (""", "\"", ($_POST['search']))) . "') AND ";

      Do you have a recommendation on how to insert this via PHP in the format you recommend?

      Thanks!

        I see where you updated the post. I added the IN BOOLEAN MODE at the end and I'm getting the same result. Do you have any other tricks?

          It should work. Here's an excerpt from the manual:

          '"some words"'

          • Find rows that contain the exact phrase “some words” (for example, rows that contain “some words of wisdom” but not “some noise words&#8221😉. Note that the “"” characters that enclose the phrase are operator characters that delimit the phrase. They are not the quotes that enclose the search string itself.

          Could you print the resulting query and copy it here, please? (Just to confirm the actual syntax.)

            Certainly. I really appreciate all of your help! Here is the query:

            SELECT * FROM risks WHERE MATCH (description,taxstate,county,city) AGAINST ('"apartment complex" gainesville' IN BOOLEAN MODE) ORDER BY effective ASC

            This query finds results that have either the phrase "apartment complex" OR the word gainesville. It does not do what it like it to do which is to just show records with both "apartment complex" AND gainesville.

            Thanks again!

            Jim

              You need to add a plus sign to do a logical AND - otherwise a result just gains a higher relevancy if it contains "gainesville". Somewhere in the manual pages it explains how to order by relevancy, if you are interested.

              SELECT * FROM risks WHERE MATCH (description,taxstate,county,city)
  AGAINST ('"apartment complex" +gainesville' IN BOOLEAN MODE)
  ORDER BY effective ASC

                Thanks for your help! It's probably the relevancy that I need to understand better.

                  Write a Reply...