QueryBuilder for inserting data

Hi all,

Can someone point me to why I can’t get building and running insert queries using yii\db\QueryBuilder to work?

The reason I’m using QueryBuilder is I’m in a migration script where I want to insert some default values in a table I’m making.

The migration script contains the following code:




      // Create the table

      $tableOptions = null;                                    

        if ($this->db->driverName === 'mysql') {                 

            // http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci         

            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';                                           

        }                                                        

                                                                 

        $this->createTable('{{%mediafiletype}}', [               

            'id' => Schema::TYPE_PK,                             

            'name' => Schema::TYPE_STRING . ' NOT NULL',         

            'mimetype' => Schema::TYPE_STRING . ' NOT NULL',     

            'extension' => Schema::TYPE_STRING . ' NOT NULL',    

            'CONSTRAINT uc_mediafiletype_name UNIQUE(name)',     

            'CONSTRAINT uc_mediafiletype_mimetype UNIQUE(mimetype)',                                                              

            'CONSTRAINT uc_mediafiletype_extension UNIQUE(extension)',                                                            

        ], $tableOptions);       


        // Create some default file types.                       

        $connection = Yii::$app->db;                             

                                                           

        $params = [                                              

            ':name' => 'PNG file',                               

            ':mimetype' => 'image/png',                          

            ':extension' => 'png'                                

        ];                                                       

        $connection->createCommand((new \yii\db\QueryBuilder($connection))                                                        

            ->insert('{{%mediafiletype}}', [                     

            'name' => ':name',                                   

            'mimetype' => ':mimetype',                           

            'extension' => ':extension',                         

        ], $params), $params)                                    

        ->execute();    



But when I run it I get the following error message:


Exception 'yii\db\Exception' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

The SQL being executed was: INSERT INTO `mediafiletype` (`name`, `mimetype`, `extension`) VALUES (':name', ':mimetype', ':extension')'

Can anyone tell me what I’m doing wrong?

Hi,

I know this is ages old, but it has not been answered and I’m facing the same issue. Have you been able to solve this?

Thanks.