Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Displaying the generated SQL from a Zend\Db\Sql object

DZone's Guide to

Displaying the generated SQL from a Zend\Db\Sql object

· Database Zone ·
Free Resource

Slow SQL Server? These SentryOne resources share tips and tricks for not only troubleshooting SQL Server performance issues, but also preventing them before they hit your production environment.

If you use Zend\Db\Sql to generate your SQL, then it's useful to find out what the generated SQL looks like.

Consider code like this:

public function fetchAllWithTitleSince($title, $since)
{
    $sql = new Sql($this->dbAdapter);
 
    $select = $sql->select();
    $select->from($this->tableName);
    $select->columns(array('id', 'title', 'url', 'date_updated'));
    $select->where->like('title', "%$title%");
    $select->where->greaterThanOrEqualTo('date_created', date('Y-m-d', strtotime($since)));
 
    $statement = $this->dbAdapter->createStatement();
    $select->prepareStatement($this->dbAdapter, $statement);
    return $statement->execute();
}

To find out what the generated SQL will look like, you can use the $select's getSqlString()method:

$select->getSqlString();

For me, this results in lots of warnings along the lines of:

Lots of notices

This is less than helpful, so to avoid the warnings, you need to supply the correct platform information to the method:

    $select->getSqlString($this->dbAdapter->getPlatform());

So we now get:

Expected SQL string

This is much better!





Database monitoring tools letting you down? See how SentryOne empowers Enterprises to go faster.

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}