How to create composite SQL request in ZendFramework 2?

For programming and general questions on Zend Framework

How to create composite SQL request in ZendFramework 2?

Postby anas355 on Sun Apr 09, 2017 9:13 am

I am trying to create search filter on my site in ZF2.
The database search is carried out according to various criteria, therefore for each column there are different conditions. For example, in the database there is a field year. Search on the field "year" is carried out as the search for the period. That is, the user specifies the "start year" and "year end", then show the documents that fall in this period. In addition, the user enters the name of the section of the document I also pass in the search function.

Code: Select all
public function getFinal_descriptionssearch($paginated=false,$searchList)
     {
      if ($paginated) {
       $select = new Select('books');
       $resultSetPrototype = new ResultSet();
       $resultSetPrototype->setArrayObjectPrototype(new Book());
       $paginatorAdapter = new DbSelect(
       $select->where($searchList)->order('iid ASC'),
       $this->tableGateway->getAdapter(),
       $resultSetPrototype
       );
       $paginatorF = new Paginator($paginatorAdapter);
       return $paginatorF;}
         $rowset = $this->tableGateway->select();
              return $rowset;
     }


At this stage was to implement a search only if the field equals (=) the search criteria. And need it to be greater than (>) or less than (<).
How to combine SQL query with different search terms. To be just a static SQL query?
Code: Select all
SELECT * FROM books WHERE (
(discipline = 'searchlist[1]')
AND
(year BETWEEN 'searchList['year_start']' AND searchList['year_end']));
anas355
 
Posts: 3
Joined: Mon Apr 18, 2016 11:16 am

Re: How to create composite SQL request in ZendFramework 2?

Postby mehm8471 on Sun Apr 16, 2017 12:33 am

"where" object is very powerful:
Following might help, please try that.

Code: Select all
        $select->where
            ->nest()
                ->equalTo('discipline', $searchlist[1])
                ->and
                ->between('year', $searchlist['year_start'], $searchlist['year_end'])
            ->unnest();


(you don't actually need nest() here but just to give an idea also about it where you might need many other conditions in real)

Suat
Suat
smozgur.com
mehm8471
 
Posts: 23
Joined: Sat Jun 21, 2014 11:39 pm


Return to Zend Framework

Who is online

Users browsing this forum: Yahoo [Bot] and 3 guests