MySQL如果值为null则不搜索

Question

I have the following query:

SELECT nfc_film.title, nfc_film.film_id, nfc_film.description, nfc_film.release_year, nfc_film.rating, nfc_film.last_update, nfc_category.name 
  FROM nfc_film
  JOIN nfc_film_category
    ON nfc_film.film_id = nfc_film_category.film_id
  JOIN nfc_category
    ON nfc_film_category.category_id = nfc_film_category.category_id
 WHERE
       nfc_film.title LIKE :searchterm
   AND
       nfc_category.name = :category
 LIMIT 10
OFFSET :page

I'm aware of the IFNULL() function. But say if :category was either empty or null, how would i just make the query as if that condition wasn't in there.

So like If :category is null then don't search in nfc_category.name

Answer 1

You can just change your WHERE conditions to include empty/null tests for category. If it is empty or NULL, the result of the nfc_category.name = :category will be irrelevant (and should be optimised away). Note the need for parentheses to avoid operator precedence issues.

(:category = '' OR :category IS NULL OR nfc_category.name = :category)

Dependent on your PDO::ATTR_EMULATE_PREPARES setting, you may need to use another parameter name e.g. :category0, :category1 for the second and third usage of the parameter.