0

This is my regular code and it works fine:

$sql = 'SELECT '
    . '* '
  . 'FROM '
    . 'item '
  . 'WHERE MATCH '
    . '(title) '
  . 'AGAINST '
    . '(:search IN NATURAL LANGUAGE MODE) '
  . 'OR MATCH '
    . '(description) '
  . 'AGAINST '
    . '(:search IN NATURAL LANGUAGE MODE) '
  . 'ORDER BY '
    . 'id ASC';

I want to compare the field "status" to the string "public" because I need only the "public" entrys, I tried something like this:

$sql = 'SELECT '
    . '* '
  . 'FROM '
    . 'item '
  . 'WHERE MATCH '
    . '(title) '
  . 'AGAINST '
    . '(:search IN NATURAL LANGUAGE MODE) '
  . 'OR MATCH '
    . '(description) '
  . 'AGAINST '
    . '(:search IN NATURAL LANGUAGE MODE) '
  . 'WHERE '
    . 'status = "public" '
  . 'ORDER BY '
    . 'id ASC';

But with the last "WHERE" the result is nothing I expected.

Is there any solution?

Mario
  • 3
  • 3

2 Answers2

1

You have added 2 WHERE statements, thats not allowed and when you use OR it is best to place brackets round the OR condition to ensure the correct result

$sql = 'SELECT *
        FROM item 
        WHERE status = "public"
        AND (
                MATCH (title) AGAINST (:search IN NATURAL LANGUAGE MODE) 
             OR MATCH (description) AGAINST (:search IN NATURAL LANGUAGE MODE) 
            )
        ORDER BY id ASC';
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
1

There are couple of errors in your query,

  1. WHERE clause is added twice

  2. Conditions in OR should be enclosed in round brackets '()'

    SELECT *
FROM item
WHERE ( 
        MATCH (title) AGAINST (:search IN NATURAL LANGUAGE MODE)
        OR MATCH (description) AGAINST (:search IN NATURAL LANGUAGE MODE)
)
AND status = "public"
ORDER BY
id ASC

Also, using SELECT * is harmful. You can read the details about it here

Samir Selia
  • 7,007
  • 2
  • 11
  • 30