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.

Zend Framework 2.x Learn ZF2 by Example: Protecting our Pages - Authentication type

I'm starting to implement authentication on my web app and I ran into a problem with the example in the book.

The example on page 146 uses MD5 as a encryption scheme.

$adapter = new DbAdapter($db,
                         'users',
                         'login',
                         'password',
                         'MD5(password)');

The adapter builds a simple SQL query that will check the table, match the user name and password then let you know if it's valid. The problem lies in the 5th parameter. When I tried to match an account I know works it kept saying the account wasn't valid. After digging into the documentation I found the 5th parameter wasn't configured right. It should be 'MD5(?)'.

$adapter = new DbAdapter($db,
                         'users',
                         'login',
                         'password',
                         'MD5(?)');

After the change the auth adapter works perfectly. Hope this helps save you some time.

Zend Framework 2 & MAMP : Memory Issues

I ran into a problem working through the tutorial for Learn ZF2 for form validation. I reached the section where the form data would be inserted into a database and that wasn't possible using the command line PHP server. I decided to switch to MAMP because it has more stuff baked in.

I quickly ran into an issue that was tough to track down. After added the database code the form no longer submitted. After ripping out lots of code and not finding an answer I called it a night. The next day I figured out the problem was with MAMP not my code. If I disabled the file validation on the form everything worked fine.

This was the error I ran into

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 32 bytes) in /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/Stdlib/ErrorHandler.php on line 113

Not the most straight forward bug. Turns out the issue is the command line PHP server has a larger memory_limit size. It's 128M while MAMP is set to 32M. It overflowed after processing the file. After upping memory_limit to 128M and restarting the server everything worked fine.

The memory_limit var is stored in php.ini. It can be reached by using
File => Edit template => php => php 5.4
Find memory_limit and change it to 128M.

Zend Framework 2.x Learn ZF2 by Example : Unable to render template

Getting to the MVC portion of the book and ran into an error. On page 59 it shows you how to manually include a template to aid debugging. I ran into this error when trying to see my changes.

Fatal error: Uncaught exception 'Zend\View\Exception\RuntimeException' with message 'Zend\View\Renderer\PhpRenderer::render: Unable to render template "debug/layout/sidebar"; resolver could not resolve to a file' in /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/View/Renderer/PhpRenderer.php:498 Stack trace: #0 /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/View/View.php(205): Zend\View\Renderer\PhpRenderer->render(Object(Zend\View\Model\ViewModel)) #1 /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/Mvc/View/Http/DefaultRenderingStrategy.php(102): Zend\View\View->render(Object(Zend\View\Model\ViewModel)) #2 [internal function]: Zend\Mvc\View\Http\DefaultRenderingStrategy->render(Object(Zend\Mvc\MvcEvent)) #3 /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(468): call_user_func(Array, Object(Zend\Mvc\MvcEvent)) #4 /Volumes/zend_framework/learnzf2/vendor/zendframe in /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/View/Renderer/PhpRenderer.php on line 498

Page 58 talks about the way templates are included. The middle of the page shows a config option called 'template_path_stack'. This is the path the PhpRender uses to find manually included templates. There is a similar line in the Application module.config.php. I thought this would be imported into the master config but that didn't happen. You have to add this.

Zend Framework 2.x Learn ZF2 by Example : Error creating Debug module

I'm rewriting part of my website with Zend Framework 2 because I want to use something more mature and flexible than something I wrote years ago. I picked up the book Learn ZF2 by Example because it was rated well. The intro gets your feet wet and eases you in. I got stuck when trying to reproduce the steps to create a new module using ZFTool.

On page 28 under Automatic Module Creation they want you to download a tool that will help with creating new modules. I'm not sure if the instructions are old but it not work. Running the command will give you the following error

Fatal error: Uncaught exception 'Zend\ModuleManager\Exception\RuntimeException' with message 'Module (ZFTool) could not be initialized.' in /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/ModuleManager/ModuleManager.php:189
Stack trace:
#0 /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/ModuleManager/ModuleManager.php(163): Zend\ModuleManager\ModuleManager->loadModuleByName(Object(Zend\ModuleManager\ModuleEvent))
#1 /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/ModuleManager/ModuleManager.php(90): Zend\ModuleManager\ModuleManager->loadModule('ZFTool')
#2 [internal function]: Zend\ModuleManager\ModuleManager->onLoadModules(Object(Zend\ModuleManager\ModuleEvent))
#3 /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(468): call_user_func(Array, Object(Zend\ModuleManager\ModuleEvent))
#4 /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/ in /Volumes/zend_framework/learnzf2/vendor/zendframework/zendframework/library/Zend/ModuleManager/ModuleManager.php on line 189

Your path will vary, but the error is the same. Searching google didn't give me a good answer. The ZFTool page has instructions on how to install the it using composer. When the install finished I ran the command php vendor/bin/zf.php create module Debug and the new module was created.

I hope this saves you some time.