How to make an SQL query with some blank criteria?

Asked

Viewed 530 times

2

I am creating a filter for a database search in a PHP application and obviously I have numerous criteria, but, I have only been able to work with one criterion.

Code:

select codigo, despesa, data, estabelecimento, valor from financeiro where codigo = 603;

So it obviously works perfectly.

However, on a page I need to leave blank some criteria, as in the example.

In the hypothesis that the user left the establishment field empty, however, it does not return any value, when it should show the same as the first query. 

            <?php 
    include "conexao_bd.inc";

    $codigo = $_POST["codigo"];
    $data = $_POST["data"];
    $hora = $_POST["hora"];
    $despesa = $_POST["despesa"];
    $valor = $_POST["valor"];
    $modo = $_POST["modo"];
    $observacao = $_POST["observacao"];
    $estabelecimento = $_POST["estabelecimento"];
    $genero = $_POST["genero"];
    $tipo = $_POST["tipo"]; 


    $resultado = mysqli_query($link, "select * from financeiro where codigo like '$codigo' and data like '$data' and hora like '$hora' and despesa like '$despesa' and valor like '$valor' and modo like '$modo' and observacao like '$observacao' and estabelecimento like '$estabelecimento' and  genero like '$genero' and tipo = '$tipo'");

    //Escrevendo a tabela.
    echo "<table border=1>";
    echo "<tr><td><strong>Código</strong></td><td><strong>Data</strong></td><td><strong>Hora</strong></td><td><strong>Despesa</strong></td><td><strong>Valor</strong></td><td><strong>Modo</strong></td><td><strong>Observação</strong></td><td><strong>Estabelecimento</strong></td><td><strong>Gênero</strong></td><td><strong>Tipo</strong></td></tr>"; 


    while($linha = mysqli_fetch_assoc($resultado)) {

        echo "<tr>
        <td>".$linha['codigo']."</td>
        <td>".$linha['data']."</td>
        <td>".$linha['hora']."</td>
        <td>".$linha['despesa']."</td>
        <td>".$linha['valor']."</td>
        <td>".$linha['modo']."</td>
        <td>".$linha['observacao']."</td>
        <td>".$linha['estabelecimento']."</td>
        <td>".$linha['genero']."</td>
        <td>".$linha['tipo']."</td>
        </tr>";      

    }

    echo "</table>";

?>

Could someone give me a light? Grateful.

php mysql sql query

  • You said the second "should show the same as the first consultation". What do you mean, the first appointment is empty establishments, is that it? How many records do you have in the bank today whose code is 603 and also the establishment field is empty?

  • So it’s because I’m using this with PHP, so if the user doesn’t put parameters, they need to go blank, right? But the parameter needs to be there so that the user can choose to type. .

  • Then your query makes no sense. Omit the and estabelecimento='' whenever the field is blank, it becomes simpler. You only ask for the blank 603 records, it will only come if you have the 2 situations. Better you [Dit] your question, put the right tags then, and explain your problem right, otherwise staff waste time answering things that will not suit you, and your problem will continue without decent solution. I would suggest you learn PHP and SQL with simpler things, once you understand the basics goes to more complicated things.

  • Right, I didn’t express myself properly, Bacco. I did an issue trying to explain better.

  • My problem is passing parameter when the user does not use all parameters, but only a few, or only one.

  • The field may be with default value null. See the @Skywalker response

  • And how I would change that?

  • In case, how could I leave in passing the parameter for it to simply omit null.

  • @Eduardoribeiro from what I understand you should not pass the column and estabelecimento = '';. Add in the clause only if the user fills the filter.

  • in all filters I use LIKE. Unless the value comes from algm combobox, if it is typed put LIKE that solves your problem.

  • People, in PHP how I would omit the and establishment = '' and the other parameters if the user does not fill it?

  • @Gustavotinoco, I tried to use the like and it didn’t work.

  • I updated the code, my problem is with passing the parameter when searching the database.

  • @Gustavotinoco, correcting, the like works when I run the SQL statement in phpMyAdmin, but when I do it by mysqli_query it brings nothing.

  • Good Eduardo Ribeiro, my experience is limited to the use of functions mysqli_query because until today I only worked with the methods PDO Oriented database object, if you are interested in my project is here: https://github.com/gustinoco/Sislabweb (I did the internship at Embrapa) the methods of handling the bank are in the /entity folder/

  • 1

    Note that the answer indicated in the closure solves for any comparator, be in, =, LIKE. What it does is leave out the comparison if the parameter is empty, and you can apply it to the parameters you want (which is exactly what I suggested in the 2nd comment, omit the comparison and the field if the value is empty).

  • 1

    Guys, thank you, I’ve been concatenating the SQL statement as suggested by Skywalker and gave it right. Thank you all for your help.

Show 12 more comments

2 answers

1


Try

estabelecimento is NULL

SOURCE: http://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html

UPDATE

In your case, create sql concatenated.

$sql ="select.... where codigo=$codigo ";
if(!empty($estabelecimento))
  $sql.=" AND/OR estabelecimento=$estabelecimento ";
if(!empty($campoX))
  $sql.=" AND/OR campo_x=$campoX ";

Never insert variable directly into the same sql was done, totally insecure, always use Prepared statements to add parameters within sql.

  • 1

    You gave it right, buddy, your tip was very valuable. You’re the guy, thank you.

0

One way to achieve this is: 

select codigo, despesa, data, estabelecimento, valor from financeiro where codigo = 603 and LTRIM(RTRIM(estabelecimento)) = ''

So if you have spaces you will also be considered empty.

Browser other questions tagged php mysql sql query

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