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. Using composer-packages - for example PhpExcel/PhpSpreadsheet in Yii 2

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.

Using composer-packages - for example PhpExcel/PhpSpreadsheet in Yii 2 ¶

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"');
$writer->save('hello world.xlsx');
exit();