Tag: zend-framework2
Nabin Nepal (Starx) json mysql php zend-framework2
April 9, 2013

How to apply WHERE clause again array or jscon encoded value

M4l33n’s Questions:

m4l33nm4l33n

Values are stored under company_id like 

["2"]
["2", "1"]

where 2 and 1 are the IDs of companies. Now i want all result of ID 2. How can i fire query again json encoded data.

select * from tbl where company_id = 2

This is what I need to perform.

For more info, json format is the result of dynamic (fill by database values) Select List in Zend2.

And what if it was array instead of json, how this simple select query can be executed.

M8R-1jmw5rM8R-1jmw5r

As each ID is a number inside double quotes you can just query with a LIKE statement:

select * from tbl where company_id LIKE '%"2"%'

And what if it was array instead of json, how this simple select query can be executed.

You can store JSON into a mysql database because it is a string. But an array is not a string, so you can not store it there. You would need to serialize it but for that you should prefer JSON.

The alternative would be to create a table that stores the relationships to the companies.

StarxStarx

MySQL has a datatype called SET You can use this data type on this task. This will allow you to enter comma separated values and still be able to query as per single item.

Manual: http://dev.mysql.com/doc/refman/5.0/en/set.html

Your query will have to be updated to something like this:

SELECT * FROM tbl_name WHERE FIND_IN_SET('2', company_id);

But, this problems arises because the tables are not normalized properly. Adding a reference table and querying this reference table will be a much better option, when the application reaches large-scale.

Nabin Nepal (Starx) phpunit zend-framework2
March 31, 2013

ZF2 Unit test album module returns routing issue

Question by Beniston

BenistonBeniston

I am trying out the phpunit in the Zf2 album module. I encountered an error which states about routing.

Below is the debug information. It says ‘Route with name “album” not found’, but when I checked module.config.php in the album module folder, I see that is correctly set and in the browser the redirection to that route is working fine.

AlbumControllerAlbumControllerTest::testDeleteActionCanBeAccessed
ZendMvcRouterExceptionRuntimeException: Route with name "album" not found
D:wwwzend2vendorzendframeworkzendframeworklibraryZendMvcRouterSimpleRouteStack.php:292
D:wwwzend2vendorzendframeworkzendframeworklibraryZendMvcControllerPluginUrl.php:88
D:wwwzend2vendorzendframeworkzendframeworklibraryZendMvcControllerPluginRedirect.php:54
D:wwwzend2moduleAlbumsrcAlbumControllerAlbumController.php:80
D:wwwzend2vendorzendframeworkzendframeworklibraryZendMvcControllerAbstractActionController.php:87
D:wwwzend2vendorzendframeworkzendframeworklibraryZendEventManagerEventManager.php:468
D:wwwzend2vendorzendframeworkzendframeworklibraryZendEventManagerEventManager.php:208
D:wwwzend2vendorzendframeworkzendframeworklibraryZendMvcControllerAbstractController.php:108
D:wwwzend2testsmoduleAlbumsrcAlbumControllerAlbumControllerTest.php:35
C:wampbinphpphp5.4.3phpunit:46

I understand that the issue in AlbumController.php line 80 is 

return $this->redirect()->toRoute('album');

But not sure why it is not working. Any one has encountered and overcome such issues?

Answer by dave b.

dave b.dave b.

I hope it will save approx. 30 minutes of searching in the zend framework 2 code:

class AlbumControllerTest extends PHPUnit_Framework_TestCase
{

//...

    protected function setUp()
    {
        $bootstrap        = ZendMvcApplication::init(include 'config/application.config.php');
        $this->controller = new AlbumController();
        $this->request    = new Request();
        $this->routeMatch = new RouteMatch(array('controller' => 'index'));
        $this->event      = $bootstrap->getMvcEvent();

        $router = new ZendMvcRouterSimpleRouteStack();
        $options = array(
                    'route'    => '/album[/:action][/:id]',
                    'constraints' => array(
                        'action' => '[a-zA-Z][a-zA-Z0-9_-]*',
                        'id'     => '[0-9]+',
                    ),
                    'defaults' => array(
                        'controller' => 'AlbumControllerAlbum',
                        'action'     => 'index',
                    ),
            );
        $route = ZendMvcRouterHttpSegment::factory($options);

        $router->addRoute('album', $route);

        $this->event->setRouter($router);
        $this->event->setRouteMatch($this->routeMatch);
        $this->controller->setEvent($this->event);
        $this->controller->setEventManager($bootstrap->getEventManager());
        $this->controller->setServiceLocator($bootstrap->getServiceManager());
    }

}

Answer by Starx

StarxStarx

Actually the easy way is to get the config data from the service manager:

$config = $serviceManager->get('Config');

Full code for the function setUp():

protected function setUp() {
    $serviceManager = Bootstrap::getServiceManager();
    $this -> controller = new AlbumController();
    $this -> request = new Request();
    $this -> routeMatch = new RouteMatch(
        array(
            'controller' => 'index',
        )
    );
    $this -> event = new MvcEvent();
    $config = $serviceManager->get('Config');
    $routerConfig = isset($config['router']) ? $config['router']  : array();
    $router = HttpRouter::factory($routerConfig);
    $this -> event -> setRouter($router);
    $this -> event -> setRouteMatch($this -> routeMatch);
    $this -> controller -> setEvent($this -> event);
    $this -> controller -> setServiceLocator($serviceManager);
}
...

Please fill the form - I will response as fast as I can!