Yii Framework Wiki
Difference between
#19 and
#54 of
Yii v2 snippet guide II
Changes
Title unchanged
Yii v2 snippet guide II
Category unchanged
Tutorials
Yii version unchanged
2.0
Tags unchanged
tutorial,beginner,yii2,snippets
Content changed
---
Hi. I had to split my article as max length was reached. Check my previous articles here (mainly the 1st one):
- [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 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.
**Using composer-packages - for example PhpExcel/PhpSpreadsheet in Yii 2**
---
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 latter approach.
Following is my idea:
```php
ob_start();
ob_implicit_flush(false);
$writer->save($outputFileName);
$file = ob_get_clean();
return \Yii::$app->response->sendContentAsFile($file, 'file.xlsx',[
'mimeType' => 'application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'inline' => false
]);
```
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).
3 followers
Viewed: 68 722 times
Version: 2.0
Category: Tutorials
Written by: rackycz
Last updated by: rackycz
Created on: Aug 26, 2020
Last updated: 2 years ago
Revisions
View all history-
2 years ago by
rackycz youtube link
-
3 years ago by
rackycz List of articles
-
3 years ago by
rackycz List of articles
-
3 years ago by
rackycz base64 stream download
-
3 years ago by
rackycz base64 stream download
-
3 years ago by
rackycz base64 stream download
-
3 years ago by
rackycz Search sort related column
-
3 years ago by
rackycz Search sort related column
-
3 years ago by
rackycz Search sort related column
-
3 years ago by
rackycz Search sort related column