0

I have the following error:

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected '-'

This is the piece of code where it happens:

        $stmt = $db->prepare(
            "
                SELECT
                            product_id,
                            name                           
                FROM
                            products
                WHERE
                            MATCH(name) AGAINST(:name IN BOOLEAN MODE)

            "
        );  

        $stmt->bindParam(':name',$name,PDO::PARAM_STR);

        $stmt->execute();

The problem appears when name has a dash (-) in it. When the name is 'normal' (without a dash), it works.

I tried the solution in this question (PDO and UTF-8 Special characters in PHP / MySQL?) but that does not work.

Anyone an idea?

Thanks in advance!

Edit

@lonesomeday answer works, but I found another solution. By changing 'IN BOOLEAN MODE' to 'IN NATURAL LANGUAGE MODE', the error disappeared.

Sam Leurs
  • 480
  • 4
  • 20
  • 2
    If a database table/column name has a `-` then enclose the name in backticks ( `\`` ) – Nigel Ren Aug 27 '18 at 14:26
  • 1
    Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) – Nigel Ren Aug 27 '18 at 14:28
  • What happens when you use MATCH(name) AGAINST(\`:name\` IN BOOLEAN MODE) – AmmoPT Aug 27 '18 at 14:28
  • This is definitely **not** a duplicate of that question, and backticks are not the solution. – lonesomeday Aug 27 '18 at 14:30
  • 1
    When *what exactly* has a dash in it…?! The value of `$name`? Or what else? – deceze Aug 27 '18 at 14:30
  • Just to clarify, is it the column name that has the dash, or is it the variable, with something like `$name = 'this-name'`? – aynber Aug 27 '18 at 14:30
  • @deceze It's obviously `$name`, both from the context and from the error. – lonesomeday Aug 27 '18 at 14:31
  • The answer to this question is that `IN BOOLEAN MODE` gives a special meaning to several characters, including `-`. I have an answer written, but can't submit it until the question is reopened. – lonesomeday Aug 27 '18 at 14:32
  • @lonesomeday thank you, how can I reopen this question? Untill then, I will check how to fix this myself given that 'IN BOOLEAN MODE' gives a special meaning to several characters like you say. – Sam Leurs Aug 27 '18 at 14:36
  • @yesterday It's been reopened and I've answered it. If you refresh the page, you'll see my answer. – lonesomeday Aug 27 '18 at 14:36

1 Answers1

4

The problem is the IN BOOLEAN MODE full-text search. The character - in the search string (along with several others) has a special meaning (excluding a word from the search). Your error, I think, comes from having a - character with a word immediately preceding, with no intervening space.

In any case, the - won't be part of the search in any case, like the other operators, so the simplest approach is to strip it, along with the other operators, out.

$name = str_replace(['-', '+', '<', '>', '(', ')', '~', '*', '\'', '"'], ' ', $name);

Only include those characters if you genuinely want their specific meaning in this context.

lonesomeday
  • 233,373
  • 50
  • 316
  • 318
  • Hi, thank you for your answer. It works! But I also found another solution: changing 'IN BOOLEAN MODE' to 'IN NATURAL LANGUAGE MODE' seems to fix it. What I want to achieve is to fetch "related products". So products with "the same" name (not exactly the same name, but the best matches). But I'm not sure to use 'IN BOOLEAN MODE' (+ your solution for the fix) or 'IN NATURAL LANGUAGE MODE' for this? – Sam Leurs Aug 27 '18 at 14:38
  • @yesterday Sounds like `NATURAL LANGUAGE MODE` is more appropriate. You should write it up as an answer to this question and mark it as the accepted answer! – lonesomeday Aug 27 '18 at 14:45