Correct way to query a MySql database in Zend Framework 2

Question

After a long struggle and research, I have finally been able to query a database in mysql table using zend framework 2.

I am using the Autoloader. Here is the code:

require_once 'Zend/Loader/StandardAutoloader.php';
    $loader = new Zend\Loader\StandardAutoloader(array('autoregister_zf' => true));

    $loader->register();

    use Zend\Db\Adapter\Adapter;


    $params = array(
        'driver'=> 'Pdo_Mysql',
        'hostname' => 'localhost',
        'username' => '*****',
        'password' => '*****',
        'dbname' => '*****'
    );

    try{

       $adapter = new Adapter($params);

    }
    catch (Exception $e){
        echo $e->getMessage();
    }

    $sql = 'select * from user';

    $rs = $adapter->createStatement($sql)->execute(array(100,100));

    print_r(iterator_to_array($rs));

    echo $rs->count();

I want to know what is the correct way and best practice to query a mysql table. It used to be much easier in ZF1 but now I understand that ZF2 is much more object oriented.

I want to optimise performance and good coding. Can you tell me if what I did is good and what is the best way to query mysql database when using ZF2 as there will be a lot of querying to do.

Greco Jonathan · Answer 1 · 2015-10-08T10:33:52.337

First of all.

After a long struggle and research

I disagree, with you. You can find example of what your search everywhere.

You're code is not respecting the OOP approach at all, it's not even respect Zf2 standard coding so you have to break this. I also recommand to use Zend skeleton application to show you how to use Zf2 with a standard architecture.

For answering your question : Zf2 has a config part to configure connection params. You should use it, it's the best practise according to the documentation itself.

This complete tutorial About Database and model will help you for sure.

In this tutorial, you can see a sample of wich you connection configuration should look in a config file

//db.config.php
return array(
     'db' => array(
         'driver'         => 'Pdo',
         'dsn'            => 'mysql:dbname=[yourDb_name];host=localhost',
         'driver_options' => array(
             PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
         ),
     ),
     'service_manager' => array(
         'factories' => array(
             'Zend\Db\Adapter\Adapter'
                     => 'Zend\Db\Adapter\AdapterServiceFactory',
         ),
     ),
 );

Then from here you can also specify in another config file your credential for security and/or environment issues/ facilities

 //credential.config.local.php
 return array(
     'db' => array(
         'username' => 'YOUR USERNAME HERE',
         'password' => 'YOUR PASSWORD HERE',
     ),
 );

All config file are merged. Use it !

With following this tutorial you'll be able to do your model layer and interact with your database in OOP approach. Your have to use design pattern like MVC pattern, and some other according to your needs. But if you want to respect zf2 coding standards, it's the only way !

Correct way to query a MySql database in Zend Framework 2

1 Answers1