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

About these ads

5 Responses

Subscribe to comments with RSS.

  1. seyferx said, on March 20, 2014 at 5:25 pm

    Please, write about how to use doctrine migrations with Zend2.

    Now i’m found just that module

    https://github.com/vgarvardt/ZfSimpleMigrations

  2. Azhar said, on August 13, 2014 at 6:33 pm

    hi
    i am using the below code to insert the values into the database but it is not inserting
    can you help me ??

    $con=$this->adapter;//connection to the database;
    $sql = new Sql($con);
    for($i=0;$iinsert();
    $insert->into(‘section_questions’);
    $insert->columns(array(‘topic_group_id’,’section_question’,’section_id’));
    $insert->values(array(‘topic_group_id’=>$data[$i][0]['topic_group_id'],
    ‘section_question’=>$data[$i+1][0]['section_question'],
    ‘section_id’ => $data['section']),$insert :: VALUES_MERGE );
    }

  3. Azhar said, on August 13, 2014 at 7:49 pm

    this is my new code to insert data but its not working .

    $insert = $sql->insert();
    $insert->into(‘section_questions’);
    $insert->columns(array(‘topic_group_id’,’section_question’,’section_id’));
    $insert->values(array(‘topic_group_id’=>$data[$i][0]['topic_group_id'],
    ‘section_question’=>$data[$i+1][0]['section_question'],
    ‘section_id’ => $data['section']),$insert :: VALUES_MERGE );

  4. Azhar said, on August 13, 2014 at 7:55 pm

    i solve it


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 264 other followers

%d bloggers like this: