Yii v2 snippet guide II

Tutorials

2.0

tutorial,beginner,yii2,snippets

**Intro**

---

Hi. I had to split my article as max length was reached. Check my previous articles here:

- [https://www.yiiframework.com/wiki/2552/yii-v2-snippet-guide](https://www.yiiframework.com/wiki/2552/yii-v2-snippet-guide)

- [https://www.yiiframework.com/wiki/250/yii-for-beginners](https://www.yiiframework.com/wiki/250/yii-for-beginners)

- [https://www.yiiframework.com/wiki/462/yii-for-beginners-2](https://www.yiiframework.com/wiki/462/yii-for-beginners-2)

**Connection to MSSQL**

---

You might need MSSQL drivers in PHP. Based on your system you have to download different driver. The differences are x64 vs x86 and ThreadSafe vs nonThreadSafe. In Windows I always use ThreadSafe.

[Explanation](https://www.php.net/manual/en/faq.obtaining.php#faq.obtaining.threadsafety).

Newest PHP drivers are [here](https://docs.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server).

- Drivers v5.8 = PHP 7.2 - 7.4

Older PHP drivers [here](https://www.microsoft.com/en-us/download/details.aspx?id=20098).

- Drivers v4.0 = PHP 7.0 - 7.1

- Drivers v3.2 = PHP 5.x

Once drivers are downloaded and extracted, pick one DLL file and place it into folder "php/ext". On Windows it might be for example here: "C:\xampp\php\ext"

**Note:**

In some situations you could also need [these OBDC drivers](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server), but I am not sure when:

Now file **php.ini** must be modified. On Windows it might be placed here: "C:\xampp\php\php.ini". Open it and search for rows starting with word "extension" and paste there cca this:

```php

extension={filename.dll}

// Example:

extension=php_pdo_sqlsrv_74_ts_x64.dll

```

Now restart Apache and visit phpinfo() web page. You should see section "pdo_sqlsrv". If you are using XAMPP, it might be on this URL: [http://localhost/dashboard/phpinfo.php](http://localhost/dashboard/phpinfo.php).

Then just add connection to your MSSQL DB in Yii2 config. In my case the database was remote so I needed to create 2nd DB connection. Read next chapter how to do it.

**Using MSSQL database as the 2nd DB in the Yii2 project**

---

Adding 2nd database is done like this in yii-config:

```php

'db' => $db, // the original DB

'db2'=>[

  'class' => 'yii\db\Connection',

  'driverName' => 'sqlsrv',

  // I was not able to specify database like this:

  // 'dsn' => 'sqlsrv:Server={serverName};Database={dbName}',

  'dsn' => 'sqlsrv:Server={serverName}',

  'username' => '{username}',

  'password' => '{pwd}',

  'charset' => 'utf8',

],

```

That's it. Now you can test your DB like this:

```php

$result = Yii::$app->db2->createCommand('SELECT * FROM {tblname}')->queryAll();

var_dump($result);

```

Note that in MSSQL you can have longer table names. Example: CATEGORY.SCHEMA.TBL_NAME

And your first test-model can look like this (file MyMsModel.php):

```php

namespace app\models;

use Yii;

use yii\helpers\ArrayHelper;

class MyMsModel extends \yii\db\ActiveRecord

{

  public static function getDb()

  {

    return \Yii::$app->db2;

  }

  public static function tableName()

  {

    return 'CATEGORY.SCHEMA.TBL_NAME';

  }

}

```

Usage:

```php

$result = MyMsModel::find()->limit(2)->all();

var_dump($result);

```

**Creating models in Gii for remote MSSQL tables**

---

Once you have added the 2nd database (read above) go to the Model Generator in Gii. Change there the DB connection to whatever you named the connection in yii-config (in the example above it was "db2") and set tablename in format: SCHEMA.TBL_NAME. If MSSQL server has more databases, one of them is set to be the main DB. This will be used I think. I haven't succeeded to change the DB. DB can be set in the DSN string, but it had no effect in my case## My articles

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

* [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)

* [Začínáme s PHP frameworkem Yii2 (I) česky - YouTube](https://youtu.be/ub06hNoL8B8)

**Connection to MSSQL**

---

You will need MSSQL drivers in PHP. Programatically you can list them or test their presence like this:

```php

var_dump(\PDO::getAvailableDrivers());

if (in_array('sqlsrv', \PDO::getAvailableDrivers())) {

  // ... MsSQL driver is available, do something

}

```

Based on your system you have to download different driver. The differences are x64 vs x86 and ThreadSafe vs nonThreadSafe. In Windows I always use ThreadSafe.

[Explanation](https://www.php.net/manual/en/faq.obtaining.php#faq.obtaining.threadsafety).

Newest PHP drivers are [here](https://docs.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server).

- Drivers v5.8 = PHP 7.2 - 7.4

Older PHP drivers [here](https://www.microsoft.com/en-us/download/details.aspx?id=20098).

- Drivers v4.0 = PHP 7.0 - 7.1

- Drivers v3.2 = PHP 5.x

Once drivers are downloaded and extracted, pick one DLL file and place it into folder "php/ext". On Windows it might be for example here: "C:\xampp\php\ext"

**Note:**

In some situations you could also need [these OBDC drivers](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server), but I am not sure when:

Now file **php.ini** must be modified. On Windows it might be placed here: "C:\xampp\php\php.ini". Open it and search for rows starting with word "extension" and paste there cca this:

```php

extension={filename.dll}

// Example:

extension=php_pdo_sqlsrv_74_ts_x64.dll

```

Now restart Apache and visit phpinfo() web page. You should see section "pdo_sqlsrv". If you are using XAMPP, it might be on this URL: [http://localhost/dashboard/phpinfo.php](http://localhost/dashboard/phpinfo.php).

Then just add connection to your MSSQL DB in Yii2 config. In my case the database was remote so I needed to create 2nd DB connection. Read next chapter how to do it.

**Using MSSQL database as the 2nd DB in the Yii2 project**

---

Adding 2nd database is done like this in yii-config:

```php

'db' => $db, // the original DB

'db2'=>[

  'class' => 'yii\db\Connection',

  'driverName' => 'sqlsrv',

  // I was not able to specify database like this:

  // 'dsn' => 'sqlsrv:Server={serverName};Database={dbName}',

  'dsn' => 'sqlsrv:Server={serverName}',

  'username' => '{username}',

  'password' => '{pwd}',

  'charset' => 'utf8',

],

```

That's it. Now you can test your DB like this:

```php

$result = Yii::$app->db2->createCommand('SELECT * FROM {tblname}')->queryAll();

var_dump($result);

```

Note that in MSSQL you can have longer table names. Example: CATEGORY.SCHEMA.TBL_NAME

And your first test-model can look like this (file MyMsModel.php):

```php

namespace app\models;

use Yii;

use yii\helpers\ArrayHelper;

class MyMsModel extends \yii\db\ActiveRecord

{

  public static function getDb()

  {

    return \Yii::$app->db2; // or Yii::$app->get('db2');

  }

  public static function tableName()

  {

    return 'CATEGORY.SCHEMA.TBL_NAME'; // or SCHEMA.TBL_NAME

  }

}

```

Usage:

```php

$result = MyMsModel::find()->limit(2)->all();

var_dump($result);

```

**Creating models in Gii for remote MSSQL tables**

---

Once you have added the 2nd database (read above) go to the Model Generator in Gii. Change there the DB connection to whatever you named the connection in yii-config (in the example above it was "db2") and set tablename in format: SCHEMA.TBL_NAME. If MSSQL server has more databases, one of them is set to be the main DB. This will be used I think. I haven't succeeded to change the DB. DB can be set in the DSN string, but it had no effect in my case.

**PhpExcel/PhpSpreadsheet in Yii 2 and sending binary content to the browser**

---

In previous chapters I showed how to use [PhpExcel in Yii 1](https://www.yiiframework.com/wiki/462/yii-for-beginners-2). Now I needed it also in Yii 2 and it was extremely easy.

Note: [PhpExcel](https://github.com/PHPOffice/PHPExcel) is deprecated and was replaced with [PhpSpreadsheet](https://github.com/PHPOffice/PhpSpreadsheet).

```

// 1) Command line:

// This downloads everything to folder "vendor"

composer require phpoffice/phpspreadsheet --prefer-source

// --prefer-source ... also documentation and samples are downloaded

// ... adds cca 40MB and 1400 files

// ... only for devel system

// 2) PHP:

// Now you can directly use the package without any configuration:

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

// Uncomment following rows if you want to set col width:

//$sheet->getColumnDimension('A')->setAutoSize(false);

//$sheet->getColumnDimension('A')->setWidth("50");

$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);

// You can save the file on the server:

// $writer->save('hello_world.xlsx');

// Or you can send the file directly to the browser so user can download it:

// header('Content-Type: application/vnd.ms-excel'); // This is probably for older XLS files.

header('Content-Type: application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // This is for XLSX files (they are basically zip files).

header('Content-Disposition: attachment;filename="filename.xlsx"');

header('Cache-Control: max-age=0');

$writer->save('php://output');

exit();

```

Thanks to [DbCreator](https://forum.yiiframework.com/t/yii2-phpoffice-phpspreadsheet-src-phpspreadsheet-shared-ole-pps-root-php-on-line-292-headers-already-sent/126617) for the idea how to send XLSX to browser. Nevertheless exit() or die() should not be called. Read the link.

Following is my idea which originates from method renderPhpFile() from Yii2:

```php

ob_start();

ob_implicit_flush(false);

$writer->save('php://output');

$file = ob_get_clean();

return \Yii::$app->response->sendContentAsFile($file, 'file.xlsx',[

  'mimeType' => 'application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',

  'inline' => false

]);

```

This also worked for me:

```php

$tmpFileName = uniqid('file_').'.xlsx';

$writer->save($tmpFileName);

header('Content-Type: application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="filename.xlsx"');

header('Cache-Control: max-age=0');

echo file_get_contents($tmpFileName);

unlink($tmpFileName);

exit();

```

Note: But exit() or die() should not be called. Read the "DbCreator" link above.

**PDF - UTF + 1D & 2D Barcodes - TCPDF**

---

See part I of this guide for other PDF creators:

- [https://www.yiiframework.com/wiki/2552/yii-v2-snippet-guide](https://www.yiiframework.com/wiki/2552/yii-v2-snippet-guide)

TCPDF was created in 2002 (I think) and these days (year 2020) is being rewritten into a modern PHP application. I will describe both, but lets begin with the older version.

**Older version of TCPDF**

- https://github.com/tecnickcom/tcpdf

- https://tcpdf.org/examples/

Download it from GitHub and save it into folder

```

{projectPath}/_tcpdf

```

Into web/index.php add this:

```php

require_once('../_tcpdf/tcpdf.php');

```

Now you can use any Example to test TCPDF. For example: https://tcpdf.org/examples/example_001/

**Note:** You have to call constructor with backslash:

```php

$pdf = new \TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

```

**Note:** Texts are printed using more methods - see file tcpdf.php for details:

- writeHTMLCell()

- Multicell()

- writeHTML()

- Write()

- Cell()

- Text()

**Note:** Store your files in UTF8 no BOM format so diacritics is correct in PDF.

Importing new TTF fonts is done like this:

```php

// this command creates filed in folder _tcpdf\fonts. Use the filename as the fontname in other commands.

$fontname = \TCPDF_FONTS::addTTFfont("path to TTF file", 'TrueTypeUnicode', '', 96);

```

Now you can use it in PHP like this:

```php

$pdf->SetFont($fontname, '', 24, '', true);

```

Or in HTML:

```html

<font size="9" face="fontName" style="color: rgb(128, 128, 128);">ABC</font>

```

Rendering is done like this:

```php

$pdf->writeHTML($html);

```

**Note:** When printing pageNr and totalPageCount to the footer, writeHTML() was not able to correctly interpret methods getAliasNumPage() and getAliasNbPages() as shown in [Example 3](https://tcpdf.org/examples/example_003/). I had to use rendering  method Text() and position the numbers correctly like this:

```php

$this->writeHTML($footerHtmlTable);

$this->SetTextColor('128'); // I have gray pageNr

$this->Text(185, 279, 'Page ' . $this->getAliasNumPage() . '/' . $this->getAliasNbPages());

$this->SetTextColor('0'); // returning black color

```

**New version of TCPDF**

... to be finished ...

- https://github.com/tecnickcom/tc-lib-pdf

- composer require tecnickcom/tc-lib-pdf:dev-master

- use Com\Tecnick\Pdf\TCPDF;

- $pdf = new TCPDF();

**Custom formatter - asDecimalOrInteger **

---

If I generate a PDF-invoice it contains many numbers and it is nice to print them as integers when decimals are not needed. For example number 24 looks better and saves space compared to 24.00. So I created such a formatter. Original inspiration and how-to was found here:

- https://stackoverflow.com/questions/46089960/yii2-formatter-create-custom-format

- ... thanks to karpy47

My formatter looks like this:

```php

<?php

namespace app\myHelpers;

class MyFormatter extends \yii\i18n\Formatter {

  public function asDecimalOrInteger($value) {

    $intStr = (string) (int) $value; // 24.56 => "24" or 24 => "24"

    if ($intStr === (string) $value) {

      // If input was integer, we are comparing strings "24" and "24"

      return $this->asInteger($value);

    }

    if (( $intStr . '.00' === (string) $value)) {

      // If the input was decimal, but decimals were all zeros, it is an integer.

      return $this->asInteger($value);

    }

    // All other situations

    $decimal = $this->asDecimal($value);



    // Here I trim also the trailing zero.

    // Disadvantage is that String is returned, but in PDF it is not important

    return rtrim((string)$decimal, "0");

  }

}

```

Usage is simple. Read the link above and give like to karpy47 or see below:

```php

// file config/web.php

'components' => [

    'formatter' => [

        'class' => 'app\myHelpers\MyFormatter',

   ],

],

```

There is only one formatter in the whole of Yii and you can extend it = you can add more methods and the rest of the formatter will remain so you can use all other methods as mentioned in documentation.

**Displaying SUM of child models in a GridView with parent models**

---

... can be easily done by adding a MySQL VIEW into your DB, creating a model for it and using it in the "ParentSearch" model as the base class.

Let's show it on list of invoices and their items. Invoices are in table "invoice" (model Invoice) and their items in "invoice_item" (model InvoiceItem). Now we need to join them and sort and filter them by SUM of prices (amounts). To avoid calculations in PHP, DB can do it for us if we prepare a MySQL VIEW:

```sql

CREATE VIEW v_invoice AS

SELECT invoice.*,

SUM(invoice_item.units * invoice_item.price_per_unit) as amount,

COUNT(invoice_item.id) as items

FROM invoice

LEFT JOIN invoice_item

ON (invoice.id = invoice_item.id_invoice)

GROUP BY invoice.id

```

**Note:** Here you can read why it is better not to use COUNT(\*) in LEFT JOIN:

- [https://learnsql.com/blog/introduction-using-aggregate-functions-joins/](https://learnsql.com/blog/introduction-using-aggregate-functions-joins/)

- Chapter: Dealing with NULLs

This will technically clone the original table "invoice" into "v_invoice" and will append 2 calculated columns: "amount" + "items". Now you can easily use this VIEW as a TABLE (for reading only) and display it in a GridView. If you already have a GridView for table "invoice" the change is just tiny. Create this model:

```php

<?php

namespace app\models;

class v_Invoice extends Invoice

{

    public static function primaryKey()

    {

        // here is specified which column(s) create the fictive primary key in the mysql-view

        return ['id'];

    }

    public static function tableName()

    {

        return 'v_invoice';

    }

}

```

.. and in model InvoiceSearch replace word Invoice with v_Invoice (on 2 places I guess) plus add rules for those new columns. Example:

```php

public function rules()

{

  return [

    // ...

    [['amount'], 'number'], // decimal

    [['items'], 'integer'],

  ];

}

```

Into method search() add condition if you want to filter by amount or items:

```php

if (trim($this->amount)!=='') {

  $query->andFilterWhere([

    'amount' => $this->amount

  ]);

}

```

In the GridView you can now use the columns "amount" and "items" as native columns. Filtering and sorting will work.

**Danger:** Read below how to search and sort by related columns. This might stop working if you want to join your MySQL with another table.

> I believe this approach is the simplest to reach the goal. The advantage is that the MySQL VIEW is only used when search() method is called - it means in the list of invoices. Other parts of the web are not influenced because they use the original Invoice model. But if you need some special method from the Invoice model, you have it also in v_Invoice. If data is saved or changed, you must always modify the original table "invoice".

**Sort and search by related column**

---

Lets say you have table of invoices and table of companies. They have relation and you want to display list of Invoices plus on each row the corresponding company name. You want to filter and sort by this column.

Your GridView:

```php

<?= GridView::widget([

// ...

  'columns' => [

    // ...

    [

      'attribute'=>'company_name',

      'value'=>'companyRelation.name',

    ],

```

Your InvoiceSearch model:

```php

class InvoiceSearch extends Invoice

{

  public $company_name;



  // ...



  public function rules() {

    return [

      // ...

      [['company_name'], 'safe'],

    ];

  }

  // ...



  public function search($params) {

    // ...

    // You must use joinWith() in order to have both tables in one JOIN - then you can call WHERE and ORDER BY on the 2nd table.

    // Explanation here:

    // https://stackoverflow.com/questions/25600048/what-is-the-difference-between-with-and-joinwith-in-yii2-and-when-to-use-them



    $query = Invoice::find()->joinWith('companyRelation');

    // Appending new sortable column:

    $sort = $dataProvider->getSort();

    $sort->attributes['company_name'] = [

      'asc' => ['table.column' => SORT_ASC],

      'desc' => ['table.column' => SORT_DESC],

      'label' => 'Some label',

      'default' => SORT_ASC

    ];

    // ...

    if (trim($this->company_name)!=='') {

      $query->andFilterWhere(['like', 'table.column', $this->company_name]);

    }

  }

```

**Sending binary data as a file to browser - decoded base64**

---

In my tutorial for Yii v1 I presented following way how to send headers manually and then call exit(). But calling exit() or die() is not a good idea so I discovered a better way in Yii v2.

See chapter [Secured (secret) file download](https://www.yiiframework.com/wiki/462/yii-for-beginners-2)

Motivation: Sometimes you receive a PDF file encoded into a string using base64. For example a label with barcodes from FedEx, DPD or other delivery companies and your task is to show the label to users.

For me workes this algorithm:

```php

$pdfBase64 = 'JVBERi0xLjQ ... Y0CiUlRU9GCg==';

// First I create a fictive stream in a temporary file

// Read more about PHP wrappers:

// https://www.php.net/manual/en/wrappers.php.php

$stream = fopen('php://temp','r+');

// Decoded base64 is written into the stream

fwrite($stream, base64_decode($pdfBase64));

// And the stream is rewound back to the start so others can read it

rewind($stream);

// This row sets "Content-Type" header to none. Below I set it manually do application/pdf.

Yii::$app->response->format = Yii::$app->response::FORMAT_RAW;

Yii::$app->response->headers->set('Content-Type', 'application/pdf');



// This row will download the file. If you do not use the line, the file will be displayed in the browser.

// Details here:

// https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers#Downloads

// Yii::$app->response->headers->set('Content-Disposition','attachment; filename="hello.pdf"');



// Here is used the temporary stream

Yii::$app->response->stream = $stream;

// You can call following line, but you don't have to. Method send() is called automatically when current action ends:

// Details here:

// https://www.yiiframework.com/doc/api/2.0/yii-web-response#sendContentAsFile()-detail

// return Yii::$app->response->send();

```

**Note:** You can add more headers if you need. Check my previous article (linked above).

Categories

Popular Tags

Recent Comments

Difference between #1 and #54 of
Yii v2 snippet guide II

Changes

Title unchanged

Category unchanged

Yii version unchanged

Tags changed

Content changed

Revisions