1. Intro (Part 2)
2. 6. Your Own SQL
3. 7. THEMES
4. 8. Secured (secret) file download
5. 9. Modules (not Models)
6. 10. Translating core messages
7. 11. Security
8. 12. Recommended software and sites
9. 13. CGridView
10. 14. Excel
11. 15. Known issues and bugs
12. 16. Reset default scope in relation

Intro (Part 2) ¶

Hi :-) Is that you again? Welcome !! :-)

This is second article with my tutorial. I had to add it, because Wiki article has limited length and as I was extending it's text, older paragraphs were disappearing. So here I am starting from chapter 6.

Previous article can be found here: Yii for beginners 1.

If you are interested in Yii v2, check this out: Yii v2 for beginners

6. Your Own SQL ¶

Sometimes it is much easier to create your own SQL query. I measured difference between ActiveRecord query and pure SQL. Pure SQL was 2x faster. Usefull mainly in ajax. So here is a small code that performs your SQL query and returns an array with results:

public static function sqlQuery($sql)
    {
        // $sql can be for example: 
        // "SELECT * FROM firstTable join secondTable ON ... GROUP BY ... "
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $results = $command->queryAll();
        return $results;
    }

Disadvantage is that you cannot use methods from ActiveRecord. For example validation etc. But it is faster and if you just read from DB, you can try it.

7. THEMES ¶

You may want to skin your web and allow users (or yourself) to change the skins on demand. It’s very easy. In default state (in automatically created demo project) no theme is set or used. Is used some default appearance. This was the first thing that I did not understand To turn a theme on, you have to specify it in file “protected/config/main.php” by adding the 'theme' line:

return array(
	'basePath'=>dirname(__FILE__).DIRECTORY_SEPARATOR.'..',
	'name'=>'Hello World !!',
            'theme'=>'theme1',
…

);

It means that this variable is readable using Yii::app()->theme->getName(). You can set it programmatically in an ACTION (not in view!!) like this:

Yii::app()->theme = 'theme1'; // = each user can have (select) different theme

If theme is not set (using config file, or programmatically) then method getName() will throw error! Yii::app()->theme->baseUrl will contain your theme path = you can use it in layout file to address your css files, images etc. To use your theme1, you will have to create a new subfolder in “themes” folder and name it “theme1”. The “themes” folder is placed in the root of your project. In folder “theme1” will be the same structure as in “protected” folder. Create there views and CSS folders. If a theme is set, views and controllers will be taken from your theme folder. If Yii do not find them, tries to look for them in ordinary “views” and “controllers” folders. But controllers and actions can be the same for all themes. There does not have to be a reason to change them. On the other hand, CSS files will be probably different, so create CSS folder in folder of your new theme and put new CSS files in it.

... to be continued ...

8. Secured (secret) file download ¶

Sometimes you want to offer a file to only a LOGGED IN user but don't want him to see it's path. Nobody has to know that you store files in folder: myweb.com/files. Usually links on webs look like this:

<a href="myweb.com/files/my.pdf">download</a>

But if particular files in this folder are for only particular users, there is danger, that some "smart" user will download files that are not for him, because he knows where your data-storage is.

But how to offer a file for download and not to tell users where files are stored? You can't link particular files. You have to "stream" it to user.

It is also good to store file names in DB and save files to your storage only with their IDs. Like this:

myweb.com/files/1.pdf
myweb.com/files/2.doc

etc. IDs are IDs from DB.

At the end, your download links will look like this:

myweb.com/storage/download/123/how-to-do-something.pdf

or rewritten like this:

myweb.com/controller/action/GET-ParamName/GET-ParamValue

I think now it's obvious what I want to show.

Your download links won't point to files, but to a specialised action that sends file to user. In this action you can filter users who are allowed to download your file. You can also use accessControll:

http://www.yiiframework.com/doc/guide/1.1/en/topics.auth#access-control-filter

Finally download links look like this:

<a href="myweb.com/storage/download/123/how-to-do-something.pdf">download</a>

The action is in Controller "storage" and is called actionDownload(). It processes GET parameter like this:

$error = false;
 
// no GET parameters           
if (sizeof($_GET)<=0)
{
  $error=true;
}
           
$id = 0;
 
// we take just the first (and the only) GET parameter           
foreach ($_GET as $id_=>$title_)            
{
  $id = $id_;
  break;
}
            
if (empty($id))
{
  $error = true;
}
 
if (!is_numeric($id))
{
  $error = true;
}
            
if (strval(intval($id)) <> strval($id)) 
{
// this is a simple test that checks whether a string represents an integer
  $error = true;
} 

if ($error)
{
  $this->redirect( ... );
}

// now we know that incomming GET parameter was integer
// you may have noticed, that it's value was lost:
// myweb.com/storage/download/123/how-to-do-something.pdf
// $id = 123.

Now you just have to have a look into DB, find filename and path. When this is done, you just write this:

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($fileName));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($fileName));
ob_clean();
flush();
readfile($fileName);
exit;

File is downloaded and user doesn't know from where :-)

Instead of readfile() Yii offers CHttpRequest->sendFile(). But it is goot just for small files. Readfile() is better.

And do not forget to put .htaccess file to your storage-folder with this content:

SetHandler enginge_off
deny from all

First line banns to run PHP in this folder, second line denies direct access via browser.

9. Modules (not Models) ¶

Module is a nested Yii project. It's good when you need to have longer path. Not only cars.com/buy/trailer (project/controller/action). But for example: cars.com/forsuppliers/howtosell/cars (project/module/controller/action). Module can have it's own controllers, actions, models.. But it can use models of parent project.

When I tried to run my first module I read the Yii manual at site:

http://www.yiiframework.com/doc/guide/1.1/en/basics.module

But (of course) didn't succeed. Yii manuals just don't describe everything that is important. They are just an overview for people, who understand it. So I had to investigate by my self.

It is simple:

• You have your original Yii project (project A)

• If you want to create a new module, create a brand new project using command line. (project B)

• In project A go to folder "protected" and create there folder "modules". In it create nested folder that will have name of your new module. Lets say "forsuppliers".

• In your project B go to folder "protected" and copy at least folders: components, controllers, views.

• Paste them to your "forsuppliers" folder.

• To "forsuppliers" folder add PHP file with name "ForsuppliersModule.php"

• To this file enter following text:

<?php
class ForsuppliersModule extends CWebModule
{
// Following will be the default controller of your new module
// Yii manual doesn't mention this so modules won't work for you without further research.
// If you didn't write this line, the default controller would be "DefaultController" or "Default"
// http://www.tipstank.com/2010/11/19/change-the-default-controller-of-a-module-in-yii/
  public $defaultController = 'Site';
}
?>

• In your project A (the parrent project) open file protected/config/main.php a find section 'modules'. Here you have to add your new module like this:

'modules'=>array(
          'forsuppliers'=>array()
),

• Now you can use the same models like in the parent project and your url is longer: parrentProject/moduleName/controller/action

10. Translating core messages ¶

I again found one thing that wasn't mentioned anywhere ...

If you want to set language there is one problem that I met. Yii does not remember variable Yii::app()->language! You have to set it in constructor of your basic Controller everytime again!

How?

Go to protected/components/Controller.php and make sure there is constructor with cca following content:

public function __construct($id, $module = null) 
{
  parent::__construct($id, $module);

  // testing if in URL was specified language and saving it to session varibale
  if (isset($_GET["lang"]))
  {
    Yii::app()->session["lang"] = $_GET["lang"];    
  }

  // filling variable $lang based on variable in session
  $lang = 'en_us';

  if (isset(Yii::app()->session['lang'])
  {
    $lang = Yii::app()->session['lang'];
  }

  // the most important thing - setting the language
  Yii::app()->setLanguage($lang);

}

In protected/config/main.php you need to add your source (basic) language. I recommend English.

'sourceLanguage' => 'en_us',

11. Security ¶

There is one thing I would like to show you. It is very simple, but I didn't realize this security problem for quite a long time.

Imagine that you are creating an administration for a forum. If a user loges in, he can edit his posts. Not posts of other people. First, he's presented with the list of his posts and each post contains the edit icon. You surely filter those posts by "id_user" that is stored in the session. If the user clicks the edit icon, ID of the post is sent to the server and the user will be presented with details of this post. This is done using a hidden field with id_post. But I didn’t know that it was so easy to change the value of this hidden field.

To filter posts by id_user I recommend to create a scope in the model that will return only posts of particular user. Scope can work with the session variable id_user. This scope can be named getCurrentUsersPosts. Filtering than looks like this:

$posts = Post::model()->getCurrentUsersPosts()->findAll();
foreach ($posts as $post)
{
// …
echo CHtml::activeHiddenField($post,’id_post’);
}

By clicking one record, it’s ID is sent to server and it shows the detail of it. It works like this:

$post = Post::model()->findByPk($_POST[“id_post”]);

But what if somebody changed the hidden field using firebug? Than user will see detail of post that doesn’t belong to him and he will be able to change it.

Solution? … Simple.

Just add here the scope:

$post = Post::model()‐>getCurrentUsersPosts()‐>findByPk($_POST[“id_post”]);

Difference? Edited post will be searched only among allowed records. If a user changes the hidden field and the desired post is not his, he will receive error ($post will be empty) and you know exactly what he did.

12. Recommended software and sites ¶

Web creation

• MySQL Workbench

• NetBeans

• WAMP server

• Firefox + Firebug extension + HTML validator extension

• Icons (iconfinder, iconarchive) - but make sure that icons are freeware! If icon is "free" it doesn't mean it is freeware and that you can use it on web of your employer! I recommend to use only absolutely free icons.

Web page

• jQuery

• Fancybox

Sites

• w3schools.com (manual for CSS3, JS, etc)

• www.php.net (you can search for PHP methods in right upper corner)

• Yii Playground - Code samples. I recommend to learn mainly CGridView.

Things to think about

• SEO optimization of your web

• Security of your web

13. CGridView ¶

With pure SQL ¶

If you have a complex SQL query, then ActiveRecord with relations and scopes probably won't make it and you will have to use pure SQL. Personally, I love pure SQL because it is fast and I can create any query I want. With ActiveRecord I have troubles. Even if you use pure sql, you can be protected from SQL injection. So take care how you use your queries. I will mention the "protection" later.

So how to use pure SQL query and show it's results in CGridView? It's easy to write it, but much harder to find out how to write it.

First, you need the SQL. Let's use some simple one:

SELECT * FROM user

In your controller create a new action and insert following code. It is usual for CGridView:

$model = new User('search');
if (isset($_GET['User']))
{
  $model->attributes = $_GET['User'];
}
$this->render('userList', array('model'=>$model));

// This code is here because of filtering rows. 
// If a filter is used, CGridView sends via ajax some GET parameters. 
// Their names as created just like in case of CHtml::activeTextField() therefore you work with them in the same way here.

In the view "userList" paste this definition of CGridView:

$this->widget('zii.widgets.grid.CGridView', array(
	'dataProvider' => $model->search(),
	'filter' => $model,
        'cssFile' => Yii::app()->baseUrl . '/css/cgridview/style.css',
        'pager' => array(
            'cssFile' => Yii::app()->baseUrl . '/css/cgridview/pager.css',
            'header'=>'',
            'maxButtonCount'=>5, 
            ),
        'itemsCssClass' => 'class4theTable',
        'template' => '{summary}{pager}{items}{pager}', 
	'columns' => array(

       array('name'    => 'surname',   // column name in DB, because of filtering
             'header'  => 'Surname of user',  // any text
             'type'    => 'raw',
             'value'   => '$data["surname"]', // reading data that were received from DB
             'sortable'=> false,              // should be this column sortable?  
           //'filter'  => '',                 // empty string = no filtering allowed
           //'htmlOptions' => array('style'=>'width: 30px;')
		),
  ),
));

Method search() in your model has to return object CSqlDataProvider in our case. If working with CActiveRecord and not with pure SQL, it would return CActiveDataProvider.

Variable $model is the one that came from controller. It's attributes contain values that will be used to filter rows in SQL (you will have to add this functionality manually, it's simple, I will show you)

If you have ever worked with CGridView you know that in the list of columns in section "value" you used variable $data as ActiveRecord object (row of your record) and to access attributes you used $data->id. Now we do not work with ActiveRecord but with pure SQL so DB will be return array and we will work with it like this: $data["id"]. Let me remark that in the "value" section you can use any PHP code. Do not use ECHO nor RETURN. For example:

'value'=>'convertMyNumber($data->getcount())',

To disable filtering, just write to the column-definition this:

'filter' => '',

Now you only have to define the search() method in your model. It will return data for CGridView. And you can of course rename this method.

public function search()
{
    $sql = ' ... ';
    return new CSqlDataProvider($sql);
}

This is the basic search method. Bot now it can't filter, sort od page records. So let's enhance it. Sorting is defined using CSort object. And to page records correctly you have to count how many records are totally available

public function search()
{
    $sql = ' ... ';
    
    $sort               = new CSort();
    $sort->defaultOrder = 'username'; // default sorting
    $sort->attributes   = array('id','username','firstname','surname'); // list of all sortable columns

    $count_sql          = "SELECT count(*) FROM ( ($sql) as C)";
    $count              = Yii::app()->db->createCommand($count_sql)->queryScalar()
             
    return new CSqlDataProvider($sql, array(
            'sort'=>$sort,
            
            'totalItemCount'=>$count
            'pagination'=>array
            (
                    'pageSize'=>20,
            ),
                      
      ));
}

I learned this at:

http://stackoverflow.com/questions/14513549/csqldataprovider-complex-query-and-totalitemcount

http://stackoverflow.com/questions/13044681/yii-cgridview-sorting-with-csqldataprovider

If you want to filter items you have to probably do it by your self. If any filter is used, then variable $model contains values that should be filtered. So you just have to use it and add/modify your final WHERE condition:

$where = '';
$whereArray = array();
            
foreach ($this->attributes as $key=>$value)
{
  if (trim((string)$value)!='')
  {
    $whereArray[]=" $key = '$value' ";
    // if your table has some alias, you have to use it here like this:
    // $whereArray[]=" alias.$key = '$value' ";
  }
}
            
if (!empty($whereArray))
{
  $where = 'WHERE ' . implode('AND', $whereArray);
}

If your query was:

$sql = 'SELECT * FROM user ';

You just add this:

$sql = 'SELECT * FROM user '.$where;

When you defined columns in CGridView, you used some "name" of your column. This has to be the same name as is in DB, otherwise filtering won't work. This column name also has to be used in "value" section.

Save state of filters and pagination ¶

As I already wrote many times, I'm friend of very simple native solutions. Following works for me well. It saves GET parameters and when you come back to the page, they are used to filter the grid. It of course has to distinguish what Grid ID (or particular page with set of grids) you are using, but the concept is fine I think. The only problem can appear when $_GET['ajax'] is saved and then used when it should not be.

if (!isset(Yii::app()->session["lastGet"]))
{
  Yii::app()->session["lastGet"] = array();
}

if (empty($_GET))
{
  $_GET = Yii::app()->session["lastGet"];
}
else
{
  Yii::app()->session["lastGet"] = $_GET;
}

$model = new MyModel('search');

if (isset($_GET['MyModel']))
{
  $model->attributes = $_GET['MyModel'];
}

$this->render('list', array('model'=>$model));

2x Identical CGridView on 1 page for 1 DB Table ¶

Sometimes it happens that you need to show 2 absolutely similar grids on 1 page. Both are based on 1 common table that contains column "is_closed".

The upper grid should show "open" records where is_closed=0, the lower one will show "closed" records where is_closed=1.

How to do it and allow filtering of both grids?

1) You can set different ID to each grid and do this:

$upperModel = new Model();
$lowerModel = new Model();

$upperGridId = "upper_table_open";
$lowerGridId = "lower_table_closed";

if (isset($_GET["Model"]))
{
	$upperModel->attributes = $_GET["Model"]; // Both tables will use the same filter
	$lowerModel->attributes = $_GET["Model"]; // Both tables will use the same filter

	if (isset($_GET["ajax"]) && ($_GET["ajax"] == $upperGridId) )
	{
		// filter on the upper table was triggered
		// as it is an ajax request, only this table needs to be rendered
		// so you can speed things up by renderPartial() and return

		$this->renderPartial('view_grid',array('model'=>$upperModel) );
		return;
	}
	elseif (isset($_GET["ajax"]) && ($_GET["ajax"] == $lowerGridId) )
	{
		// filter on the lower table was triggered
		// as it is an ajax request, only this table needs to be rendered
		// so you can speed things up by renderPartial() and return

		$this->renderPartial('view_grid',array('model'=>$lowerModel) );
		return;
	}
}

// if we get here, we know that it is not an Ajax request, but standard mouse-click ot F5.

$this->render('view_both_grids',array('upperModel'=>$upperModel,'lowerModel'=>$lowerModel) );

You will also need the search() method with cca this content:

public $search_closed_value = 0;
public function search();
{
	$criteria = new CDbCriteria;
	$criteria->compare('id_closed',$this->search_closed_value);
}

Before calling this method, do not forget to set:

$model->search_closed_value = 1; // If you are searching for closed records. Open records will be searched automatically.

The result will be that both tables will share 1 filter-setting in case that you enter this URL:

myweb.com/controller/action?Model[id]=1

.. if you do this, both tables will use the same ID in filter and it won't be possible to send such a link that will cause both tables to have a different filter. Sometimes this is +, sometime -. It depends.

2) If you want to enable user to set different filters in the URL for both tables, it is very easy to enhance this example. The goal is to have different model-name, but the same logic. How to do it? .. Just create a second model that will extend the first model and will contain only 1 row.

Class Model_closed extends YourModel
{
	public $search_closed_value = 1;
}

Now you only have to enhance the action in point 1. You won't need the ID of the grid.

$upperModel = new Model();
$lowerModel = new Model_closed();

if (isset($_GET["Model"]))
{
	$upperModel->attributes = $_GET["Model"];

	if (isset($_GET["ajax"]))
	{
		// filter on the upper table was triggered
		// as it is an ajax request, only this table needs to be rendered
		// so you can speed things up by renderPartial() and return

		$this->renderPartial('view_grid',array('model'=>$upperModel) );
		return;
	}
}

if (isset($_GET["Model_closed"]))
{
	$lowerModel->attributes = $_GET["Model_closed"];

	if (isset($_GET["ajax"]))
	{
		// filter on the upper table was triggered
		// as it is an ajax request, only this table needs to be rendered
		// so you can speed things up by renderPartial() and return

		$this->renderPartial('view_grid',array('model'=>$lowerModel) );
		return;
	}
}

$this->render('view_both_grids',array('upperModel'=>$upperModel,'lowerModel'=>$lowerModel) );

14. Excel ¶

Export HTML table to Excel ¶

Just try following method. You don't have to use only tables in your HTML. Important are intro-chars before any HTML code is printed. Excel needs them to understand that you are using UTF-8. See the code.

public function sendHtmlAsXLS($html,$lastModified='', $filenameWithoutExtension='')
{
  header('Pragma: public');
  header("Expires: Sat, 26 Jul 2097 05:00:00 GMT");   
  header('Last-Modified: '.$lastModified . ' GMT');
  //header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT');
  header('Cache-Control: no-store, no-cache, must-revalidate');
  header('Cache-Control: pre-check=0, post-check=0, max-age=0');
  header("Pragma: no-cache");
  header("Expires: 0");
  header('Content-Encoding: UTF-8');
  header('Content-Transfer-Encoding: text');
  header('Content-Type: application/vnd.ms-excel; charset=UTF-8');   
  header("Content-type: application/x-msexcel; charset=UTF-8");
  header('Content-Disposition: attachment; filename="'.$filenameWithoutExtension.'.xls"');

  // 3 intro-chars
  // Necessary for UTF-8 !!
  echo  chr(0xEF) . chr(0xBB) . chr(0xBF);        

  echo '<html>';
    echo '<head>';
    echo '</head>';
        
    echo '<body>';

      echo $html;

    echo '</body>';
  echo '</html>';
}

// Can be (of course) used with renderPartial:

$params = array();
$html = $this->renderPartial('view',$params,TRUE);
sendHtmlAsXLS($html);

Excel with more worksheets ¶

If you need your Excel to have more sheets, previous algorithm won't work for you. (And if it works for you, let me know how, please). I had to use extension PHPExcel to reach the goal.

There are planty of extensions for Excel, but some of them are so complicated that I rather used pure PHPExcel without Yii extensions (wrappers). The less extensions the better.

Main manual for me was this one - by Yii user StasuSS.

I'm adding info about how to use more worksheets and how to style cells.

In short:

Make sure that your config/main.php contains this:

return array(
  // application components
  'components'=>array(
    'excel'=>array(
      'class'=>'application.extensions.PHPExcel',
    ),
  ),

  // and

 'import' => array(
        'application.extensions.*',
  ),
);

Then download PHPExcel:

Extract it and move it to folder protected/extensions so that file PHPExcel.php is in the root of extensions as same as folder PHPExcel.

Now edit file protected/extensions/PHPExcel/Autoloader.php and replace following method:

public static function Register() 
{
  $functions = spl_autoload_functions();

  foreach($functions as $function)
    spl_autoload_unregister($function);

  $functions=array_merge(array(array('PHPExcel_Autoloader', 'Load')), $functions);

  foreach($functions as $function)
    $x = spl_autoload_register($function);

  return $x;
}

I don't understand it but it works. Now you can create Excel like this:

$objPHPExcel = new PHPExcel();
$objPHPExcel->removeSheetByIndex(0); // this removes the first sheet that is automatically created - usefull when generating excel in a loop

for ($i = 0; $i < 3; $i++)
{
  $objWorkSheet = $objPHPExcel->createSheet($i); //  adding a new sheet
  $objPHPExcel->setActiveSheetIndex($i);
  $activeSheet = $objPHPExcel->getActiveSheet();
  $activeSheet->setTitle("text without slashes " . $i);
  $activeSheet->setCellValue('A1', 'Text ' . $i);

  // You can style your cells like this:
  $cellStyle = array(
    'alignment' => array(
      'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
     ),
     'fill' => array(
       'type' => PHPExcel_Style_Fill::FILL_SOLID,
       'color' => array('rgb' => '999999')
     ),
     'font' => array(
       'color' => array('rgb' => 'FFFFFF')
     ),
  );

  $activeSheet->getStyle("A1:B1")->applyFromArray($cellStyle);

  // Now in your Excel cells A1:B1 will be selected. To unselect them I use following command:
  $activeSheet->getStyle("A1");

  // This command changes width of column by it's content
  $activeSheet->getColumnDimension("A")->setAutoSize(true);
}

  // In the end I select the first sheet
  $objPHPExcel->setActiveSheetIndex(0);

  // And send the file to user
  ob_end_clean();
  ob_start();

  header('Content-Type: application/vnd.ms-excel');
  header('Content-Disposition: attachment;filename="test.xls"');
  header('Cache-Control: max-age=0');
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  $objWriter->save('php://output');

15. Known issues and bugs ¶

GetTableAlias() in defaultScope() ¶

If you write following code (in defaultScope), yii crashes:

public function defaultScope()
{
  $alias = $this->getTableAlias();
}

In defaultScope() you have to call it like this:

$alias = $this->getTableAlias(false, false);

Was solved here and here.

16. Reset default scope in relation ¶

There is a problem if you define a defaultScope() in your model and want to access it via relation from a different model. Sometimes you need to reset the related defaultScope(), but it did not work for me so I had to come with another simple solution.

Basic idea is to add "scopes" to definition of your relation. It should technically work, because method resetScope() exists in each model and returns $this. But in my case (Yii 1.1.15) it didn't.

public function relations(){
  return array(
    'myRelation' => array(self::BELONGS_TO, 'User', 'user_id', 'scopes'=>array( 'resetScope' ) )
  );
}

To make this work, you have to override method resetScope() in the related model, add one variable and use it in your defaultScope(). This solved my situation.

private $_ignoreDefaultScope = false;

// Here I am overriding the standard resetScope()
public function resetScope($resetDefault = true)
{
  $this->_ignoreDefaultScope = true;
  return parent::resetScope($resetDefault);
}

public function defaultScope()
{
  if ($this->_ignoreDefaultScope)
  {
    return array();
  }
  // ...
}

Because:

Scopes are called in this order:

1st = resetScope()

2nd = defaultScope()

3rd = custom scope