Developing software in the Real World

Displaying the generated SQL from a ZendDbSql object

If you use ZendDbSql to generate your SQL, then it’s useful to find out what the generated SQL looks like.

Consider code like this:

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

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:

So we now get:

Expected SQL string

This is much better!

3 thoughts on “Displaying the generated SQL from a ZendDbSql object

  1. I'm using zend2.2 and using the statement->getSql() command to view the generated sql, but find that it is not printing out the fully generated sql.

    I do the following:
    $sql = "SELECT * FROM mytable where code = :code and name = ':name'"
    $params = array(
    $statement = $dbAdapter->createStatement($sql, $params);
    echo $statement->getSql();
    // prints out: SELECT * FROM mytable where code = :code and name = ':name'

    My reasoning is the following:
    > I like the freedom of writing a sql statement – rather than building a sql statement like you have done above with the select() where() constructs
    > I want the protection of sql injection + improved performance by using prepared statements
    > I want to have minimal abstraction layers in my php-sql development & would like a direct way to view my generated sql (not logger plugin)

    Its great that ->getSql() exists, but not great that it doesn't print out the generated code. Then I would straight-out include my parameters in the sql … but then it seems I will lose out on the performance / protection of prepared statements.

    I am fairly new to Zend & still trying to find the right way forward in my php-sql logic that I don't find too abstracted.

  2. This is now effected by If you define the platform as described above you will still get a notice because it is attempting to use the platforms quoteValue() method. There is not currently a way to set it so that the getSqlString() method will use the quoteTrustedValue() method to build the string and return it. Because of this the only way I could find to get the raw sql string was to suppress the notice as follows:


Thoughts? Leave a reply

Your email address will not be published. Required fields are marked *