Yii v2 snippet guide III

  1. My articles
  2. Switching languages and Language in URL
  3. Search and replace
  4. Virtualization - Vagrant and Docker - why and how
  5. Running Yii project in Vagrant. (Simplified version)
  6. Running Yii project in Docker (Update: xDebug added below!)
  7. Enabling xDebug in Docker, yii demo application
  8. Docker - Custom php.ini
  9. How to enter Docker's bash (cli, command line)
  10. AdminLTE - overview & general research on the theme
  11. Creating custom Widget
  12. Tests - unit + functional + acceptance (opa) + coverage
  13. Microsoft Access MDB
  14. Migration batch insert csv

My articles

Articles are separated into more files as there is the max lenght for each file on wiki.

Switching languages and Language in URL

I already wrote how translations work. Here I will show how language can be switched and saved into the URL. So let's add the language switcher into the main menu:

echo Nav::widget([
 'options' => ['class' => 'navbar-nav navbar-right'],
 'items' => [
  ['label' => 'Language', 'items' => [
    ['label' => 'German' , 'url' => \yii\helpers\Url::current(['sys_lang' => 'de']) ],
    ['label' => 'English', 'url' => \yii\helpers\Url::current(['sys_lang' => 'en']) ],
   ],
  ]

Now we need to process the new GET parameter "sys_lang" and save it to Session in order to keep the new language. Best is to create a BaseController which will be extended by all controllers. Its content looks like this:

<?php
namespace app\controllers;
use yii\web\Controller;
class _BaseController extends Controller {
  public function beforeAction($action) {
    if (isset($_GET['sys_lang'])) {
      switch ($_GET['sys_lang']) {
        case 'de':
          $_SESSION['sys_lang'] = 'de-DE';
          break;
        case 'en':
          $_SESSION['sys_lang'] = 'en-US';
          break;
      }
    }
    if (!isset($_SESSION['sys_lang'])) {
      $_SESSION['sys_lang'] = \Yii::$app->sourceLanguage;
    }
    \Yii::$app->language = $_SESSION['sys_lang'];
    return true;
  }
}

If you want to have the sys_lang in the URL, right behind the domain name, following URL rules can be created in config/web.php:

'components' => [
 // ...
 'urlManager' => [
  'enablePrettyUrl' => true,
  'showScriptName' => false,
  'rules' => [
   // https://www.yiiframework.com/doc/api/2.0/yii-web-urlmanager#$rules-detail
   // https://stackoverflow.com/questions/2574181/yii-urlmanager-language-in-url
   // https://www.yiiframework.com/wiki/294/seo-conform-multilingual-urls-language-selector-widget-i18n
   '<sys_lang:[a-z]{2}>' => 'site',
   '<sys_lang:[a-z]{2}>/<controller:\w+>' => '<controller>',
   '<sys_lang:[a-z]{2}>/<controller:\w+>/<action:\w+>' => '<controller>/<action>',
  ],
 ],
],

Now the language-switching links will produce URL like this: http://myweb.com/en/site/index . Without the rules the link would look like this: http://myweb.com/site/index?sys_lang=en . So the rule works in both directions. When URL is parsed and controllers are called, but also when a new URL is created using the URL helper.

Search and replace

I am using Notepad++ for massive changes using Regex. If you press Ctrl+Shift+F you will be able to replace in all files.

Yii::t()

Yii::t('text'  ,  'text'   ) // NO
Yii::t('text','text') // YES

search: Yii::t\('([^']*)'[^']*'([^']*)'[^\)]*\)
replace with: Yii::t\('$1','$2'\)

URLs (in Notepad++) 

return $this->redirect('/controller/action')->send(); // NO
return $this->redirect(['controller/action'])->send(); // YES

search: ->redirect\(['][/]([^']*)[']\)
replace: ->redirect\(['$1']\)

====

return $this->redirect('controller/action')->send(); // NO
return $this->redirect(['controller/action'])->send(); // YES

search: ->redirect\((['][^']*['])\)
replace: ->redirect\([$1]\)

PHP short tags

search: (<\?)([^p=]) // <?if ...
replace: $1php $2 // <?php if ...
// note that sometimes <?xml can be found and it is valid, keep it

View usage

search: render(Ajax|Partial)?\s*\(\s*['"]\s*[a-z0-9_\/]*(viewName)

Virtualization - Vagrant and Docker - why and how

Both Vagrant and Docker create a virtual machine using almost any OS or SW configuration you specify, while the source codes are on your local disk so you can easily modify them in your IDE under your OS.

Can be used not only for PHP development, but in any other situation.

What is this good for? ... Your production server runs a particular environment and you want to develop/test on the same system. Plus you dont have to install XAMPP, LAMP or other servers locally. You just start the virtual and its ready. Plus you can share the configuration of the virtual system with other colleagues so you all work on indentical environment. You can also run locally many different OS systems with different PHP versions etc.

Vagrant and Docker work just like composer or NPM. It is a library of available OS images and other SW and you just pick some combination. Whole configuration is defined in one text-file, named Vagrantfile or docker-compose.yml, and all you need is just a few commands to run it. And debugging is no problem.

Running Yii project in Vagrant. (Simplified version)

Info: This chapter works with PHP 7.0 in ScotchBox. If you need PHP 7.4, read next chapter where CognacBox is used (to be added when tested)

Basic overview and Vagrant configuration:

List of all available OS images for Vagrant is here:

Both Yii demo-applications already contain the Vagrantfile, but its setup is unclear to me - it is too PRO. So I wanted to publish my simplified version which uses OS image named scotch/box and you can use it also for non-yii PHP projects. (It has some advantages, the disadvantage is older PHP in the free version)

The Vagrantfile is stored in the root-folder of your demo-project. My Vagrantfile contains only following commands.

Vagrant.configure("2") do |config|
    config.vm.box = "scotch/box"
    config.vm.network "private_network", ip: "11.22.33.44"
    config.vm.hostname = "scotchbox"
    config.vm.synced_folder ".", "/var/www/public", :mount_options => ["dmode=777", "fmode=777"]
    config.vm.provision "shell", path: "./vagrant/vagrant.sh", privileged: false
end

# Virtual machine will be available on IP A.B.C.D (in our case 11.22.33.44, see above)
# Virtual can access your host machine on IP A.B.C.1 (this rule is given by Vagrant)

It requires file vagrant/vagrant.sh, because I wanted to enhance the server a bit. It contains following:


# Composer:
# (In case of composer errors, it can help to delete the vendor-folder and composer.lock file)
cd /var/www/public/
composer install

# You can automatically import your SQL (root/root, dbname scotchbox)
#mysql -u root -proot scotchbox < /var/www/public/vagrant/db.sql

# You can run migrations:
#php /var/www/public/protected/yiic.php migrate --interactive=0

# You can create folder and set 777 rights:
#mkdir /var/www/public/assets
#sudo chmod -R 777 /var/www/public/assets

# You can copy a file:
#cp /var/www/public/from.php /var/www/public/to.php

# Installing Xdebug v2 (Xdebug v3 has renamed config params!):
sudo apt-get update
sudo apt-get install php-xdebug

# Configuring Xdebug in php.ini:
# If things do not work, disable your firewall and restart IDE. It might help.
echo "" | sudo tee -a /etc/php/7.0/apache2/php.ini
echo "[XDebug]" | sudo tee -a /etc/php/7.0/apache2/php.ini
echo "xdebug.remote_enable=1" | sudo tee -a /etc/php/7.0/apache2/php.ini
echo "xdebug.remote_port=9000" | sudo tee -a /etc/php/7.0/apache2/php.ini
echo "xdebug.remote_autostart=1" | sudo tee -a /etc/php/7.0/apache2/php.ini
echo "xdebug.remote_log=/var/www/public/xdebug.log" | sudo tee -a /etc/php/7.0/apache2/php.ini
echo "xdebug.remote_connect_back=1" | sudo tee -a /etc/php/7.0/apache2/php.ini
echo "xdebug.idekey=netbeans-xdebug" | sudo tee -a /etc/php/7.0/apache2/php.ini

# Important: Make sure that your IDE has identical settings: idekey and remote_port.
# NetBeans: Make sure your project is correctly setup. Right-click the project and select Properties / Run Cofigurations. "Project URL" and "Index file" must have correct values.

# Note:
# Use this if remote_connect_back does not work. 
# IP must correspond to the Vagrantfile, only the last number must be 1
#echo "xdebug.remote_handler=dbgp" | sudo tee -a /etc/php/7.0/apache2/php.ini
#echo "xdebug.remote_host=11.22.33.1" | sudo tee -a /etc/php/7.0/apache2/php.ini 

sudo service apache2 restart

... so create both files in your project ...

If you want to manually open php.ini and paste this text, you can copy it from here:

// sudo nano /etc/php/7.0/apache2/php.ini
// (Xdebug v3 has renamed config params!)

[XDebug]
xdebug.remote_enable=1
xdebug.remote_port=9000
xdebug.remote_autostart=1
xdebug.remote_log=/var/www/public/xdebug.log
xdebug.remote_connect_back=1
xdebug.idekey=netbeans-xdebug

// Important: Make sure that your IDE has identical settings: idekey and remote_port.
// NetBeans: Make sure your project is correctly setup. Right-click the project and select Properties / Run Cofigurations. "Project URL" and "Index file" must have correct values.

To debug in PhpStorm check this video.

To connect to MySQL via PhpStorm check this comment by MilanG

Installing and using Vagrant:

First install Vagrant and VirtualBox, please.

Note: Sadly, these days VirtualBox does not work on the ARM-based Macs with the M1 chip. Use Docker in that case.

Important: If command "vagrant ssh" wants a password, enter "vagrant".

Now just open your command line, navigate to your project and you can start:

  • "vagrant -v" should show you the version if things work.
  • "vagrant init" creates a new project (You won't need it now)
  • "vagrant up" runs the Vagrantfile and creates/starts the virtual

Once virtual is running, you can call also these:

  • "vagrant ssh" opens Linux shell - use password "vagrant" is you are prompted.
  • "vagrant halt" stops the virtual
  • "vagrant reload" restarts the virtual and does NOT run config.vm.provision OR STARTS EXISTING VAGRANT VIRTUAL - you do not have to call "vagrant up" whenever you reboot your PC
  • "vagrant reload --provision" restarts the virtual and runs config.vm.provision

In the Linux shell you can call any command you want.

  • To find what Linux version is installed: "cat /etc/os-release" or "lsb_release -a" or "hostnamectl"
  • To get PHP version call: "php -version"
  • If you are not allowed to run "mysql -v", you can run "mysql -u {username} -p" .. if you know the login
  • Current IP: hostname -I

In "scotch/box" I do not use PhpMyAdmin , but Adminer. It is one simple PHP script and it will run without any installations. Just copy the adminer.php script to your docroot and access it via browser. Use the same login as in configurafion of Yii. Server will be localhost.

Running Yii project in Docker (Update: xDebug added below!)

Note: I am showing the advanced application. Basic application will not be too different I think. Great Docker tutorial is here

Yii projects are already prepared for Docker. To start you only have to install Docker from www.docker.com and you can go on with this manual.

  • Download the application template and extract it to any folder
  • Open command line and navigate to the project folder
  • Run command docker-compose up -d
    • Argument -d will run docker on the background as a service
    • Advantage is that command line will not be blocked - you will be able to call more commands
  • Run command init to initialize the application
  • You can also call composer install using one of following commands:
    • docker-compose run --rm frontend composer install
    • docker-compose run --rm backend composer install

Note: init and composer can be called locally, not necessarily via Docker. They only add files to your folder.

Now you will be able to open URLs:

Open common/config/main-local.php and set following DB connection:

  • host=mysql !!
  • dbname=yii2advanced
  • username=yii2advanced
  • password=secret
  • Values are taken from docker-compose.yml

Run migrations using one of following commands:

  • docker-compose run --rm frontend php yii migrate
  • docker-compose run --rm backend php yii migrate

Now go to Frontend and click "signup" in the right upper corner

Second way is to directly modify table in DB:

  • Download adminer - It is a single-file DB client: www.adminer.org/en
  • Copy Adminer to frontend\web\adminer.php
  • Open Adminer using: http://localhost:20080/adminer.php
  • If your DB has no password, adminer fill refuse to work. You would have to "crack" it.
  • Use following login and go to DB yii2advanced:
  • server=mysql !!
  • username=yii2advanced
  • password=secret
  • Values are taken from docker-compose.yml
  • Set status=10 to your first user

Now you have your account and you can log in to Backend

Enabling xDebug in Docker, yii demo application

Just add section environment to docker-compose.yml like this:

services:

  frontend:
    build: frontend
    ports:
      - 20080:80
    volumes:
      # Re-use local composer cache via host-volume
      - ~/.composer-docker/cache:/root/.composer/cache:delegated
      # Mount source-code for development
      - ./:/app
    environment:
      PHP_ENABLE_XDEBUG: 1
      XDEBUG_CONFIG: "client_port=9000 start_with_request=yes idekey=netbeans-xdebug log_level=1 log=/app/xdebug.log discover_client_host=1"
      XDEBUG_MODE: "develop,debug"

This will allow you to see nicely formatted var_dump values and to debug your application in your IDE.

Note: You can/must specify the idekey and client_port based on your IDE settings. Plus your Yii project must be well configured in the IDE as well. In NetBeans make sure that "Project URL" and "index file" are correct in "Properties/Run Configuration" (right click the project)

Note 2: Please keep in mind that xDebug2 and xDebug3 have different settings. Details here.

I spent on this approximately 8 hours. Hopefully someone will enjoy it :-) Sadly, this configuration is not present in docker-compose.yml. It would be soooo handy.

Docker - Custom php.ini

Add into section "volumes" this line:

- ./myphp.ini:/usr/local/etc/php/conf.d/custom.ini

And create file myphp.ini the root of your Yii application. You can enter for example html_errors=on and html_errors=off to test if the file is loaded. Restart docker and check results using method phpinfo() in a PHP file.

How to enter Docker's bash (cli, command line)

Navigate in command line to the folder of your docker-project and run command:

  • docker ps
  • This will list all services you defined in docker-compose.yml

The last column of the list is NAMES. Pick one and copy its name. Then run command:

  • docker exec -it {NAME} /bin/bash
  • ... where {NAME} is your service name. For example:
  • docker exec -it yii-advanced_backend_1 /bin/bash

To findout what Linux is used, you can call cat /etc/os-release. (or check the Vagrant chapter for other commands)

If you want to locate the php.ini, type php --ini. Once you find it you can copy it to your yii-folder like this:

cp path/to/php.ini /app/myphp.ini

AdminLTE - overview & general research on the theme

AdminLTE is one of available admin themes. It currently has 2 versions:

  • AdminLTE v2 = based on Bootstrap 3 = great for Yii v2 application
  • AdminLTE v3 = based on Bootstrap 4 (it is easy to upgrade Yii2 from Bootstrap3 to Bootstrap4 *)

* Upgrading Yii2 from Bootstrap3 to Bootstrap4: https://www.youtube.com/watch?v=W1xxvngjep8

Documentation for AdminLTE <= 2.3, v2.4, v3.0 Note that some AdminLTE functionalities are only 3rd party dependencies. For example the map.

There are also many other admin themes:

There are also more Yii2 extensions for integration of AdminLTE into Yii project:

I picked AdminLTE v2 (because it uses the same Bootstrap as Yii2 demos) and I tested some extensions which should help with implementation.

But lets start with quick info about how to use AdminLTE v2 without extensions in Yii2 demo application.

Manual integration of v2.4 - Asset File creation

  • Open documentation and run composer or download all dependencies in ZIP.
  • Open preview page and copy whole HTML code to your text editor.
  • Delete those parts of BODY section which you do not need (at least the content of: section class="content")

  • Also delete all SCRIPT and LINK tags. We will add them using the AssetBundle later.

  • Open existing file views/layouts/main.php and copy important PHP calls to the new file. (Asset, beginPage, $content, Breadcrumbs etc)
  • Now your layout is complete, you can replace the original layout file.

We only need to create the Asset file to link all SCRIPTs and LINKs:

  • Copy file assets/AppAsset into assets/LteAsset and rename the class inside.
  • Copy all LINK- and SCRIPT- URLs to LteAsset.
  • Skip jQuery and Bootstrap, they are part of Yii. Example:
namespace app\assets;
use yii\web\AssetBundle;
class LteAsset extends AssetBundle
{
    public $sourcePath = '@vendor/almasaeed2010/adminlte/';
    public $jsOptions = ['position' => \yii\web\View::POS_HEAD];  // POS_END cause conflict with YiiAsset  
    public $css = [
        'bower_components/font-awesome/css/font-awesome.min.css',
        'https://fonts.googleapis.com/css?family=Source+Sans+Pro:300,400,600,700,300italic,400italic,600italic',
        // etc
    ];
    public $js = [
        'bower_components/jquery-ui/jquery-ui.min.js',
        // etc
    ];
    public $depends = [
        'yii\web\YiiAsset',
        'yii\bootstrap\BootstrapAsset',
    ];
}
  • Refresh your Yii page and check "developer tools" for network errors. Fix them.

This error can appear: "Headers already sent"

  • It means you forgot to copy some PHP code from the old layout file to the new one.

Now you are done, you can start using HTML and JS stuff from AdminLTE. So lets check extensions which will do it for us

Insolita extension

Works good for many UI items: Boxes, Tile, Callout, Alerts and Chatbox. You only have to prepare the main layout file and Asset bundle, see above. It hasn't been updated since 2018.

Check its web for my comment. I showed how to use many widgets.

Imperfections in the sources:

vendor\insolita\yii2-adminlte-widgets\LteConst.php

  • There is a typo: COLOR_LIGHT_BLUE should be 'lightblue', not 'light-blue'

vendor\insolita\yii2-adminlte-widgets\CollapseBox.php

  • Class in $collapseButtonTemplate should be "btn btn-box-tool", not "btn {btnType} btn-xs"
  • (it affects the expand/collapse button in expandable boxes)
  • $collapseButtonTemplate must be modified in order to enable removing Boxes from the screen. Namely data-widget and iconClass must be changed in method prepareBoxTools()

LteBox

  • Boxes can be hidden behind the "waiting icon" overlay. This is done using following HTML at the end of the box's div:
    <div class="overlay"><i class="fa fa-refresh fa-spin"></i></div>
  • This must be added manually or by modifying LteBox

Yiister

Its web explains everything. Very usefull: http://adminlte.yiister.ru You only need the Asset File from this article and then install Yiister. Sadly it hasn't been updated since 2015. Provides widgets for rendering Menu, GridView, Few boxes, Fleshalerts and Callouts. Plus Error page.

dmstr/yii2-adminlte-asset

Officially mentioned on AdminLTE web. Renders only Menu and Alert. Provides mainly the Asset file and Gii templates. Gii templates automatically fix the GridView design, but you can find below how to do it manually.

Other enhancements

AdminLTE is using font Source Sans Pro. If you want a different one, pick it on Google Fonts and modify the layout file like this:

<link href="https://fonts.googleapis.com/css2?family=Palanquin+Dark:wght@400;500;600;700&display=swap" rel="stylesheet">
<style>
 body {
    font-family: 'Palanquin Dark', 'Helvetica Neue', Helvetica, Arial, sans-serif;
  } 
  
  h1,h2,h3,h4,h5,h6,
  .h1,.h2,.h3,.h4,.h5,.h6 {
    font-family: 'Palanquin Dark', sans-serif;
  }
</style>

To display GridView as it should be, wrap it in this HTML code:

<div class="box box-primary">
  <div class="box-header">
    <h3 class="box-title"><i class="fa fa-table"></i>&nbsp;Grid caption</h3>
  </div>
  <div class="box-body"

  ... grid view ...

  </div>
</div>

You can also change the glyphicon in web/css/site.css:

a.asc:after {
    content: "\e155";
}

a.desc:after {
    content: "\e156";
}

And this is basically it. Now we know how to use AdminLTE and fix the GridView. At least one extension will be needed to render widgets, see above.

Creating custom Widget

See official reading about Widgets or this explanation. I am presenting this example, but I added 3 rows. Both types of Widgets can be coded like this:

namespace app\components;
use yii\base\Widget;
use yii\helpers\Html;

class HelloWidget extends Widget{
 public $message;
 public function init(){
  parent::init();
  if($this->message===null){
   $this->message= 'Welcome User';
  }else{
   $this->message= 'Welcome '.$this->message;
  }
  // ob_start();
  // ob_implicit_flush(false);
 }
 public function run(){
  // $content = ob_get_clean();
  return Html::encode($this->message); // . $content;
 }
}

// This widget is called like this:
echo HelloWidget::widget(['message' => ' Yii2.0']);

// After uncommenting my 4 comments you can use this
HelloWidget::begin(['message' => ' Yii2.0']);
echo 'My content';
HelloWidget::end();

Tests - unit + functional + acceptance (opa) + coverage

It is easy to run tests as both demo-applications are ready. Use command line and navigate to your project. Then type:

php ./vendor/bin/codecept run

This will run Unit and Functional tests. They are defined in folder tests/unit and tests/functional. Functional tests run in a hidden browser and do not work with JavaScript I think. In order to test complex JavaScript, you need Acceptance Tests. How to run them is to be found in file README.md or in documentation in both demo applications. If you want to run these tests in your standard Chrome or Firefox browser, you will need Java JDK and file selenium-server*.jar. See links in README.md. Once you have the JAR file, place is to your project and run it:

java -jar selenium-server-4.0.0.jar standalone

Now you can rerun your tests. Make sure that you have working URL of your project in file acceptance.suite.yml, section WebDriver. For example http://localhost/yii-basic/web. It depends on your environment. Also specify browser. For me works well setting "browser: chrome". If you receive error "WebDriver is not installed", you need to call this composer command:

composer require codeception/module-webdriver --dev

PS: There is also this file ChromeDriver but I am not really sure if it is an alternative to "codeception/module-webdriver" or when to use it. I havent studied it yet.

If you want to see the code coverage, do what is described in the documentation (link above). Plus make sure that your PHP contains xDebug! And mind the difference in settings of xDebug2 and xDebug3! If xDebug is missing, you will receive error "No code coverage driver available".

Microsoft Access MDB

Under Linux I haven't suceeded, but when I install a web server on Windows (for example XAMPP Server) I am able to install "Microsoft Access Database Engine 2016 Redistributable" and use *.mdb file.

So first of all you should install the web server with PHP and you should know wheather you are installing 64 or 32bit versions. Probably 64. Then go to page Microsoft Access Database Engine 2016 Redistributable (or find newer if available) and install corresponding package (32 vs 64bit).

Note: If you already have MS Access installed in the identical bit-version, you might not need to install the engine.

Then you will be able to use following DSN string in DB connection. (The code belongs to file config/db.php):

<?php

$file = "C:\\xampp\\htdocs\\Database1.mdb";

return [
  'class' => 'yii\db\Connection',
	
  'dsn' => "odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=$file;Uid=;Pwd=;",
  'username' => '',
  'password' => '',
  'charset' => 'utf8',
	
  //'schemaMap' => [
  //  'odbc'=> [
  //    'class'=>'yii\db\pgsql\Schema',
  //    'defaultSchema' => 'public' //specify your schema here
  //  ]
  //], 

  // Schema cache options (for production environment)
  //'enableSchemaCache' => true,
  //'schemaCacheDuration' => 60,
  //'schemaCache' => 'cache',
];

Then use this to query a table:

$data = Yii::$app->db->createCommand("SELECT * FROM TableX")->queryAll();
var_dump($data);

Note: If you already have MS Access installed in different bit-version then your PHP, you will not be able to install the engine in the correct bit-version. You must uninstall MS Access in that case.

Note2: If you do not know what your MDB file contains, Google Docs recommended me MDB, ACCDB Viewer and Reader and it worked.

Note3: There are preinstalled applications in Windows 10 named:

  • "ODBC Data Sources 32-bit"
  • "ODBC Data Sources 64-bit"
  • (Just hit the Win-key and type "ODBC")

Open the one you need, go to tab "System DSN" and click "Add". You will see what drivers are available - only these drivers can be used in the DSN String!!

If only "SQL Server" is present, then you need to install the Access Engine (or MS Access) with drivers for your platform. You need driver named cca "Microsoft Access Driver (*.mdb, *.accdb)"

In my case the Engine added following 64bit drivers:

  • Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)
  • Microsoft Access Driver (*.mdb, *.accdb)
  • Microsoft Access Text Driver (*.txt, *.csv)
  • Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

And how about Linux ?

You need the MS Access Drivers as well, but Microsoft does not provide them. There are some 3rd party MdbTools or EasySoft, but their are either not-perfect or expensive. Plus there is Unix ODBC.

For Java there are Java JDBC, Jackcess and Ucanaccess.

And how about Docker ? As far as I know you cannot run Windows images under Linux so you will not be able to use the ODBC-advantage of Windows in this case. You can use Linux images under Windows, but I think there is no way how to access the ODBC drivers from virtual Linux. You would have to try it, I haven't tested it yet.

Migration batch insert csv

If you want to import CSV into your DB in Yii2 migrations, you can create this "migration base class" and use it as a parent of your actual migration. Then you can use method batchInsertCsv().

<?php

namespace app\components;

use yii\db\Migration;

class BaseMigration extends Migration
{
    /**
     * @param $filename Example: DIR_ROOT . DIRECTORY_SEPARATOR . "file.csv"
     * @param $table The target table name
     * @param $csvToSqlColMapping [csvColName => sqlColName] (if $containsHeaderRow = true) or [csvColIndex => sqlColName] (if $containsHeaderRow = false)
     * @param bool $containsHeaderRow If the header with CSV col names is present
     * @param int $batchSize How many rows will be inserted in each batch
     * @throws Exception
     */
    public function batchInsertCsv($filename, $table, $csvToSqlColMapping, $containsHeaderRow = false, $batchSize = 10000, $separator = ';')
    {
        if (!file_exists($filename)) {
            throw new \Exception("File " . $filename . " not found");
        }

        // If you see number 1 in first inserted row and column, most likely BOM causes this.
        // Some Textfiles begin with 239 187 191 (EF BB BF in hex)
        // bite order mark https://en.wikipedia.org/wiki/Byte_order_mark
        // Let's trim it on the first row.
        $bom = pack('H*', 'EFBBBF');

        $handle = fopen($filename, "r");
        $lineNumber = 1;
        $header = [];
        $rows = [];
        $sqlColNames = array_values($csvToSqlColMapping);
        $batch = 0;

        if ($containsHeaderRow) {
            if (($raw_string = fgets($handle)) !== false) {
                $header = str_getcsv(trim($raw_string, $bom), $separator);
            }
        }

        // Iterate over every line of the file
        while (($raw_string = fgets($handle)) !== false) {
            $dataArray = str_getcsv(trim($raw_string, $bom), $separator);

            if ($containsHeaderRow) {
                $dataArray = array_combine($header, $dataArray);
            }

            $tmp = [];
            foreach ($csvToSqlColMapping as $csvCol => $sqlCol) {
                $tmp[] = trim($dataArray[$csvCol]);
            }
            $rows[] = $tmp;

            $lineNumber++;
            $batch++;

            if ($batch >= $batchSize) {
                $this->batchInsert($table, $sqlColNames, $rows);
                $rows = [];
                $batch = 0;
            }
        }
        fclose($handle);

        $this->batchInsert($table, $sqlColNames, $rows);
    }
}
8 0
4 followers
Viewed: 191 027 times
Version: 2.0
Category: Tutorials
Tags: beginer, tutorial, yii2
Written by: rackycz
Last updated by: rackycz
Created on: Jan 21, 2021
Last updated: a year ago
Update Article

Revisions

View all history

Related Articles