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'));
        equalTo('id', 1)
        equalTo('artist', 'Tony')


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 :

14 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

    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

  5. Jii said, on February 18, 2015 at 11:06 pm

    Thanks for these great tutorials! These have saved me many hours.

  6. Duy said, on October 7, 2016 at 5:19 pm

    Thanks. :)))

  7. Bart McLeod said, on June 13, 2017 at 7:26 pm

    Hi Adbul,

    How do you get the sql for an insert? (Except from using a debugger)

    • samsonasik said, on June 19, 2017 at 6:21 pm

      Hi Bart,

      As far as I know, the way to do for insert is by define insert object:

      use Zend\Db\Sql\Insert;
      $insertObj = new Insert;
      $insertObj->values(array('name' => 'samsonasik'));
      echo $this->tableGateway->getSql()->getSqlStringForSqlObject($insertObj);


      • Bart McLeod said, on June 19, 2017 at 6:50 pm

        Nice one! I ended up using the debugger to step through and find the generated sql that way.

  8. narasimha said, on October 20, 2017 at 7:49 pm

    just like this blog how to use the following line in zend.i am stuck with it.

    “SELECT * FROM blogdata WHERE description LIKE ‘%” . $term . “%'”;

    this is my method in model

    public function getBlogsWithSearch($term){

    $sql = $this->getSql();

    $query = $sql->select()
    ->where(“description LIKE ?”, “%{$term}%”)

    $blogs = array();

    $resultSet = $sql->prepareStatementForSqlObject($query)->execute();

    foreach($resultSet as $row){
    $blogs[] = $row;

    return $blogs;

    }catch(\Exception $e){
    return array();

    it is always returing zero.please help me brother.thanks in advance

    • samsonasik said, on October 21, 2017 at 7:33 am

      you can do something like this:

      $select->where->like("description", "%$term%");

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 )

Connecting to %s

%d bloggers like this: