like in where query in zend framework 2

Question

I am using the Zend framework 2.x and facing the problem as I have search a lot. I want to use the like clause in query but each time gives the errors:

Here is my efforts:

$sql = new Sql($this->adapter);
$select = $sql->select()->columns(
array('user_profile_id', 'profile_login_name'))->from($this->table)->where->like(
       'profile_login_name', '%'.$strSearch.'%');
echo $select->getSqlString(); die;

but this gives the error:

Fatal error: Call to undefined method Zend\Db\Sql\Where::getSqlString() in /var/www/YAAB/branches/admin/models/Model/UserTable.php on line 131

I have also used the Zend\Db\Sql\Predicate but this also gives the error.

So my question are that

how to use the like clause in query in zend framework 2?
What is problem in my code?

Please reply soon as it is urgent.

I'm not too familiar with `Zend\Db`, but could you try this: `$statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute();` instead of `$select->getSqlString()` ? — Sam, Mar 22 '13 at 15:30

Andrew · Accepted Answer · 2013-06-18T09:56:21.470

Try this out

$select = $sql->select(); // or new Select('table');
$where = new \Zend\Db\Sql\Where();

// Using predicates
$where->addPredicate(
    new \Zend\Db\Sql\Predicate\Like('my_field', '%test%')
);

// Alternatively, a shortcut
$where->like('my_field', '%test%'); // Alternatively, a shortcut.

$select->where($where);

// this part will depend on if you're using TableGateway or what ever

$stmt = $sql->prepareStatementForSqlObject($select);
$resultSet = new ResultSet();
$resultSet->initialize($stmt->execute());

score 10 · Answer 2 · answered Aug 10 '13 at 18:19

You can use Predicator to use Like.

use Zend\Db\Sql\Predicate\Like

$where = new Where();
$where->like('my_field', '%' . $test . '%');

$select->where($where);

Note: And to use Not Like, you can use Literal instead.

$where->literal('my_field NOT LIKE ?', '%' . $test . '%');

Md Mehedi Hasan · Answer 3 · 2013-09-10T07:33:53.950

I use like that will help me to get much simpler.

    <?php

    namespace WebApp\Table;

    use Zend\Db\TableGateway\TableGateway;
    use Zend\Db\Sql\Where;
    use Zend\Db\Sql\Sql,
        Zend\Db\Adapter\Adapter;
    use Zend\Db\Sql\Expression;

    class ClassName
    {    

        public function sidebarJobByUser(\WebApp\Entity\User $user)
            {
                $userId  = $user->getId();
                $adapter = $this->tableGateway->getAdapter();
                $sql     = new Sql($adapter);

                $select = $sql->select();
                $select->from($this->table)
                       ->columns(array('user_profile_id', 'profile_login_name'))
                       ->where->like('profile_login_name', '%'.$strSearch.'%');

                $statement = $sql->getSqlStringForSqlObject($select);
                $results   = $adapter->query($statement, $adapter::QUERY_MODE_EXECUTE);

                return $results;
            }
    }

like in where query in zend framework 2

3 Answers3

Linked