How to do two checks in select

Question

I have 3 selects in my code where:

• If the user enters the start date and the end date will show the data between them.
• If the user enters the invoice will show the data with this note.
• If the user selects the peripheral will show all the data with this registered name.

Now my question is: How to make the select stay:

• If select initial date /final and peripheral date, be shown the peripherals (with the name given) registered within the dates defined?
• If select invoice and ancillary note just show the selected peripheral of that note!

Selects code:

if($w_op == "P"){ //P é o periférico
    $w_query="where sai_tb_peri.seq_peri = '$w_cb_peri'";   
}
else
if($w_op == "N"){ //N é a nota fiscal
    $w_query="where sai_cad_nf.num_nf = '$w_tx_nota'";
}
else{ //Caso for nenhum é as datas
$w_query="where sai_cad_nf.dt_nota_fisc between '$w_tx_dt_inic' and '$w_tx_dt_fina'";
}

$w_querybusca="SELECT * FROM sai_cad_cara_peri
                    INNER JOIN sai_tb_peri on sai_cad_cara_peri.fk_seq_peri = sai_tb_peri.seq_peri
                    INNER JOIN sai_cad_nf ON sai_cad_cara_peri.fk_seq_nf = sai_cad_nf.seq_nf $w_query;";

Answer 1

I wouldn’t put those else's initially, to help already create the variable $w_query with a value 'Where 1=1' (Pog, but it helps a lot) and would be something like this, I didn’t test and I don’t know if it will work!!!

$w_query = "where 1=1"

if($w_op == "P"){ //P é o periférico
    $w_query .= " AND sai_tb_peri.seq_peri = '$w_cb_peri'";
}

if($w_op == "N"){ //N é a nota fiscal
    $w_query .= " AND sai_cad_nf.num_nf = '$w_tx_nota'";
}

$w_query .= " AND sai_cad_nf.dt_nota_fisc between '$w_tx_dt_inic' and '$w_tx_dt_fina'";


$w_querybusca="SELECT * FROM sai_cad_cara_peri
                    INNER JOIN sai_tb_peri on sai_cad_cara_peri.fk_seq_peri = sai_tb_peri.seq_peri
                    INNER JOIN sai_cad_nf ON sai_cad_cara_peri.fk_seq_nf = sai_cad_nf.seq_nf";
$w_querybusca.= $w_query;

Answer 2

Rewriting your code, the use switch is more advisable than the use of if..else, at any time you can replace your if for switch, o make.

switch ($w_op) 
{
case "P":
    $w_where = "where sai_tb_peri.seq_peri = '$w_cb_peri'";   
    break;
case "N":
    $w_where = "where sai_cad_nf.num_nf = '$w_tx_nota'";
    break;
default:
    $w_where = "where sai_cad_nf.dt_nota_fisc between '$w_tx_dt_inic' and '$w_tx_dt_fina'";
}

$w_query = "SELECT * FROM sai_cad_cara_peri ".
           "INNER JOIN sai_tb_peri on sai_cad_cara_peri.fk_seq_peri = sai_tb_peri.seq_peri ".
           "INNER JOIN sai_cad_nf ON sai_cad_cara_peri.fk_seq_nf = sai_cad_nf.seq_nf ";
$w_query .= $w_where;

To help you with the query in itself, or even the structure, I believe that your question could be supplemented with more information about the bank, the receipt of incoming, outgoing information, etc.

A suggestion: Do not mount query’s complex in PHP, opite for riding a VIEW make the join and in your application the code will be cleaner and easier to work. Each with its responsibilities, bank can also (and should) have a more worked out structure, views, procedures, triggers, functions.