Welcome to Abdul Malik Ikhsan's Blog

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

Posted in Tutorial PHP, Zend Framework 2 by samsonasik on March 19, 2014

zf2-zendframework2Zend 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