1. Intro
2. Connection to MSSQL
3. Using MSSQL database as the 2nd DB in the Yii2 project
4. Creating models in Gii for remote MSSQL tables
5. PhpExcel/PhpSpreadsheet in Yii 2 and sending binary content to the browser
6. PDF - 1D & 2D Barcodes - TCPDF

Intro ¶

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/250/yii-for-beginners
• 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:

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.

Newest PHP drivers are here.

• Drivers v5.8 = PHP 7.2 - 7.4

Older PHP drivers here.

• 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, 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:

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.

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:

'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:

$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):

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:

$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. Now I needed it also in Yii 2 and it was extremely easy.

Note: PhpExcel is deprecated and was replaced with 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 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:

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:

$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 - 1D & 2D Barcodes - TCPDF ¶

See part I of this guide for other PDF creators:

• 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:

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:

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

Note that texts are printed using more methods - see file tcpdf.php for details:

• writeHTMLCell()
• Multicell()
• writeHTML()
• Write()
• Cell()
• Text()

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();