Search by data php Pdo

Asked

Viewed 522 times

2

I need to search the bank using the column Dtbase, for example: day 26 I populated the table with various information, day 28 I want to consult what I did day 26. I want an input date or other method that is more feasible for me to select the date and to return the records from the database of this day that I selected. If the selected date does not exist in Dtbase shows nothing, if it exists displays all records of the database table within the html table I have below in the code.

Database column:

inserir a descrição da imagem aqui

Table where I want a field above to select the date, and in the table return the records of the selected day in the field (date):

<?php
$controller = new Comando($conn);
?>
    <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->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);
}
}

?>
php html sql pdo

  • Post the code where you make the queries in the bank.

  • I don’t even have a search function yet. I posted the ones I have above.

  • 1

    Explain better what you want. I understood that you want a page that you click and open a calendar for example to choose the date, and that this date is passed for consultation. That’s it?

  • On the 26th of any month or year? I don’t understand.

  • @Kevin. F you have 3 listing methods in your DAO, which one you want to use to make the date filter.

  • I want a search field to search for previous dates, at the top of the same page that below will pull the information in the table that already exists, depending on the day that is selected. Can be from 26/01/2016 onwards. This table will be populated every day. For example: today I populated with various information, tomorrow I want to consult what I did today, then on the 28th I want to consult what I did on the 26th. An input select, searching for the dates I want to see the information in the table below.

  • @Thomas Lima anyone, whatever else is feasible.

  • @Kevin. F what I imagined is the following: 2 date fields (initial and final) to search all records of this period, then in the list method you pass an array with its parameters: list(params). And in the list method you make a foreach in your array by mounting your WHERE Dtbase clause between '2016-01-26' AND '2016-01-26'. That would be it???

  • @Thomas Lima Could not be a WHERE Dtbase = '2016-01-26' and only with a field ? I see no problem, in making a between however to be more specific the way above would be better.

  • 2

    @Kevin. F right... got a little confused the part where you say you want to bring the records of the day 26 and in that result the previous days tbm... you want to bring only the records of the selected day... or the records of the selected day + the previous days???

  • @Thomas Lima, only those of the selected day.

  • uses an ajax request is easier for the server

  • I haven’t found a solution yet, some help ?

Show 8 more comments

2 answers

1


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

Then in the SELECT I do like 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 format dd/mm/yyyy and make a WHERE on the date of the form HTML. This way would be comparing date in text format.

In the form you can have a field text, maybe with a mask: __/__/____. And when the PHP rescue this field you can put in your SELECT in the PDO.

  • You have some practical example to base ?

0

Kevin, I didn’t have time to create the tables and test 100%. But taking advantage of the code you posted, follows 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 listing 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 tried to change as little as possible to take advantage of what is already ready. But I advise you to refactor your code.

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

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

NOTE: If the attributes of the Prog() class are equal to those of the database, you can replace the following after $stmt->execute():

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

This way the PDO itself returns an object array. =)

I hope I’ve helped!

Browser other questions tagged php html sql pdo

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