Search Function in Symfony2 is not working

Question

I am working on a project using symfony framework. I just want to make a simple search function wherein it can search all the entities that contain with what the user inputted. When I tried to run my code, its not working it keeps on redirecting to my no found result even if their is a related files to be found with.

Here is my controller:

public function searchAction(Request $request){
        $request = $this->getRequest();
        $data = $request->request->get('search');


        $em = $this->getDoctrine()->getManager();
        $query = $em->createQuery(
                'SELECT a,b,c
                FROM SampleBundle:transactionDetail a
                JOIN a.transaction b
                JOIN b.documentRelated c
                WHERE a.pNumber LIKE :data
                AND b.senderId LIKE :data
                AND b.receiverId LIKE :data
                AND b.transactDate LIKE :data
                AND a.amountPaid LIKE :data')
                ->setParameter('data',$data);  

        $res = $query->getResult();
        if($res == null){
                return $this->render('SampleBundle:Sample:noresult.html.twig');
        }
        return $this->render('SampleBundle:Sample:search.html.twig', array('res' => $res));
    }

and for my search.html.twig

{% extends '::layout.html.twig' %}
{% block pageTitle %} Related Files Found{% endblock %}
{% block body %}

div class="table-responsive margins" >
  <table  class="table table-condensed table-striped table-bordered table-hover no-margin">
      <thead>
        <tr style="height: 40px; ">
        <th>Transaction Date</th>       
        <th>Sender ID</th> 
        <th>Receiver ID</th>
        <th>P Number</th> 
        <th>Amount Paid</th> 

      </tr>
    </thead>
    <tbody>
    <tr>    
        {% for res in res %}         
          {% for other in res.transaction %}
         <td >{{res.transaction.transactDate|date('YMd')}}&nbsp;&nbsp;{{res.ediTransaction.transactionDate|date('H:i')}}</td>
          <td>{{res.transaction.senderId}}</td>
          <td >{{res.transaction.receiverId}}</td>

          {% if other.pNumber != null %}
            <td style="word-break: break-all">{{other.pNumber}}</td> 
          {% else %}
            <td>N/A</td>
          {% endif %}
          {% if other.amountPaid != null %}
             <td style="word-break: break-all">{{other.amountPaid}}</td>    
          {% else %}
            <td>N/A</td>
          {% endif %}

      </tr>
          {% endfor %}
      {% endfor %}
      </tbody>
    </table>
  </div>
{% endblock %}
{% block javascripts %}
  {% javascripts
    'bundles/sampledoc/js/jQuery.js'
    'bundles/sampledoc/js/bootstrap.js'
     %}
    <script src="{{ asset_url }}"></script>

  {% endjavascripts %}
{% endblock %}

my form for searching:

<form action="{{path('sample_search')}}" method="POST">
  <div class="form-group input-group">
    <input type="text" name="search" class="form-control" placeholder="Search" style="width: 200px; float: right;" >
      <span class="input-group-btn"><button class="btn btn-default" type="submit" style="margin-right: 20px;"><i class="fa fa-search"></i></button></span>
  </div>
</form>

Can somebody help me with this one? thanks in advance.

Answer 1

Assuming $data is an associative array holding all the goodies, I do not think you can just hand it to setParameter() the way you are doing. You need to break it down more specifically, for each of those "LIKE" clauses.

Completely untested but I suspect you are looking for something like:

`
WHERE a.pNumber LIKE :pNumber
AND b.senderId LIKE :senderId
AND b.receiverId LIKE :recieveId
AND b.transactDate LIKE :transactDate
AND a.amountPaid LIKE :amountPaid')
->setParameters(array(
    'pNumber' => $data['pNumber'],
    'receiverId'   => $data['receiverId'],
    'transactDate' => $data['transactDate'],
    'amountPaid'   => $data['amountPaid'],
));
`

Answer 2

Data can't equal all your criteria. If you AND everything in your query data has to match all fields, so you will probably return no results.

Try something like:

WHERE a.pNumber LIKE :data
OR b.senderId LIKE :data
OR b.receiverId LIKE :data
OR b.transactDate LIKE :data
OR a.amountPaid LIKE :data')
->setParameter('data', "%$data%"); 

And look at the generated SQL with

$query->getSQL()

This way you can actually test your query directly on the DB.