Yii Framework Wiki
Difference between
#70 and
#110 of
Yii v2 snippet guide III
Changes
Title unchanged
Yii v2 snippet guide III
Category unchanged
Tutorials
Yii version unchanged
2.0
Tags unchanged
tutorial,yii2,beginer
Content changed
[...]
* [Yii v1 for beginners](https://www.yiiframework.com/wiki/250/yii-for-beginners)* [Yii v1 for beginners 2](https://www.yiiframework.com/wiki/462/yii-for-beginners-2)
* [Yii v2 snippet guide I](https://www.yiiframework.com/wiki/2552/yii-v2-snippet-guide)
* [Yii v2 snippet guide II](https://www.yiiframework.com/wiki/2558/yii-v2-snippet-guide-ii)
* [Yii v2 snippet guide III](https://www.yiiframework.com/wiki/2567/yii-v2-snippet-guide-iii)
**Switching languages and Language in URL**
---
I already [wrote](https://www.yiiframework.com/wiki/2552/yii-v2-snippet-guide#i18n-translations) 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:
```php
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
<?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:
```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:
- https://app.vagrantup.com/boxes/search
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](https://box.scotch.io) 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:
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]
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](https://youtu.be/8r77ys2r4ww?t=450).
To connect to MySQL via PhpStorm check [this comment by MilanG](https://stackoverflow.com/questions/30823844/connect-to-mysql-database-on-vagrant-machine-in-phpstorm/43546183)
**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".
- Install [Virtual Box](https://www.virtualbox.org/wiki/Downloads), I recommend to install also the "Extension Pack", but is might be done automatically by "scotch/box".
- Install [Vagrant](https://www.vagrantup.com/downloads) ... on Windows restart is needed :-(
- https://www.sitepoint.com/re-introducing-vagrant-right-way-start-php/
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](https://www.adminer.org/en). 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](https://www.sitepoint.com/docker-php-development-environment/)*
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:
- Frontend: http://localhost:20080
- Backend: http://localhost:21080
- ... see docker-compose.yml to understand these port numbers
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
- This will create a new user and will send an email. It will appear in folder frontend/runtime/mail
- Now you have to activate it. First way is to use the email
- Open the EML file in frontend/runtime/mail
- copy whole href which is inside and modify it by changing these substrings: "=3D" "=" "&" "%2F"
- Original format:
- http://l=ocalhost:20080/index.php?r=3Dsite%2Fverify-email&token=3D07tYL8tqNjsyr8=eZBoN_mXOgwtq1XqvB_1614901373
- Desired format:
- http://localhost:20080/index.php?r=site/verify-email&token=07tYL8tqNjsyr8eZBoN_mXOgwtq1XqvB_1614901373
- If the link is correct, activation will succeed
Second way is to directly modify table in DB:
- Download adminer - It is a single-file DB client: [www.adminer.org/en](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](https://xdebug.org/docs/).
> 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](https://adminlte.io/) is one of available admin themes. It currently has 2 versions:
- [AdminLTE v2](https://adminlte.io/themes/dev/AdminLTE/index3.html) = based on Bootstrap 3 = great for Yii v2 application
- [AdminLTE v3](https://adminlte.io/themes/AdminLTE/index2.html) = 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](https://adminlte.io/themes/AdminLTE/documentation), [v2.4](https://adminlte.io/docs/2.4/installation), [v3.0](https://adminlte.io/docs/3.0)
Note that some AdminLTE functionalities are only 3rd party dependencies. For example the map.
There are also many other admin themes:
- https://startbootstrap.com/theme/sb-admin-2 - nice tutorial for integration to yii2 is on [YouTube](https://youtu.be/eQdDBhQpU9o?t=1554)
- https://www.youtube.com/watch?v=CNQgmhjMhhM
- https://www.coderseden.com/product/material-dashboard-yii2
- and others, see [Google](https://www.google.com/search?q=html+template+administration)
There are also more Yii2 extensions for integration of AdminLTE into Yii project:
- https://www.yiiframework.com/extension/insolita/yii2-adminlte-widgets
- http://adminlte.yiister.ru/site/boxes
- https://www.yiiframework.com/extension/hail812/yii2-adminlte3 (composer installation failed)
- https://www.yiiframework.com/extension/yii2-adminlte-asset, (git)[https://github.com/dmstr/yii2-adminlte-asset]
- https://github.com/yidas/yii2-adminlte
- https://codeclimate.com/github/cjtterabytesoft/yii2-adminlte-basic/widgets/MainSidebar.php/source
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](https://adminlte.io/docs/2.4/installation) and run composer or download all dependencies in ZIP.
- Open [preview page](https://adminlte.io/themes/AdminLTE/index2.html) 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 yii-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:
```php
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.
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.
**Changing AdminLTE font**
---
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:
```html
<div class="box box-primary">
<div class="box-header">
<h3 class="box-title"><i class="fa fa-table"></i> 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.
**Switching languages and Language in URL**
---
I already [wrote](https://www.yiiframework.com/wiki/2552/yii-v2-snippet-guide#i18n-translations) 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:
```php
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
<?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:
```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:
- https://app.vagrantup.com/boxes/search
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](https://box.scotch.io) 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](https://youtu.be/8r77ys2r4ww?t=450).
To connect to MySQL via PhpStorm check [this comment by MilanG](https://stackoverflow.com/questions/30823844/connect-to-mysql-database-on-vagrant-machine-in-phpstorm/43546183)
**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".
- Install [Virtual Box](https://www.virtualbox.org/wiki/Downloads), I recommend to install also the "Extension Pack", but is might be done automatically by "scotch/box".
- Install [Vagrant](https://www.vagrantup.com/downloads) ... on Windows restart is needed :-(
- https://www.sitepoint.com/re-introducing-vagrant-right-way-start-php/
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](https://www.adminer.org/en). 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](https://www.sitepoint.com/docker-php-development-environment/)*
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:
- Frontend: http://localhost:20080
- Backend: http://localhost:21080
- ... see docker-compose.yml to understand these port numbers
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
- This will create a new user and will send an email. It will appear in folder frontend/runtime/mail
- Now you have to activate it. First way is to use the email
- Open the EML file in frontend/runtime/mail
- copy whole href which is inside and modify it by changing these substrings: "=3D" "=" "&" "%2F"
- Original format:
- http://l=ocalhost:20080/index.php?r=3Dsite%2Fverify-email&token=3D07tYL8tqNjsyr8=eZBoN_mXOgwtq1XqvB_1614901373
- Desired format:
- http://localhost:20080/index.php?r=site/verify-email&token=07tYL8tqNjsyr8eZBoN_mXOgwtq1XqvB_1614901373
- If the link is correct, activation will succeed
Second way is to directly modify table in DB:
- Download adminer - It is a single-file DB client: [www.adminer.org/en](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](https://xdebug.org/docs/).
> 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](https://adminlte.io/) is one of available admin themes. It currently has 2 versions:
- [AdminLTE v2](https://adminlte.io/themes/dev/AdminLTE/index3.html) = based on Bootstrap 3 = great for Yii v2 application
- [AdminLTE v3](https://adminlte.io/themes/AdminLTE/index2.html) = 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](https://adminlte.io/themes/AdminLTE/documentation), [v2.4](https://adminlte.io/docs/2.4/installation), [v3.0](https://adminlte.io/docs/3.0)
Note that some AdminLTE functionalities are only 3rd party dependencies. For example the map.
There are also many other admin themes:
- https://startbootstrap.com/theme/sb-admin-2 - nice tutorial for integration to yii2 is on [YouTube](https://youtu.be/eQdDBhQpU9o?t=1554)
- https://www.youtube.com/watch?v=CNQgmhjMhhM
- https://www.coderseden.com/product/material-dashboard-yii2
- and others, see [Google](https://www.google.com/search?q=html+template+administration)
There are also more Yii2 extensions for integration of AdminLTE into Yii project:
- https://www.yiiframework.com/extension/insolita/yii2-adminlte-widgets
- http://adminlte.yiister.ru/site/boxes
- https://www.yiiframework.com/extension/hail812/yii2-adminlte3 (composer installation failed)
- https://www.yiiframework.com/extension/yii2-adminlte-asset, [git](https://github.com/dmstr/yii2-adminlte-asset)
- https://github.com/yidas/yii2-adminlte
- https://codeclimate.com/github/cjtterabytesoft/yii2-adminlte-basic/widgets/MainSidebar.php/source
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](https://adminlte.io/docs/2.4/installation) and run composer or download all dependencies in ZIP.
- Open [preview page](https://adminlte.io/themes/AdminLTE/index2.html) 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:
```php
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](https://www.yiiframework.com/extension/insolita/yii2-adminlte-widgets) 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:
```html
<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:
```html
<div class="box box-primary">
<div class="box-header">
<h3 class="box-title"><i class="fa fa-table"></i> 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](https://www.yiiframework.com/doc/guide/2.0/en/structure-widgets) or this [explanation](https://www.codevoila.com/post/33/yii2-example-create-yii2-custom-widget-advanced-part). I am presenting [this example](http://www.bsourcecode.com/yiiframework2/how-to-create-custom-widget-in-yii2-0-framework/), but I added 3 rows. Both types of Widgets can be coded like this:
```php
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
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](https://github.com/yiisoft/yii2-app-basic/blob/master/README.md#testing) in both demo applications. If you want to run these tests in your standard Chrome or Firefox browser, you will need [Java JDK](https://www.oracle.com/java/technologies/downloads/) and file [selenium-server*.jar](https://www.selenium.dev/downloads). See links in README.md. Once you have the JAR file, place is to your project and run it:
```php
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:
```php
composer require codeception/module-webdriver --dev
```
PS: There is also this file [ChromeDriver](https://chromedriver.chromium.org/downloads) 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](https://gist.github.com/odan/1abe76d373a9cbb15bed)! 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](https://www.microsoft.com/en-us/download/details.aspx?id=54920) (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](https://mdbviewer.herokuapp.com) 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](https://github.com/mdbtools/mdbtools) or [EasySoft](https://www.easysoft.com/products/data_access/odbc-access-driver/index.html#section=tab-1), but their are either not-perfect or expensive. Plus there is [Unix ODBC](http://www.unixodbc.org/).
For Java there are [Java JDBC](http://www.hxtt.com/access.html), [Jackcess](https://jackcess.sourceforge.io/) and [Ucanaccess](http://ucanaccess.sourceforge.net/site.html).
**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
<?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);
}
}
```
4 followers
Viewed: 191 444 times
Version: 2.0
Category: Tutorials
Written by: rackycz
Last updated by: rackycz
Created on: Jan 21, 2021
Last updated: a year ago
Revisions
View all history-
a year ago by
rackycz Migration CSV
-
a year ago by
rackycz Migration CSV
-
2 years ago by
rackycz Microsoft Access MDB
-
2 years ago by
rackycz Microsoft Access MDB
-
2 years ago by
rackycz Microsoft Access MDB
-
2 years ago by
rackycz Microsoft Access MDB
-
2 years ago by
rackycz Microsoft Access MDB
-
2 years ago by
rackycz Microsoft Access MDB
-
2 years ago by
rackycz Microsoft Access MDB
-
2 years ago by
rackycz Microsoft Access MDB