Problems with Mysql query

Asked

Viewed 49 times

2

<form method="POST" class="w-100" action="pesquisa_diaria.php">
   
    <div class="card-body">
        <div class="row">
           
            <div class="col-sm-3"> 
                <label>Tipo de Venda</label>
                   <select class="custom-select" name="tipo_vendas" required>
                     <option value=''>Selecione...</option>
                     <option value='caixa'>Caixa</option>
                     <option value='delivery'>Delivery</option>
                     <option value='null'>Todos</option>
                 </select>
            </div>
            
            <div class="col-sm-3"> 
                <label>Tipo de Pagamento</label>
                   <select class="custom-select" name="tipo_pagamento" required>
                     <option value=''>Selecione...</option>
                     <option value='cartao_credito'>Cartão de Credito</option>
                     <option value='cartao_debito'>Cartão de Debito</option>
                     <option value='dinheiro'>Dinheiro</option>
                     <option value='null'>Todos</option>
                 </select>
            </div>
            
            <div class="col-sm-3"> 
                 <label>Data</label>
                 <input id="datepicker" name="data"  width="276" required/>
            </div>

            <div class="col-12 text-right align-self-end">
                </br><button type="submit" class="btn btn-success">Pesquisar</button>
            </div>
        </div>
    </div>
</form>

I have a form that does a query in the database, but I’m having trouble with the "All" options. When I select this option, the query does not bring any result.

SELECT * FROM vendas WHERE DATE(date_created) = '2018-12-12'
AND tipo_venda = 'null' AND tipo = 'null' ORDER BY 'id'
php mysql sql

1 answer

1


I guess when you talk todos Voce wants it to select any value, if that’s the case you need to do something like:

$WHERE = array();
if( $_GET['tipo_pagamento'] != "null" )  // so coloca se nao for null
    array_push($WHERE,"tipo='{$_GET['tipo_pagamento']}'");

if( $_GET['tipo_vendas'] != "null" ) // so coloca se nao for null
    array_push($WHERE,"tipo_venda ='{$_GET['tipo_pagamento']}'");

$WHERE = count($WHERE) ? "AND ".join($WHERE," AND ") : "";

$qry = "SELECT * FROM vendas WHERE DATE(date_created) = '2018-12-12' {$WHERE} ORDER BY 'id'"

print $qry;

The above code hasn’t been tested, but that’s the idea

  • But I also think that my query is wrong because even playing the select right in the bank the results do not come when and null,

  • If you want to check if the value is NULL , have to use IS NULL - example tipo_venda IS NULL or tipo_venda IS NOT NULL MYSQL-doc

  • 1

    Icaro’s answer is correct. Your query does not come because you are comparing 'null' (varchar) to NULL (actual value). If your table has NULL information the query should compare "WHERE FIELD IS NULL" (example) and not "WHERE FIELD = 'NULL'" (interpreting the value to be compared as varchar). But if you’re just wanting to bring everyone in without specifying which one, the answer is correct.

  • I understand, but if in my table there is no null in the fields, how do I bring everything

  • Don’t put in the where. Example I want all tipo_venda NULL or NOT NULL: SELECT * FROM vendas WHERE DATE(date_created) = '2018-12-12' AND tipo_pagamento='meu tipo de pagamento' ORDER BY 'id'

  • note that the query does not have tipo_venda, That’s because I want any tipo_venda

  • now if you want tipo_venda with values not null, ai Voce tipo_venda NOT IS NULL

  • vc says do so SELECT * FROM sales WHERE DATE(date_created) = '2018-12-12' AND tipo_venda IS NOT NULL AND type IS NOT NULL ORDER BY 'id'

  • but if I choose another option would not make mistake

  • this query will return only the records with some (not null) value in tipo_venda and tipo

  • if Voce does this other query SELECT * FROM vendas WHERE DATE(date_created) = '2018-12-12' ORDER BY 'id' he will pick up all independent records of tipo_venda and tipo, or they may have valid value or not

  • Let’s go continue this discussion in chat.

Show 7 more comments

Browser other questions tagged php mysql sql

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