Yii DAO consists: CDbConnection, CDbCommand, CDbDataReader and CDbTransaction. Post of examples

$connection=new CDbConnection($dsn,$username,$password);
$connection->active=true;
$connection->active=false;  // close connection
 
to use, configure in a db app component in the app config
 
array(
    ......
    'components'=>array(
        ......
        'db'=>array(
            'class'=>'CDbConnection',
            'connectionString'=>'mysql:host=localhost;dbname=testdb',
            'username'=>'root',
            'password'=>'password',
            'emulatePrepare'=>true,  // needed by some MySQL installations
        ),
    ),
)
 
//if done can use as
$connection=Yii::app()->db;
$command=$connection->createCommand($sql);
 
//if INSERT, UPDATE and DELETE
$rowCount=$command->e xecute();
 
//if SELECT   
$dataReader=$command->query(); 
// if return all rows of result  
$rows=$command->queryAll();      
 
 
// go through every row of data
foreach($dataReader as $row) { ... }
// get all in a single array
$rows=$dataReader->readAll();
 
//4th part of DAO - transaction
$transaction = $connection->beginTransaction();
try
{
    $connection->createCommand($sql1)->e xecute();// etc
    $transaction->commit();
}
catch(Exception $e) // an exception is raised if a query fails
{
    $transaction->rollback();
}
//Binding Parameters
$sql="INSERT INTO user (username, email) VALUES(:username,:email)";
$command=$connection->createCommand($sql);
$command->bindParam(":username",$username,PDO::PARAM_STR);
$command->bindParam(":email",$email,PDO::PARAM_STR);
$command->e xecute();
// possible for Binding Columns
 
//create query builder and e xecute with DAO
$user = Yii::app()->db->createCommand()
    ->select('id, username, profile')
    ->from('user u')
    ->join('profile p', 'u.id=p.user_id')
    ->where('id=:id', array(':id'=>$id))
    ->queryRow();
 
// using CDbCriteria with DAO, no AR used
$criteria = new CDbCriteria;//config then
$builder = new CDbCommandBuilder(Yii::app()->db->gets chema());
//use table name and criteria
$command = $builder->createFindCommand('product', $criteria); 
$productIds = $command->queryAll();
 
//CArrayDataProvider implements a data provider based on a raw data array
$rawData=Yii::app()->db->createCommand('SELECT * FROM tbl_user')->queryAll();
// or using: $rawData=User::model()->findAll();
$dataProvider=new CArrayDataProvider($rawData, array(
    'id'=>'user',
    'sort'=>array(
        'attributes'=>array(
             'id', 'username', 'email',
        ),
    ),
    'pagination'=>array(
        'pageSize'=>10,
    ),
));
// $dataProvider->getData() will return a list of arrays.
 
//LEFT JOIN DAO
$criteria=new CDbCriteria(array(
'select'=>'*',
'distinct'=>true,
'alias'=>'c',
'join'=>'left join category_description cs ON c.category_id=cd.category.id',
'condition'=>'c.category_id=:category and cd.site_id=:site',
'params'=>array(
':category'=>(int)$category_id,
':site'=>(int)$site_id,
),
));
$dataReader=Yii::app()->db->commandBuilder->createFindCommand('category',$criteria)->query();
foreach($dataReader as $row)
{
//обработка результата
}

One comment

#182
Denny says:
Thursday, October 2, 2014 11:11 AM
Thiss article gives clear idea in favor of thee new visitors of blogging,
that really howw to do blogging.

Leave a Comment

Fields with * are required.

Please enter the letters as they are shown in the image above.
Letters are not case-sensitive.