Filter in a Select MYSQL via PHP (Ionic 3)

Question

Good afternoon to everyone, before explaining a little more about the doubt I would like to make it clear that I am very lay in the subject and, amazingly, I’ve been trying to solve this problem for weeks. Doubt: I would like to list some data from my BD, in case the user would enter a date and the system would list only records containing that date, as if it were a filter. After several tests I only managed to make a simple list, without any filter, follows the codes:(home.ts)

    carregarProdutos(){

  this.produtoProvider.getAll()
  .then(data => {
    this.produtos = data;

  });

}

products.ts(Preview)

  getAll() {
return new Promise(resolve => {
  this.http.get(this.URL+'/produto').subscribe(data => {
    resolve(data);
  }, err => {
    console.log(err);
  });
});
}

Index.php

$app->get('/produto/', function() use ($app){
   (new \controllers\Produto($app))->lista();
});

Controller product.php

        public function lista(){
            global $app;
            $sth = $this->PDO->prepare("SELECT * FROM produtos");
            $sth->execute();
            $result = $sth->fetchAll(\PDO::FETCH_ASSOC);
            $app->render('default.php',["data"=>$result],200); 
        }

As I said this code is working, but I can not in any way make the variable transport so that it stays like this:

$sth = $this->PDO->prepare("SELECT * FROM produtos WHERE data= :data");
$sth ->bindValue(':data',$data);

Again I’m sorry, but I’m really layabout.

Answer 1

First of all I would like to thank Jorge, he helped me a lot in the resolution. I will explain here how the functional code was. index php.

$app->get('/produto/:datad', function($datad) use ($app){
(new \controllers\Produto($app))->lista($datad);

}); Controller:

public function lista($datad){
    global $app;
    $sth = $this->PDO->prepare("SELECT * FROM consumo_atual where data_criacao = :data");
    $sth ->bindValue(':data',$datad);
    $sth->execute();
    $result = $sth->fetchAll(\PDO::FETCH_ASSOC);
    $app->render('default.php',["data"=>$result],200); 
}

Now my only problem is the listing of the data on the TS itself, but anyway I leave the record to anyone who may have this doubt in the future.

Answer 2

You can pass the date as a parameter in your route

On the client side, add method with a date parameter for date search, at http request url pass date: /product/'+ date

getAllByDate(data) {
return new Promise(resolve => {
  this.http.get(this.URL+'/produto/'+ data).subscribe(data => {
    resolve(data);
  }, err => {
    console.log(err);
  });
});
}

On the server side, add a path with the date parameter, {date}

$app->get('/produto/{data}', function($request, $response, $args) use ($app){

   //o valor da data é passado no array $args
   (new \controllers\Produto($app))->lista($args['data']);
});

    //data é passada como argumento para o seu método
    public function lista($data){
        global $app;
        $sth = $this->PDO->prepare("SELECT * FROM produtos WHERE               data= :data");

       $sth ->bindValue(':data',$data);
        $sth->execute();
        $result = $sth->fetchAll(\PDO::FETCH_ASSOC);
        $app->render('default.php',["data"=>$result],200); 
    }