Code problem ( Find values between a date and another in the database )

Question

Okay, here’s the problem. I’m working on a code that picks up a series of flights that are available between a date and another date entered by the user. Dates are entered through the datepicker in js, but I think that part is working well and is not related to the problem. However I have not had success yet, the data is never returned. I have already used the if conditions to change the return of the data but it is still in error. I think it has to see the $_GET parameter misapplied. I would very much appreciate your support if further information is required and ask for coolocarei.

 <?php

if (isset($_GET["botao"])){
$parms = array();
$filter = array();

$query = "SELECT V.ID FROM voo as V WHERE ";
    if (isset($_GET["origem"]) && !empty($_GET["origem"])) {
        $filter[] = "(V.IDLocalOrigem=?)";
        $parms[] = $_GET["origem"];
    }

    $query = "SELECT V.ID FROM voo as V WHERE ";
    if (isset($_GET["destino"]) && !empty($_GET["destino"])) {
        $filter[] = "(V.IDLocalDestino=?)";
        $parms[] = $_GET["destino"];
    }

    $query = "SELECT V.ID FROM voo as V WHERE ";
    if (isset($_GET["data"]) && !empty($_GET["data"])) {
        $filter[] = "(V.DataPartida>=?)";
        $parms[] = $_GET["data"];
    }

    $query = "SELECT V.ID FROM voo as V WHERE ";
    if (isset($_GET["entre"]) && !empty($_GET["entre"])) {
        $filter[] = "(V.DataChegada<=?)";
        $parms[] = $_GET["entre"];
    }

}       


        $query .= explode(' AND ',$filter);

   ?>       

        <pre><?php var_dump($filter); ?></pre>
  <?php



    /*$query= "select V.ID, V.IDLocalOrigem, L1.Nome, V.IDLocalDestino,".
    " L2.Nome, V.IDAviao, V.DataPartida, V.DataChegada,".
    " V.PrecoBilhete, V.Estado, L1.Nome as nomeOrigem, L2.Nome as nomeDestino".
    " FROM Voo as V".
    " INNER JOIN Local as L1 ON V.IDLocalOrigem = L1.ID".
    " INNER JOIN Local as L2 ON V.IDLocalDestino = L2.ID".
    " WHERE ((V.IDLocalOrigem=?) OR (? is NULL)) AND".
    " ((V.IDLocalDestino=?) OR (? is NULL)) AND".
    " ((V.DataPartida>=?) OR (? is NULL)) AND".
    " ((V.DataPartida<=?) OR (? is NULL)) AND".
    " ((V.PrecoBilhete=?) OR (? is NULL)) AND".
    " ((V.Estado=?) OR (? is NULL))";*/


      // RESOLVER: Pesquisar todos os voos entre datas
    $stmt = $mydb->prepare($query);
    $Origem = !empty($_GET["origem"]) ? $_GET["origem"] : null;
    $Destino =!empty($_GET["destino"]) ? $_GET["destino"] : null;
    $DataPartida = null;
    if (!empty($_GET["data"])){
        $DataPartida = $_GET["data"].' 00:00:00';   
    }
    if (!empty($_GET["entre"])){
        $DataChegada = $_GET["entre"].' 23:59:59';  
    }

    $Preco = null;

    $Estado = null;

    $stmt->bind_param("iiii", $Origem, $Destino, 
    $DataPartida $DataChegada);

    $stmt->execute();


    $result = $stmt->get_result();
    $todosDados = $result->fetch_all(MYSQL_ASSOC);

    foreach($todosDados as $r   )
        {
        echo "<h1>Ida</h1>";

        echo "<table border=1>";
        echo "<tr>";
        echo "<td>";

        echo "Origem:".$r["nomeOrigem"]."</br>";
        echo "Destino:".$r["nomeDestino"]."</br>";
        echo "DataPartida:".$r["DataPartida"]."</br>";
        echo "DataChegada:".$r["DataChegada"]."</br>";
        echo "Preço:".$r["PrecoBilhete"]."</br>";
        echo "Estado:".$r["Estado"]."</br>";
        echo '<a href="lugares.php?idvoo='.$r["ID"].'" class="btn btn-primary">Comprar</a>';

        echo "</td>";
        echo "</tr>";
        echo "</table>";
        }   

    $result->free();


    $stmt->bind_param("iiii", $Origem, $Destino, 
    $DataPartida, $DataChegada);

    $stmt->execute();


    $result = $stmt->get_result();
    $todosDados = $result->fetch_all(MYSQL_ASSOC);




    foreach($todosDados as $r   )
        {
        echo "<h1>Ida</h1>";

        echo "<table border=1>";
        echo "<tr>";
        echo "<td>";

        echo "Origem:".$r["nomeOrigem"]."</br>";
        echo "Destino:".$r["nomeDestino"]."</br>";
        echo "DataPartida:".$r["DataPartida"]."</br>";
        echo "DataChegada:".$r["DataChegada"]."</br>";
        echo "Preço:".$r["PrecoBilhete"]."</br>";
        echo "Estado:".$r["Estado"]."</br>";
        echo '<a href="lugares.php?idvoo='.$r["ID"].'" class="btn btn-primary">Comprar</a>';

        echo "</td>";
        echo "</tr>";
        echo "</table>";
        }   

    $result->free();
    $stmt->close();
?>

$filter displays this var_dump:

array(4) {
  [0]=>
  string(19) "(V.IDLocalOrigem=?)"
  [1]=>
  string(20) "(V.IDLocalDestino=?)"
  [2]=>
  string(18) "(V.DataPartida>=?)"
  [3]=>
  string(18) "(V.DataChegada<=?)"
}

Answer 1

Here is an example of how to mount the dynamic query:

$parms = array();
$filter = array();
$query = "(...) WHERE ";
if (isset($_GET["origem"]) && !empty($_GET["origem"])) {
    $filter[] = "(V.IDLocalOrigem=?)";
    $parms[] = $_GET["origem"];
}

[fazer o mesmo para os restantes parametros]

$query .= implode(' AND ', $filter);

[executar o query com os parametros que foram preenchidos]

Note that the code has not been tested, there may be some error.