This recipe describes the steps required to set up database access in a Yii application.
We will use a sample application like the one created in the previous recipe. Additionally, we will need a database server like MySQL, PostgreSQL, or SQLite; also, we will need its credentials and some interface like PHPMyAdmin or command-line access to execute SQL statements against our database.
First of all, we need to create a database.
Create a new database in MySQL.
Now, go to the sample application and change the folder to
protected/config. It contains the following three files:console.php: This is the configuration file to run applications in console modemain.php:This is the configuration file for web applicationstest.php: This is the configuration file for testing web applications
Open
main.phpand locate the following code:'db'=>array( 'connectionString' => 'sqlite:'.dirname(__FILE__).'/../data/testdrive.db', ), // uncomment the following to use a MySQL database */ 'db'=>array( 'connectionString' => 'mysql:host=localhost;dbname=sampleapp', 'emulatePrepare' => true, 'username' => 'root', 'password' => '', 'charset' => 'utf8', ), */
Tip
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
The basic application created uses the SQLite database. Remove the code for SQLite configuration and uncomment the MySQL configuration.
Change the connectionString, username, and password to match your environment. This is all you need to set the database connection.
Yii provides the following three methods to work with database connections:
Active Record
Query Builder
Data Access Objects (DAO)
Models in Yii generally extend the CActiveRecord class to provide database access using the Active Record method. This uses a complete object-oriented coding style and creates all the necessary SQL to deal with the database server. Additionally, we can set validation rules in models and also add pre- and post-save hooks. Yii provides the GUI tool named Gii to generate the models for database tables. Though the Active Record method is easy to use, it consumes more memory and needs more execution time than other methods. The sample code to get all records from a table (say, User) will go as follows:
$users = User::model()->findAll(array('status'=>'active'));With Query Builder, we create a command object with Yii::app()->db->createCommand() and then add other parts of SQL query with methods such as select, from, where, and join. Query Builder is faster than Active Record and provides a clean API to query the database. For example:
$command = Yii::app()->db->createCommand();
$command->select(*)->from('user')->where(array(
'status'=>'active'
));
$users = $command->queryAll();Finally, with a DAO we create the same command as in Query Builder, but instead of adding a query using methods, we pass the entire SQL statement to it as Yii::app()->db->createCommand($sql). This is the fastest method to access the database and also useful when we need to write complex queries such as the following:
$sql = 'select * from user where status = "active"'; $users = Yii::app()->db->createCommand($sql)->queryAll();
Both Query Builder and the DAO return data in the raw-array format, whereas Active Record returns an array of models with each representing a single row.