Zend Framework 2 : Using INSERT INTO … SELECT with Zend\Db

Zend Framework 2.3.0 released with dozen of features. One of feature on Zend\Db is we can make insert using select instance. It can make life easier when you have a situation, for example : to ‘copy’ the content of some table to other table, for example : migration with some criteria need to be achieved. Here we go :

//this example is when we use component instead of full stack framework
include './vendor/autoload.php';

use Zend\Db\Adapter\Adapter;
use Zend\Db\Sql\Insert;
use Zend\Db\Sql\Sql;

$adapterSelect = new Zend\Db\Adapter\Adapter(array(
    'driver'   => 'pdo_mysql',
    'database' => 'DbSELECT',
    'username' => 'root',
    'password' => ''
));

$adapterInsert = new Zend\Db\Adapter\Adapter(array(
    'driver'   => 'pdo_mysql',
    'database' => 'DbINTO',
    'username' => 'root',
    'password' => ''
));

/**
 * php 5.4 code, for php 5.3 code, you can extract with
   $selectedtable = new Zend\Db\TableGateway\TableGateway('tableneedtobeselected', $adapterSelect);
   $select = $selectedtable->getSql()->select()->where(array('field' => 'value'));
 */
$select = (new Zend\Db\TableGateway\TableGateway('tableneedtobeselected', $adapterSelect))
                ->getSql()->select()
                ->where(array('field' => 'value'));

/**
 * table that need to be inserted
 */
$tableToBeInserted = new Zend\Db\TableGateway\TableGateway('tablenamewillinserted', $adapterInsert);

//insert with select
$tableToBeInserted->insert($select);

Done 😉

References :
1. http://framework.zend.com/blog/zend-framework-2-3-0-released.html