Yii Database Connection

Most Web applications have the databases. We have to configure the database details in protected/cofig/main.php to use the database. Here i explained with two types of database connectivity. They are application configuration connection, CDbConnection .

Application Configuration (config/main.php)

Sqlite Database

To connect the sqlite db with application, we need the directory details of database.

'db'=>array(
    'connectionString' => 'sqlite:'.dirname(__FILE__).'/../data/testdrive.db',
),

Mysql Database

To connect the mysql db with application, we need the hosting details, database name, username, and password.

'db'=>array(
        'connectionString' => 'mysql:host=localhost;dbname=testmysql',
        'emulatePrepare' => true,
        'username' => 'root',
        'password' => '',
        'charset' => 'utf8',
        ),

Excute Query

$user = Yii::app()->db->createCommand()
        ->select('username, password')
        ->from('tbl_user')
        ->where('id=:id', array(':id'=>1))
        ->queryRow();

CDbConnection

CDbConnection represents a connection to a database. We have to set 1 for "active", After we initialized CDbConnection. See below code for understanding How to create a CDbConnection

$connection=new CDbConnection($dsn,$username,$password);
$connection->active=true;

Excute Query

$sqlStatement="select username, password from tbl_user where id=:id limit 1";
$command=$connection->createCommand($sqlStatement);
$command->bindParam(':id',$userid);
$command->execute();   // a non-query SQL statement execution
$user=$command->query();