How to optimize SQL to filter results

0

The filter may occur in the following scenarios:

  • No% of% completed
  • 1 (one) input filled
  • Combinations of input , including all
  • SQL to be optimized:

    $cnpj           = isset($_GET['cnpj']) ? $_GET['cnpj'] : $_POST['cnpj'];
    $razao_social   = isset($_GET['razao']) ? $_GET['razao'] : $_POST['razao'];
    $cod            = isset($_GET['cod']) ? $_GET['cod'] : $_POST['cod'];
    $dt_inicial     = isset($_GET['dt_inicial']) ? $_GET['dt_inicial'] :$_POST['dt-inicial'];
    $dt_final       = isset($_GET['dt_final']) ? $_GET['dt_final'] : $_POST['dt-final'];
    
    if ($cnpj != '' && $razao_social != '' && $cod != '' && $dt_inicial != '' && $dt_final != '') {
        $sql = 'SELECT * FROM nfe WHERE cnpj="$cnpj" AND (razao_social LIKE "%$razao_social%") AND id="$cod" AND data>="$dt_inicial" AND data<="$dt_final"';
    } else {
        if ($cnpj != '' && $razao_social != '' && $cod != '' && $dt_inicial != '') {
            $sql = 'SELECT * FROM nfe WHERE cnpj="$cnpj" AND (razao_social LIKE "%$razao_social%") AND id="$cod" AND data>="$dt_inicial"';
        } else {
            if ($cnpj != '' && $razao_social != '' && $cod != '') {
                $sql = 'SELECT * FROM nfe WHERE cnpj="$cnpj" AND (razao_social LIKE "%$razao_social%") AND id="$cod"';
            } else {
                if ($cnpj != '' && $razao_social != '') {
                    $sql = 'SELECT * FROM nfe WHERE cnpj="$cnpj" AND (razao_social LIKE "%$razao_social%"';
                } else {
                    if ($cnpj != '') {
                        $sql = 'SELECT * FROM nfe WHERE cnpj="$cnpj"';
                    } else {
                        if ($cnpj != '' || $razao_social != '' || $cod != '' || $dt_inicial != '' || $dt_final != '') {
                            $sql = 'SELECT * FROM nfe WHERE cnpj="$cnpj" OR (razao_social LIKE "%$razao_social%") OR id="$cod" OR data>="$dt_inicial" OR data<="$dt_final"';
                        } else {
                            $sql = 'SELECT * FROM produtos';
                        }
                    }
                }
            }
        }
    }
    
    try {
        $query = $conecta->prepare($sql);
        $query->execute();
        $resultado = $query->fetchAll(PDO::FETCH_ASSOC);
    }catch (PDOexception $erro) {
        echo 'Erro ao selecionar: '.$erro->getMessage();
    }
    
        
    asked by anonymous 25.02.2017 / 01:24

    3 answers

    4

    Based on the answer from Paulo Santos, you can use a single query checking the value of the parameters.

    It is also worth remembering that we should not concatenate the variables directly, but rather use preparedStatement .

    One possible solution to your problem:

    $sql = 'SELECT * FROM nfe WHERE (cnpj = :cnpj1 OR :cnpj2 IS NULL)';
    $sql .= ' AND (razao_social LIKE :razao1 OR :razao2 IS NULL)';
    $sql .= ' AND (id = :id1 OR :id2 IS NULL)';
    $sql .= ' AND (data >= :dataini1 OR :dataini2 IS NULL)';
    $sql .= ' AND (data <= :datafim1 OR :datafim2 IS NULL)';
    
    try {
        $query = $conecta->prepare($sql);
        $query->bindValue(':cnpj1', $cnpj);
        $query->bindValue(':cnpj2', $cnpj);
        $query->bindValue(':razao1', $razao_social);
        $query->bindValue(':razao2', $razao_social);
        $query->bindValue(':id1', $cod);
        $query->bindValue(':id2', $cod);
        $query->bindValue(':dataini1', $dt_inicial);
        $query->bindValue(':dataini2', $dt_inicial);
        $query->bindValue(':datafim1', $dt_final);
        $query->bindValue(':datafim2', $dt_final);
    
        $query->execute();
        $resultado = $query->fetchAll(PDO::FETCH_ASSOC);
    }catch (PDOexception $erro) {
        echo 'Erro ao selecionar: '.$erro->getMessage();
    }
    

    It's important to note that the passing of the parameters that the PDO will do is a bit different from what PHP usually does. Let's say the variable $cnpj has an empty string as value. This value will be passed to the query as a string instead of null , breaking its logic.

    To ensure that there are no problems, you can set null to the variables that are actually null, like this:

    $cnpj           = isset($_GET['cnpj'])       ? $_GET['cnpj']       : (isset($_POST['cnpj'])       ? $_POST['cnpj']       : null);
    $razao_social   = isset($_GET['razao'])      ? $_GET['razao']      : (isset($_POST['razao'])      ? $_POST['razao']      : null);
    $cod            = isset($_GET['cod'])        ? $_GET['cod']        : (isset($_POST['cod'])        ? $_POST['cod']        : null);
    $dt_inicial     = isset($_GET['dt_inicial']) ? $_GET['dt_inicial'] : (isset($_POST['dt-inicial']) ? $_POST['dt-inicial'] : null);
    $dt_final       = isset($_GET['dt_final'])   ? $_GET['dt_final']   : (isset($_POST['dt-final'])   ? $_POST['dt-final']   : null);
    
        
    25.02.2017 / 19:32
    2

    If you do not have this query explosion , one for each particular case, insert your query into the "if null" condition of the field, that you have only a select .

    $nome = $_POST['nome'];
    $sexo = $_POST['sexo'];
    $etnia = $_POST['etnia'];
    
    'SELECT * FROM Pessoa WHERE ($nome = null OR nome like "%$nome%") AND ($sexo = null or sexo = $sexo) AND ($etnia = null OR etnia = $etnia)'
    

    This works as follows, if the variable is null, ie it is not set in the field of your form, the field will be ignored in the query, if it comes different from null, the field is considered and filtered in the search .

    Basically a conditional filtering. That way you only have to type a single query to be able to filter by any combination of fields that the user does.

    It's also a good practice to use bind_param to mount your query, as it is possible to make a SQL Injection attack the way you are doing.

        
    25.02.2017 / 04:31
    1

    Do not rely on DB filtering. Even the PHP filtering functions are not guaranteed.
    Regular Expressions adds a bit more security and allows you to follow a logic, a standard. Make the script responsive. It would be better if it were applied in all fields.
    After everything analyzed it is necessary to pass a:
    htmlentities ($ field_data, ENT_QUOTES, "UTF-8", false) It's good to have the habits of treating the forms on the nail and test in every way possible to avoid errors.
    Do not need to mount a lot of expensive SELECT, make the system mount the select for you, there goes a draft:

    <?php
    //Se todos os campos forem nulos a query vai para a tabela produto
    if(($cnpj == null) AND ($razao_social == null) AND ($cod == null) AND ($dt_inicial == null) AND ($dt_final == null)){
    $sql = 'SELECT * FROM produtos';
    } else {
    //Se algo passar pela validação a query vai para tabela notafiscal
    $condicao = " WHERE ";
    //Incluir o CNPJ se for diferente de nulo
    if((isset($cnpj) == true) and ($cnpj != null)){$condicao = " cnpj='$cnpj'";}
    //Incluir a razão social se não for nula
    if((isset($razao_social) == true) and ($razao_social != null)){$condicao .= " AND razao_social LIKE '%".$razao_social."%'";}
    //Incluir o codigo se não for nulo
    if((isset($cod) == true) and ($cod != null)){$condicao .= " AND id = '$cod'";}
    //Incluir a data inicial se não for nula
    if((isset($dt_inicial) == true) and ($dt_inicial != null)){$condicao .= " AND data >= '$dt_inicial'";}
    //Incluir a data final se não for nula
    if((isset($dt_final) == true) and ($dt_final != null)){ $condicao .= " AND data <= '$dt_final'";}
    //A sql a ser a fazer query no db
    $sql = "SELECT * FROM nfe $condicao";
    $query = $PDO->prepare($sql);
    $query->execute();
    $count = $query->rowCount();
    if($count > 0){for($i = 0; $row = $query->fetch(); $i++){
                   //imprimindo os registros
                       echo $row['razao_social'];}}}
    ?>
    

    Can be improved! There are always ways to improve!

        
    25.02.2017 / 02:36