Difference between #27 and #28 of Yii for beginners 2

unchanged
Title
Yii for beginners 2
unchanged
Category
Tutorials
unchanged
Tags
yii, tutorial, beginner, MVC
changed
Content
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**](http://www.yiiframework.com/wiki/250/yii-for-beginners/ "Yii for
beginners 1").

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:

~~~
[php]
 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:


~~~
[php]
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:


~~~
[php]
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: 
~~~
[php]
<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:

~~~
[php]
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:

~~~
[php]
myweb.com/storage/download/123/how-to-do-something.pdf
~~~

or rewritten like this:

~~~
[php]
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:

~~~
[php]
<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:

~~~
[php]
$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:

~~~
[php]

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:
~~~
[php]
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](http://www.yiiframework.com/doc/guide/1.1/en/basics.module
"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]
<?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:

~~~
[php]
'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:
~~~
[php]
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.
~~~
[php]
'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 error 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
list of his posts. You surely filter them by "id_user" that is stored
in session. If user clicks an item of this list, he will see details of this
post and he can change it. This is done using hidden form 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 creating a scope in Post model that will
return only posts of logged user. Scope can work with the session variable
id_user. This scope can be named getCurrentUsersPosts. Filtering than looks like
this:
~~~
[php]
$posts = Post::model()->getCurrentUsersPosts()->findAll();
foreach ($posts as $post)
{
// …
echo CHtml::activeHiddenField($post,’id_post’);
}
~~~
By clicking one record, it’s ID is send to server and it shows the detail of
it. It works like this:
~~~
[php]
$post = Post::model()->findByPk($_POST[“id_user”]);
~~~
But what if somebody changed the hidden field? 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:
~~~
[php]
$post =
Post::model()‐>getCurrentUsersPosts()‐>findByPk($_POST[“id_user”]);
~~~
Difference?
Edited post will be searched only among allowed records. If 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](http://www.mysql.com/products/workbench/)

- [NetBeans](http://netbeans.org/)

- [WAMP server](http://www.wampserver.com/en/)

- [Firefox](http://www.firefox.com) + [Firebug](https://getfirebug.com/)
extension + HTML validator extension

- Icons ([iconfinder](http://www.iconfinder.com/),
[iconarchive](http://www.iconarchive.com/)) - 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](http://www.jquery.com "jQuery")

- [Fancybox](http://www.fancybox.net "Fancybox")


**Sites**

- [w3schools.com](http://www.w3schools.com/css/default.asp "W3
Schools") (manual for CSS3, JS, etc)

- [www.php.net](http://www.php.net "php.net") (you can search for PHP
methods in right upper corner)

- [Yii
Playground](http://www.yiiplayground.com/index.php?r=UiModule/dataview/gridView
"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:
~~~
[php]
SELECT * FROM user
~~~
In your controller create a new action and insert following code. It is usual
for CGridView:
~~~
[php]
$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:

~~~
[php]
$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:

~~~
[php]
'value'=>'convertMyNumber($data->getcount())',
~~~


To disable filtering, just write to the column-definition this:
~~~
[php]
'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.
~~~
[php]
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
~~~
[php]
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/14513549/csqldataprovider-complex-query-and-totalitemcount
"http://stackoverflow.com/questions/14513549/csqldataprovider-complex-query-and-totalitemcount")

[http://stackoverflow.com/questions/13044681/yii-cgridview-sorting-with-csqldataprovider](http://stackoverflow.com/questions/13044681/yii-cgridview-sorting-with-csqldataprovider
"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:

~~~
[php]
$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:
~~~
[php]
$sql = 'SELECT * FROM user ';
~~~
You just add this:
~~~
[php]
$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.

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.



~~~
[php]
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](http://www.yiiframework.com/forum/index.php/topic/29225-tip-using-phpexcel-with-yii/
"title") - 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:

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

  // and

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

Then download [PHPExcel](http://phpexcel.codeplex.com/releases/view/107442
"Title"):

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:

~~~
[php]
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:

~~~
[php]
$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');
~~~

14. Known issues and bugs
----------------------------------
### GetTableAlias() in defaultScope()

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

~~~
[php]
public function defaultScope()
{
  $alias = $this->getTableAlias();
}
~~~

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

~~~
[php]
$alias = $this->getTableAlias(false, false);
~~~

Was solved
[here](http://www.yiiframework.com/forum/index.php/topic/9036-trying-to-get-table-alias-regularly-crashes/
"title") and [here](http://code.google.com/p/yii/issues/detail?id=1183
"title").