Method returns no data

Question

I'm trying to send a list of a products from a form to another view. While querying the database, I can have results but it's not the case with the methods I'm using.

This is the method to generate the form view :

 public function battleAction() {
  $categories = $this->forward('ProductBundle:Product:categorie');
    return $this->render('ProductBundle:Product:battle.html.twig', 
array('categories' => $categories)); }

This is the method to validate the form action and get the results :

    public function battleRetailersAction(Request $request) {
            $retailer = $request->get('retailer');
            $retaile = $request->get('retaile');
            $category = $request->get('category');
            $subcategory = $request->get('subcategory');
            $em = $this->getDoctrine()->getManager();
            $RAW_QUERY =  'SELECT * FROM  produit_revendeur pr,produit p, revendeur r , subcat s , categorie c WHERE r.nom IN("'.$retailer.'","'.$retaile.'") 
and c.nom like "'.$category.'" and s.nom like "'.$subcategory.'" and pr.produit_id = p.id
 AND r.id = pr.revendeur_id
        and s.categorie_id = c.id ;';
        $statement = $em->getConnection()->prepare($RAW_QUERY);
        $statement->execute();
        $products= $statement->fetchAll();
        var_dump($products);
        $categories = $this->forward('ProductBundle:Product:categorie');
        return $this->render('ProductBundle:Product:battles.html.twig', array('categories' => $categories,'products'=>$products));
    }

This is my form (basic):

<form  method="post" action="{{url('battleretailers')}}" >
  Retailer 1: <input type="text" id="retailer" name="retailer1"><br>
  Retailer 2: <input type="text" id="retaile" name="retailer2"><br>
  Category : <input type="text" id="category" name="category"><br>
  Subcategory: <input type="text" id="subcategory" name="subcategory"><br>
  <input type="submit">
</form>

This is the view to query the results :

<h1>liste des produits <h1>
        <table border =1>
            <tr>
                <th>Nom </th>
            </tr>
            {% for p in products %}
           <tr>
                <td> {{p.nom}} </td>
            </tr>

            {% endfor %}
        </table>

Answer 1

You will need to restructure your SQL based on the input of your user e.g.

$sql = 'SELECT * FROM  produit_revendeur pr,produit p, revendeur r , subcat s , categorie c WHERE pr.produit_id = p.id';
if (trim($retailer) != '' || trim($retaile) != '') {
    $sql .= ' AND r.nom IN (';
    if (trim($retailer) != '')  $sql .= '?';
    if (trim($retailer) != '' && trim($retaile) != '') $sql .= ',';
    if (trim($retaile) != '')   $sql .= '?';
    $sql .= ')';
}
if (trim($category) != '') $sql .= ' AND c.nom LIKE ?';
if (trim($subcategory) != '') $sql .= ' AND s.nom LIKE ?';


$parms = [];
if (trim($retailer) != '') $parms[] = $retailer;
if (trim($retaile) != '') $parms[] = $retaile;
if (trim($category) != '') $parms[] = '%'.$category.'%';
if (trim($subcategory) != '') $parms[] = '%'.$subcategory.'%';


$statement = $em->getConnection()->prepare($sql);
$statement->execute($parms);