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

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

Please reply soon as it is urgent.

Angry

I'm not too familiar with Zend\Db, but could you try this: $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); instead of $select->getSqlString() ? – Waddle

@Waddle : yes i have used that, this is used to get results. – Leonteen

Answer 1

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());

Ovovitellin

Answer 2

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 . '%');

Mede

Answer 3

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;
            }
    }

Slang