How to do a SQL query with some blank criteria? [duplicate]

Navigation
2

I'm creating a filter for a database search in a PHP application and obviously I have a lot of criteria, but I've only managed to work with only 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 some criteria blank, as in the example.

On the assumption 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? Thankful.

    
asked by anonymous 19.07.2016 / 04:56

2 answers

1

Try 

estabelecimento is NULL

SOURCE: link

UPDATE

In your case, create the sql in a concatenated way. 

$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 sql as it was done, totally insecure, always use prepared statements to add parameters within sql.

    
19.07.2016 / 06:25
0

One way to do this is: 

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

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

    
19.07.2016 / 12:57
Open several windows of the same application without accumulating icons in the taskbar Mounting a 'frame' in html from an array in php