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
语句中,最好要经过防注入处理。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。