Search with a null value, mysql

Question

Search with a null value, mysql

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm a beginner in programming and I'm trying to find something new, in this case, I have two selects fields that tell my values, but I'd like the search to 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 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");

Thanks to all who can help

php mysql mysqli

asked by anonymous 14.03.2018 / 13:38

2 answers

Vertical scrolling by sections (like a slide) Popup Preventing Macro from Moving Forward

score 0 · Answer 1

When writing selects with criteria in this way, I recommend that you use parentheses to make the conditions explicit, to make reading clearer.

I believe this is 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"

score 0 · Answer 2

Try this:

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 working with OR you must always be aware, otherwise the next ones will not work.