How to optimize SQL to filter results

Asked

Viewed 596 times

0

The filter may occur in the following scenarios:

  1. Without any input filled
  2. 1 (a) input filled
  3. Combinations of inputs, 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();
}
php sql encoding-style

  • No doubt about the way this query of yours will make it very difficult to maintain the code and it doesn’t have to be this complex.

  • Because they were negative on the question?

  • One thing I recommend is creating pagination using LIMIT ...,..., this because if it returns many results this will "lock". I think this answer should help http://answall.com/a/94890/3635

3 answers

4


Based on Paulo Santos' answer, you can use a single query to verify the value of the parameters.

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

A 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 is important to note that the passing of the parameters that PDO will do is a little different from what PHP normally 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 his logic.

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

$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);

  • Hello @jlHertel, thank you for your help and defense regarding negativity. I implemented the solution you proposed to me, but there is no data return.

  • Would you like to continue our debate on chat? http://chat.stackexchange.com/rooms/54343/como-optimizar-sql-para-filtrar-results

  • The thinking of @jlHertel is correct, but it reversed the logic of the comparison, first you check if it is null, then you check if the field is equal to that of the search, the reason is simple, this conditional section only executes one of the parts, preferably the first one, that is, if it is NULL it does not make the second comparison, because of the OR.

  • @Incremental order matters little, as the optimizer will remove unnecessary code before executing

  • @luccasrodrigo, I made the mistake of forgetting the passing of parameters that the driver does is different from the common PHP treatment. It is necessary to ensure that the variables are null. I will adjust the response accordingly

  • Hello @jlHertel, actually my variables are in the following format: $n_cad = isset($_GET['n_cad']) ? $_GET['n_cad'] : ($_POST['filtrar-n-cad'][0] != '' ? $_POST['filtrar-n-cad'][0] : $_POST['filtrar-nome'][1]); as I will do to ensure that the value is NULL for name=filtrar-n-cad[]?

  • And regardless of that, based on your solution, I have the following problem: http://answall.com/questions/186797/consulta-sql-utilizando-pdo-retornando-array-vazio?noredirect=1#comment386401_186797 @jlHertel

  • Hello @jlHertel, could you explain to me why cnpj1 and cnpj2, razao1 and razao2, etc.?

  • @luccasrodrigo, it was just a way to name the parameters. You can put the name you find most interesting

  • the point is that cnpj1 and cnpj2, razao1 and razao2, respectively have the same variable value in bind, this does not generate redundancy?

  • @luccasrodrigo, yes it is redundant. Unfortunately it is a limitation of PDO. It does not allow to have two parameters with the same name.

Show 6 more comments

2

So you don’t have this consultation explosion, one for each particular case, insert in your query the "if null" condition of the field, this will make you have only one 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 comes null, that is, it is not set in the field of your form, the field will be ignored in the query, if it comes other than null, the field is considered and filtered in the search.

Basically a conditional filtering. That way you only have to write a single consultation to be able to filter through any combination of fields that the user makes.

It is also worth saying, that it is good practice for you to use the bind_param to assemble your query, since it is possible to make an attack of SQL Injection the way you’re doing.

  • Hello @Paulosantos, when trying to adapt to my context I get the following error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=null OR n_cad=) AND (=null OR id=) AND (=null OR data_emissao >= ) AND (2017-02' at line 1

  • SQL: "SELECT * FROM nfe WHERE ($cliente=null OR cliente LIKE '%".$cliente."%') AND ($n_cad=null OR n_cad=$n_cad) AND ($n_nfe=null OR id=$n_nfe) AND ($dt_inicial=null OR data_emissao >= $dt_inicial) AND ($dt_final=null OR data_emissao <= $dt_final) ORDER BY id DESC LIMIT ".$nfes.",".$maximo;

  • My friend, your problem is simple. Your variables are not coming null, they are coming as empty strings. Note this excerpt from your "(=null OR issuance >= ) error". Just check if the variables are equal to "" (empty string), if positive equals the variable to NULL that your query will successfully compile. Either do this or use the "Bind Param" to escape the empty string for you.

1

Filtering by DB is not to be trusted. Even PHP filtering functions are not guaranteed.
Regular expressions adds a little more security and allows you to follow a logic, a pattern. Make the script responsive. It would be better if it were applied in all fields.
After all analyzed it is necessary to pass a:
htmlentities($dos_data fields, ENT_QUOTES, "UTF-8", false);
In everything would be nice.
It is good to have the habits of treating the forms in nail and testing all possible ways to avoid errors.
Don’t need to assemble a lot of SELECT guy, 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'];}}}
?>

It can be improved! There are always ways to improve!

  • Hello @Marcoscélio, thanks for the advice. However, I do not know if I expressed myself very well, but the question is the SQL itself, assuming that the data have already been processed...

  • @luccasrodrigo Look there! That’s what I understood!

  • Hello @Marcoscélio, thank you for your attention. Can you do in "one" line as Paulo indicated? Look at the comments in his reply...

  • @luccasrodrigo but doing it in that way will generate error because there is no null cnpj, or social reason null, because they are only null if no registration has been made yet. Computers and systems are dumb and try to find exactly what we ask for. If we set a field to null the system will simply try to find a null cnpj in the mysql table as it does not exist because in the register it was predicted ai does not return anything or error. You cannot leave a null accepted field type name, because the user might not want to say his name and this is unacceptable, or say the name or get out. OK.

  • but in this case the user can perform a filter with the combination of inputs, so it may or may not fill in CNPJ, name, date...

Browser other questions tagged php sql encoding-style

You are not signed in. Login or sign up in order to post.