List all the data of a search

Navigation
1

How to list all the data in this query, I can only display the first line but I want to query all of them and show them. 

public function gerarCardapio(){
        try{
            $cat = "M";
            $sql = "SELECT * FROM alimentos WHERE id_usuario = :idUser AND categoria = :cat AND quantidade >0 ";
            $stmt = DB::prepare($sql);
            $stmt->bindParam('idUser', $_SESSION['user_id']);
            $stmt->bindParam('cat', $cat);
            $stmt->execute();//Executa o comano SQL
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            echo $stmt->rowCount();
        }catch (PDOException $e){
            echo $e->getMessage();
        }
    }
    
asked by anonymous 15.10.2017 / 00:47

2 answers

1

As commented by @Knautiluz, you should use while . 

# Apenas um exemplo de uso, pois não sei os nomes das colunas.
while($linha = $stmt->fetch(PDO::FETCH_ASSOC)){
    printf("id_usuario: %s categoria: %s", $linha['id_usuario'], $linha['categoria']);
}

thus getting your method: 

public function gerarCardapio(){
    try{
        $cat = "M";
        $sql = "SELECT * FROM alimentos WHERE id_usuario = :idUser AND categoria = :cat AND quantidade >0 ";
        $stmt = DB::prepare($sql);
        $stmt->bindParam(':idUser', $_SESSION['user_id']);
        $stmt->bindParam(':cat', $cat);
        $stmt->execute();//Executa o comano SQL
        # Apenas um exemplo de uso, pois não sei os nomes das colunas.
        while($linha = $stmt->fetch(PDO::FETCH_ASSOC)){
            printf("id_usuario: %s categoria: %s", $linha['id_usuario'], $linha['categoria']);
        }
    }catch (PDOException $e){
        echo $e->getMessage();
    }
}
  

You can see a basic example in tutorialspoint

I recommend that you read the documentation how to use the bindParam .

References

15.10.2017 / 01:21
0

I noticed that you are returning the number of rows from the table's feed field using echo $stmt->rowCount();

Use $result->fetchAll(PDO::FETCH_ASSOC) ,

And also use : in handling bindParam (: ..., $ ....); 

public function gerarCardapio(){
  try{
    $cat = "M";
    $sql = "SELECT * FROM alimentos WHERE id_usuario = :idUser AND categoria = :cat AND quantidade > 0 ";
    $stmt = DB::prepare($sql);
    $stmt->bindParam(':idUser', $_SESSION['user_id']);
    $stmt->bindParam(':cat', $cat);
    $stmt->execute();//Executa o comano SQL
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    var_dump($result);
  }catch (PDOException $e){
    echo $e->getMessage();
  }
}
    
15.10.2017 / 01:07
If there is data in mysql make a PHP / MySQL update Send appointment email for Google Calendar appointment