Search by date php pdo

Navigation
2

I need to search the database using the DtBase column, for example: on day 26 I populated the table with several information, on day 28 I want to see what I did on day 26. I want an input date or another method that it is more feasible for me to select the date and to return the records of the bank of this day that I have selected. If the selected date does not exist in DtBase it does not show anything, if it exists it displays all records from the database table inside the html table I have below in the code.

Database column:

TablewhereIwantafieldabovetoselectthedate,andinthetablereturntherecordsofthedayselectedinthefield:

<?php$controller=newComando($conn);?><formmethod="POST" action="../controller/progPrecontrole.php">
        <div class="large-12 columns">
            <div class="TableCSS" >
                <table>
                    <tr>
                        <td>ST</td>
                        <td>BITRUCK</td>
                        <td>Motorista</td>
                        <td>Data Saída</td>
                        <td>Origem</td>
                        <td>Destino</td>
                        <td>Previsão chegada</td>
                        <td>Carga/Manifesto</td>
                        <td>Adiantamento Fincanceiro</td>
                        <td>Agendas</td>
                        <td>Malotes</td>
                        <td colspan="2">Observação</td>
                    </tr>
                    <?php
                        foreach ($controller->ListaPorTipoB() as $objProg) {
                    ?>
                    <tr>
                        <td>
                        <?php 
                            echo ($controller->RetornarCarctere($objProg->getst()));
                        ?>
                        </td>
                        <td><?php echo $objProg->getplaca(); ?></td>
                        <td><?php echo $objProg->getmot(); ?></td>
                        <td><?php echo $objProg->getsaida(); ?></td>
                        <td><?php echo $objProg->getorig(); ?></td>
                        <td><?php echo $objProg->getdest(); ?></td>
                        <td><?php echo $objProg->getprev(); ?></td>
                        <td><?php echo $objProg->getcarga(); ?></td>
                        <td><?php echo $objProg->getadfin(); ?></td>
                        <td><?php echo $objProg->getagen(); ?></td>
                        <td><?php echo $objProg->getmal(); ?></td>
                        <td class="t1" ><div><?php echo $objProg->getobs(); ?></div></td>
                        <td><a href="edita.php?id=<?php echo $objProg->getid();?>"><p>Alterar</p></a></td>
                    </tr>
                    <?php
                        }
                    ?>
                </table>
            </div>
      </form>

Functions I have so far: 

class ProgDAO{

private $conn;

public function __construct($connection) {
    $this->conn = $connection;
}

public function ListaPorTipoB($tipo){
    $results = array();
    $stmt = $this->conn->prepare('SELECT * FROM GTCLogist WHERE DsTpVeiculo = ?');
    $stmt->execute(array($tipo));
        if($stmt) {
            while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
                $prog = new Prog();
                $prog->setid($row->ID);
                $prog->setst($row->DsStatus);
                $prog->setplaca($row->NrPlaca);
                $prog->setmot(stripslashes($row->DsMotorista));
                $prog->setsaida(date('d/m/Y', strtotime($row->DtSaida)));
                $prog->setorig($row->DsOrigem);
                $prog->setdest($row->DsDestino);
                $prog->setprev(date('d/m/Y', strtotime($row->DtPrevChegDest)));
                $prog->setcarga($row->DsCarga);
                $prog->setadfin($row->DsAdFin);
                $prog->setagen($row->DsAgendas);
                $prog->setmal($row->DsMalote);
                $prog->setobs($row->DsObservacao);
                $results[] = $prog;
            }
        }
    return $results;
}

public function ListaPorTipoT($tipo){
    $results = array();
    $stmt = $this->conn->prepare('SELECT * FROM GTCLogist WHERE DsTpVeiculo = ?');
    $stmt->execute(array($tipo));
        if($stmt) {
            while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
                $prog = new Prog();
                $prog->setid($row->ID);
                $prog->setst($row->DsStatus);
                $prog->setplaca($row->NrPlaca);
                $prog->setmot(stripslashes($row->DsMotorista));
                $prog->setsaida(date('d/m/Y', strtotime($row->DtSaida)));
                $prog->setorig($row->DsOrigem);
                $prog->setdest($row->DsDestino);
                $prog->setprev(date('d/m/Y', strtotime($row->DtPrevChegDest)));
                $prog->setcarga($row->DsCarga);
                $prog->setadfin($row->DsAdFin);
                $prog->setagen($row->DsAgendas);
                $prog->setmal($row->DsMalote);
                $prog->setobs($row->DsObservacao);
                $results[] = $prog;
            }
        }
    return $results;
}

public function ListaPorTipoC($tipo){
    $results = array();
    $stmt = $this->conn->prepare('SELECT * FROM GTCLogist WHERE DsTpVeiculo = ?');
    $stmt->execute(array($tipo));
        if($stmt) {
            while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
                $prog = new Prog();
                $prog->setid($row->ID);
                $prog->setst($row->DsStatus);
                $prog->setcarreta($row->CdCarreta);
                $prog->setplaca($row->NrPlaca);
                $prog->setmot(stripslashes($row->DsMotorista));
                $prog->setsaida(date('d/m/Y', strtotime($row->DtSaida)));
                $prog->setorig($row->DsOrigem);
                $prog->setdest($row->DsDestino);
                $prog->setprev(date('d/m/Y', strtotime($row->DtPrevChegDest)));
                $prog->setcarga($row->DsCarga);
                $prog->setadfin($row->DsAdFin);
                $prog->setagen($row->DsAgendas);
                $prog->setmal($row->DsMalote);
                $prog->setobs($row->DsObservacao);
                $results[] = $prog;
            }
        }
    return $results;
}

public function editar(Prog $prog){
    $this->conn->beginTransaction();
    try {
        $stmt = $this->conn->prepare(
            'UPDATE GTCLogist SET DsStatus = :DsStatus, DsMotorista = :DsMotorista, DtSaida = :DtSaida, 
            DsOrigem = :DsOrigem, DsDestino = :DsDestino, DtPrevChegDest = :DtPrevChegDest, DsCarga = :DsCarga, 
            DsAdFin = :DsAdFin, DsAgendas = :DsAgendas, DsMalote = :DsMalote, DsObservacao = :DsObservacao,
            CdCarreta = :CdCarreta 
            WHERE ID = :ID'
        );
        $stmt->bindValue(':ID', $prog->getid(), PDO::PARAM_INT);
        $stmt->bindValue(':DsStatus', $prog->getst(), PDO::PARAM_INT);
        $stmt->bindValue(':DsMotorista', $prog->getmot(), PDO::PARAM_STR);
        $stmt->bindValue(':DtSaida', $prog->getsaida(), PDO::PARAM_INT);
        $stmt->bindValue(':DsOrigem', $prog->getorig(), PDO::PARAM_STR);
        $stmt->bindValue(':DsDestino', $prog->getdest(), PDO::PARAM_STR);
        $stmt->bindValue(':DtPrevChegDest', $prog->getprev(), PDO::PARAM_INT);
        $stmt->bindValue(':DsCarga', $prog->getcarga(), PDO::PARAM_STR);
        $stmt->bindValue(':DsAdFin', $prog->getadfin(), PDO::PARAM_INT);
        $stmt->bindValue(':DsAgendas', $prog->getagen(), PDO::PARAM_STR);
        $stmt->bindValue(':DsMalote', $prog->getmal(), PDO::PARAM_STR);
        $stmt->bindValue(':DsObservacao', $prog->getobs(), PDO::PARAM_STR);
        $stmt->bindValue(':CdCarreta', $prog->getcarreta(), PDO::PARAM_INT);
        $stmt->execute();
        $this->conn->commit();
    }
    catch(Exception $e) {
        $this->conn->rollback();
    }
}
public function listar($id){
        $prog = new Prog();
        $stmt = $this->conn->prepare(
            'SELECT * FROM GTCLogist WHERE ID = :ID'
        );
        $stmt->bindValue(':ID', $id, PDO::PARAM_INT);
        $stmt->execute();
        if($stmt) {
            while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
                $prog->setid($row->ID);
                $prog->setst($row->DsStatus);
                $prog->setcarreta($row->CdCarreta);
                $prog->setplaca(stripslashes($row->NrPlaca));
                $prog->setmot($row->DsMotorista);
                $prog->setsaida(date('d/m/Y', strtotime($row->DtSaida)));
                $prog->setorig($row->DsOrigem);
                $prog->setdest($row->DsDestino);
                $prog->setprev(date('d/m/Y', strtotime($row->DtPrevChegDest)));
                $prog->setcarga($row->DsCarga);
                $prog->setadfin($row->DsAdFin);
                $prog->setagen($row->DsAgendas);
                $prog->setmal($row->DsMalote);
                $prog->setobs($row->DsObservacao);
            }
        }
    return $prog;
}
public function ListaData(){
    $results = array();
    $stmt = $this->conn->prepare('SELECT * FROM GTCLogist');
    $stmt->execute();
        if($stmt) {
            while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
                if($row->ID == '1'){
                    $prog = new Prog();
                    $prog->setid($row->ID);
                    $prog->setdata(date('d/m/Y', strtotime($row->DtBase)));
                    $results[] = $prog;
                }
            }
        }
    return $results;
}
}

Control: 

<?php

class Comando{

private $conn;

public function __construct($connec) {
    $this->conn = $connec;
}

public function ListaPorTipoB(){
    $dao = new ProgDAO($this->conn);
    return $dao -> ListaPorTipoB('Bitruck');
}

public function ListaPorTipoT(){
    $dao = new ProgDAO($this->conn);
    return $dao -> ListaPorTipoT('Truck');
}

public function ListaPorTipoC(){
    $dao = new ProgDAO($this->conn);
    return $dao -> ListaPorTipoC('Cavalo Truck');
}

public function ListaData(){
    $dao = new ProgDAO($this->conn);
    return $dao -> ListaData();
}

public function editar(Prog $objProg){
    $dao = new ProgDAO($this->conn);
    return $dao -> editar($objProg);
}

public function listar($id){
    $dao = new ProgDAO($this->conn);
    return $dao -> listar($id);
}

public function RetornarCarctere($x){
    $dao = new ProgDAO($this->conn);
    return $dao -> RetornarCarctere($x);
}
}

?>
    
asked by anonymous 26.01.2016 / 16:34

2 answers

1

To make an easier search I use a way when it comes to single search. For example, do a search that searches for everything that is 26/01/2016 .

So in SELECT I do this:

SELECT * FROM TABELA WHERE CONVERT(VARCHAR(10), DATA, 103) = '26/01/2016'

Note the use of CONVERT . I convert the date into text and the format dd/mm/yyyy and I make a WHERE on the date that comes from the HTML form. This would be comparing date in text format.

In the form you can have a text field, with mask maybe: __/__/____ . And when PHP redeems this field you can by SELECT and PDO .

    
02.02.2016 / 14:09
0

Kevin, I did not have time to create the tables and test 100%. But taking advantage of the code you posted, there is a solution:

Code: 

<?php

$data = filter_input(INPUT_GET, 'dt');

?>

<div class="large-12 columns">
        <div class="large-3 columns">
            <?php
                foreach ($controller->ListaData() as $objProg) {
                    echo "<a href='/?dt=". implode("-",array_reverse(explode("/", $objProg->getdata()))) ."''>". $objProg->getdata() ."</a>";
                }
            ?>
            <a id="open-first">Código Status</a>
            <hr>
        </div>
    </div>
    <form method="POST" action="../controller/progPrecontrole.php">
        <div class="large-12 columns">
            <div class="TableCSS" >
                <table>
                    <tr>
                        <td>ST</td>
                        <td>BITRUCK</td>
                        <td>Motorista</td>
                        <td>Data Saída</td>
                        <td>Origem</td>
                        <td>Destino</td>
                        <td>Previsão chegada</td>
                        <td>Carga/Manifesto</td>
                        <td>Adiantamento Fincanceiro</td>
                        <td>Agendas</td>
                        <td>Malotes</td>
                        <td colspan="2">Observação</td>
                    </tr>
                    <?php
                        foreach ($controller->listar($data) as $objProg) {
                    ?>
                    <tr>
                        <td>
                        <?php 
                            echo ($controller->RetornarCarctere($objProg->getst()));
                        ?>
                        </td>
                        <td><?php echo $objProg->getplaca(); ?></td>
                        <td><?php echo $objProg->getmot(); ?></td>
                        <td><?php echo $objProg->getsaida(); ?></td>
                        <td><?php echo $objProg->getorig(); ?></td>
                        <td><?php echo $objProg->getdest(); ?></td>
                        <td><?php echo $objProg->getprev(); ?></td>
                        <td><?php echo $objProg->getcarga(); ?></td>
                        <td><?php echo $objProg->getadfin(); ?></td>
                        <td><?php echo $objProg->getagen(); ?></td>
                        <td><?php echo $objProg->getmal(); ?></td>
                        <td class="t1" ><div><?php echo $objProg->getobs(); ?></div></td>
                        <td><a href="edita.php?id=<?php echo $objProg->getid();?>"><p>Alterar</p></a></td>
                    </tr>
                    <?php
                        }
                    ?>
                </table>
            </div>
      </form>

DAO list method: 

public function listar($data, $id = null){

        $sql = "SELECT * FROM GTCLogist WHERE 1=1";
        if (isset($data) && !empty($data)){
            $sql .= " AND DtBase = :dt ";
        }
        if (isset($id) && !empty($id)){
            $sql .= " AND ID = :ID ";
        }

        $prog = new Prog();
        $stmt = $this->conn->prepare($sql);
        if (isset($data) && !empty($data)){
            $stmt->bindValue(':dt', $data);
        }
        if (isset($id) && !empty($id)){
            $stmt->bindValue(':ID', $id);
        }
        $stmt->execute();

        if($stmt) {
            while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
                $prog->setid($row->ID);
                $prog->setst($row->DsStatus);
                $prog->setcarreta($row->CdCarreta);
                $prog->setplaca(stripslashes($row->NrPlaca));
                $prog->setmot($row->DsMotorista);
                $prog->setsaida(date('d/m/Y', strtotime($row->DtSaida)));
                $prog->setorig($row->DsOrigem);
                $prog->setdest($row->DsDestino);
                $prog->setprev(date('d/m/Y', strtotime($row->DtPrevChegDest)));
                $prog->setcarga($row->DsCarga);
                $prog->setadfin($row->DsAdFin);
                $prog->setagen($row->DsAgendas);
                $prog->setmal($row->DsMalote);
                $prog->setobs($row->DsObservacao);
            }
        }
    return $prog;
}

Note that I've tried to change as little as possible for you to take advantage of what's already done. But I advise you to refactor your code.

I added a link on the dates that you print at the top of the screen, to the same page, after I retrieve that date and step as a parameter in the list method ($ data).

In DAO I changed the list method to filter by date.

NOTE: If the attributes of the Prog () class are the same as those of the bank, you can override doing the following after $stmt->execute() : 

$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_CLASS, 'Prog');

That way the PDO itself returns you an Array of objects. =)

I hope I have helped!

    
26.01.2016 / 18:18
How to do regular expression to accept words and then 2 numbers with jQuery, without special characters? How to validate md5 password with database? [closed]