Welcome to Abdul Malik Ikhsan's Blog

Zend Framework 2 : Getting real Sql String of Zend\Db

Posted in Tutorial PHP, Zend Framework 2 by samsonasik on May 16, 2014

zf2-zendframework2 I know, this is maybe a simple post for you, but it useful for me :). Hope it useful for someone. When we build a query using Zend\Db, sometime, complex query need checking for debugging to make sure our query is right. For example, we need to make sure we build a query like this :

SELECT `album`.* FROM `album` WHERE `title` = 'abracadabra' AND (`id` = '1' OR `artist` = 'Tony') LIMIT 1

And we already have an \Zend\Db\Sql\Select instance. In case if you have Model class like zf2 docs provide

$sql = $this->tableGateway->getSql();
$select = $sql->select();
$select->where(array('title' => 'abracadabra'));
$select->where
  ->NEST->
        equalTo('id', 1)
            ->OR->
        equalTo('artist', 'Tony')
  ->UNNEST;

$select->limit(1);

So, Before we return it with :

return $this->tableGateway->selectWith($select);

We can check it with :

echo $sql->getSqlstringForSqlObject($select); die ; // ( die/exit to debugging purpose )
//it will print sql string :
// SELECT `album`.* FROM `album` WHERE `title` = 'abracadabra' AND (`id` = '1' OR `artist` = 'Tony') LIMIT 1

That will return the real sql build for our specific platform.

references :
https://github.com/zendframework/zf2/issues/3224

About these ads

7 Responses

Subscribe to comments with RSS.

  1. seyferx said, on May 16, 2014 at 3:44 pm

    How to output in Doctrine case?

    $this->queryBuilder = $this->getEntityManager()->createQueryBuilder();
    $this->queryBuilder->select(“c”)->from($this->getEntityName(), “c”);

    Then i’m build some query and then get it
    $query = $this->queryBuilder->getQuery();

    Now i’m can output it like this
    \Doctrine\Common\Util\Debug::dump($query->getSQL());

    But there i’m will see ? in places, where i placed params.
    How to see query with binded param values?

  2. Mhor said, on May 16, 2014 at 4:28 pm

    I normally use $select->getSqlString(). Is there a difference?

  3. Mohammad Nomaan Patel said, on May 16, 2014 at 4:46 pm

    Hi Samsonasik,
    Thanks for the useful post. Waiting for the post about Apigility…. Hope it would be coming soon

  4. cany said, on May 16, 2014 at 6:35 pm

    sir, i have been searching calender or date code, alongwith upload fiels which shows progress meter am not getting it
    1) in one form i should have fields which have date field but when u click inside text box calendar should open 2) upload form for all types doc/pdf/image/txt with multiple which also shows progress in percentage 3) when u click submit or save button all above function should save to database and upload to particular path 5) i have tried zf2fileupload examples but not getting progress bar 6) date i have used combo box


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 260 other followers

%d bloggers like this: