Step Three:Yii and Databases

Yii and databases

Yii provides great support for database programming. Yii Data Access Objects (DAO) is built on top of the PHP Data Objects (PDO) extension (http://php.net/pdo). This is a database abstraction layer that enables the application to interact withthe database independent of the specific choice of database server. All supported database management systems (DBMS) are encapsulated behind a single uniform interface. This way, the code can remain database independent, and applications developed using Yii DAO can be easily switched to use a different DBMS without the need for modification.

To establish a connection with a supported DBMS, one can simply create a new CDbConnection class and instantiate it:
$connection=new CDbConnection($dsn,$username,$password);

Here the format of the $dsn variable depends on the specific PDO database driver being used. Some common formats include:
• SQLite: sqlite:/path/to/dbfile
• MySQL: mysql:host=localhost;dbname=testdb
• PostgreSQL: pgsql:host=localhost;port=5432;dbname=testdb
• SQL Server: mssql:host=localhost;dbname=testdb
• Oracle: oci:dbname=//localhost:1521/testdb

CDbConnection also extends from CApplicationComponent, which allows it to be configured as an application component. This means that we can add it to the components property of the application, and customize the class and property values that are there in the configuration file. This is our preferred approach.

To customize our application configuration, we normally provide a configuration file to initialize its property values when the application instance is being created. The main application configuration file is located in /protected/config/main.php. This is actually a PHP file containing an array of key-value pairs. Each key represents the name of a property of the application instance, and each value is the corresponding property's initial value. If you open up this file, you will see that a few settings have already been configured for us by the yiic tool.

Adding an application component to the configuration is easy. Open up the main config file and locate the components property. We see that there are already entries specifying a log and a user application component. We also see ( as we noted previously) that there is a db component there as well, configured to use an SQLite connection to an SQLite database located at protected/data/testdrive.db. We are going to replace that connection with one for a MySQL database. It can be defined as follows:

// application components
'components'=>array(
…
'db'=>array(
'connectionString' => 'mysql:host=127.0.0.1;dbname=trackstar_dev',
'emulatePrepare' => true,
'username' => 'your_db_user_name',
'password' => 'your_db_password',
'charset' => 'utf8',
),
)

This assumes that a MySQL database has been created called trackstar_dev and is available to connect to the localhost IP of 127.0.0.1. One of the great benefits of making this an application component is that from now on, we can reference the database connection simply as a property of the main Yii application: Yii::app()->db anywhere throughout our application. Similarly, we can do this for any of the components defined in the config file.