Yii 自带了丰富的功能 ，包括 MVC，DAO/ActiveRecord，I18N/L10N，缓存，身份验证和基于角色的访问控制，脚手架，测试等，可显著缩短开发时间。


Yii里面的 DAO/ActiveRecord 已经很方便我们进行数据库的操作，但有些时候还需要自己写一些 sql 语句去查询等。在 Yii 里面可以通过以下方式直接执行 sql 语句:


在此之前需要在 config/main.php 里面配置数据相关信息:


'db'=>array(

   'class' => 'CDbConnection',

   'connectionString' => 'mysql:host=127.0.0.1;port=3607;dbname=dbname',

   'emulatePrepare' => true,

   'username' => 'root',

   'password' => 'mysql',

   'charset' => 'utf8',

   'tablePrefix'=>'pf_',

  ),

然后再通过 Yii::app()->db 进行调用。


方式一:

$db = Yii::app()->db;

$sql = "SELECT * FROM `news` ORDER BY id DESC";

$command = $db->createCommand($sql);

$result = $command->queryAll();

print_r($result);

=============================================================


方式二:

$db = Yii::app()->db;

$sql = "select sum(if(starttime>'09:00:00',1,0)) as late,sum(if(endtime<'18:00:00',1,0)) as early from present where userid=:userid and date between :date_start and :date_end";

$results = $db->createCommand($sql)->query(array(

  ':userid' => 115,':date_start'=>'2009-12-1',':date_end'=>'2009-12-31',

));

foreach($results as $result){

  echo $result['late']," and ",$result['early']," /n";

}

=============================================================


方式三:

$sql = "select sum(if(starttime>'09:00:00',1,0)) as late,sum(if(endtime<'18:00:00',1,0)) as early from present where userid=115 and date between '2009-12-1' and '2009-12-31'";

$results = $db->createCommand($sql)->query();

foreach($results as $result){

  echo $result['late']," and ",$result['early']," /n";

}

=============================================================


方式四:

$db = Yii::app()->db;

$oCommand = $db->createCommand('SELECT * FROM news WHERE title = :title');

$oCommand->bindParam(':title', $title, PDO::PARAM_STR);

$result = $oCommand->queryAll();

print_r($result);

=============================================================


说明：把查询条件作为参数（如方式二），比较安全，可直接避免注入。要是直接用在 SQL 语句中，最好要经过防注入处理。