Search with null value, mysql

Question

I’m a beginner in programming and I’m trying to find something new, in this case, I have two fields that inform my research values, but I wish the research could be done even if a field is null

This is the request code

if($_SERVER['REQUEST_METHOD']=='POST'){
            $id_categoria = $_POST['id_categoria'];
            $id_sub_categoria = $_POST['id_sub_categoria'];
            $pagina = 1;
            $_SESSION['id_categoria'] = $id_categoria;
            $_SESSION['id_sub_categoria'] = $id_sub_categoria;

And in this case this is query executed

            $result_empresas = ("SELECT * FROM clientes 
                                WHERE categoria_id = '$id_categoria' AND subcategoria_id = '$id_sub_categoria' 
                                OR categoria_id = '$id_categoria' AND subcategoria_id = '$id_sub_categoria' IS NULL
                                OR subcategoria_id = '$id_sub_categoria' 
                                LIMIT $inicio, $qnt_result_pg");

Thank you to all who can help

Answer 1

Try it this way:

SELECT * FROM clientes 
WHERE (
(categoria_id = '$id_categoria' AND subcategoria_id = '$id_sub_categoria')
OR (categoria_id = '$id_categoria' AND subcategoria_id = '$id_sub_categoria' IS NULL)
OR (subcategoria_id = '$id_sub_categoria')
) 
LIMIT $inicio, $qnt_result_pg

Or:

SELECT * FROM clientes 
WHERE 
(
  (categoria_id = '$id_categoria' OR categoria_id IS NULL)
OR 
  (subcategoria_id = '$id_sub_categoria' OR subcategoria_id IS NULL)
) 
LIMIT $inicio, $qnt_result_pg

When to work with OR must always be attentive, if not the next ones hinder the previous ones.

Answer 2

When writing selects with criteria this way, I recommend using parentheses to make explicit the conditions, to make a reading clearer.

I believe that’s what you need:

SELECT * 
FROM clientes 
WHERE ( categoria_id = '$id_categoria' OR '$id_categoria' IS NULL ) AND 
    ( subcategoria_id = '$id_sub_categoria' OR '$id_sub_categoria' IS NULL ) AND
    ('$id_sub_categoria' IS NOT NULL OR '$id_categoria' IS NOT NULL)
LIMIT $inicio, $qnt_result_pg"