Zend Framework 2: Using mysql functions in SQL classes

The built in ZF2 classes for sql queries have been wonderful to use. I like that it takes care of escaping data and saves you from messy string all over your codebase. I ran into a problem when trying to do something "complex".

Here is an example of the SQL I was trying to write.

INSERT into `table` SET `col1` = FROM_UNIXTIME(?)

When I tried running this the mysql method was quoted

`FROM_UNIXTIME(?)`

I started digging around and found an extra class required if you want to use functions in the escaped data spot. It's called an expression. Here is how they're used in the insert class.

$insert->into($tableName)
       ->values([`col` => new \Zend\Db\Sql\Expression("FROM_UNIXTIME(?)","?", [\Zend\Db\Sql\Expression::TYPE_VALUE])]);

Now the function won't be quoted and the value can be safely escaped when execute is run.


Here's some documentation on how expressions are used in a where clause.