Mysql functions in zend 2

Question

How to query below in zend 2

select * from states st where TRIM(LOWER(st.state_name))='noida'

Any help is appreciated. Thanks

Please try something yourself. Have you referred -> http://stackoverflow.com/questions/10242185/zend-query-select ? — Pratik Joshi, May 26 '15 at 06:31
@jQuery.PHP.Magento.com reference provided by you is of zend 1 :( — Arvind Jha, May 26 '15 at 07:22
Check http://stackoverflow.com/questions/17242185/zend-framework-2-sql-select-with-or-and-and — Pratik Joshi, May 26 '15 at 07:43

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

Use following:

$resultStates=$this->states->select()->where('TRIM(LOWER(st.state_name))=?','noida')
    ->query()
    ->fetchAll();

For details refer Here and Here.

edited May 23 '17 at 12:31

Community

1
1

answered May 26 '15 at 06:34

Pratik Joshi

11,485
7
41
73

newage · Accepted Answer · 2015-05-26T08:14:49.970

0

/* DB Adapter get and SQL object create */
$adapter = GlobalAdapterFeature::getStaticAdapter();
$sql = new \Zend\Db\Sql\Sql($adapter);

/* Select object create */
$select = new \Zend\Db\Sql\Select();
$select->from('states');
$select->where->addPredicate(
    new \Zend\Db\Sql\Predicate\Expression(
        'TRIM(LOWER(state_name)) = ?',
        'noida'
    )
);

/* Select object convert to string and execute */
$queryString = $sql->getSqlStringForSqlObject($select);
$result = $adapter->query($queryString, Adapter::QUERY_MODE_EXECUTE);

edited May 26 '15 at 08:14

answered May 26 '15 at 07:42

newage

899
7
18

What is `$sql`? Also, you have an extra `)` in `addPredicate` line which I have already fixed. – php-dev May 26 '15 at 08:10

score 0 · Answer 3 · answered Feb 19 '16 at 10:12

In you model file just use below code here I am using module profile.

Profile/Model/Common.php

namespace Profile\Model;

use Zend\Db\Sql\Sql;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Select;

class Common 
{
        protected $dbConfig;
        protected $adapter;

        public function __construct($dbConfig)
         {
            $this->adapter = new Adapter($dbConfig);
         }
        public function getStateList()
         {

            $sql  = "select * from states st where TRIM(LOWER(st.state_name))='noida'";
            $statement = $this->adapter->query($sql);

            $results = $statement->execute();

            $resultSet = new ResultSet();
            $resultSet->initialize($results);
            $list = $resultSet->toArray();

            return $list; // This will return a list of array
        }

}

Profile/Controller/IndexController

namespace Profile\Controller;
use Profile\Model\Common;


class IndexController extends AbstractActionController
{


    protected $dbConfig = array(
                        'driver' => DRIVER,
                        'database' => DB,
                        'username' => DBUSER,
                        'password' => DBPASS
                     );

    public function init(){
    $ssOrder = new Container(__CLASS__);

        //SET OPTIONS 
    }


    public function indexAction()
    {
        $plist = new Common($this->dbConfig);
        $resultList = $plist->getStateList(); // This will give you state list


    }


}

Good Luck

Mysql functions in zend 2

3 Answers3