yii框架使用原生态的sql语句也是可以对数据库进行操作的，以下就是详细的操作代码，很详细：
| 代码如下 |
复制代码 |
|
class IndexController extends Controller
{
public function actionIndex()
{
$con = Yii::app()->db;//数据库连接
//查询
$sql = "select * from user";
$command = $con->createCommand($sql);
$res = $command->queryAll();
print_r($res);
//插入
$sql = "insert into user (integral,name) values (999,'www.111com.net')";
$command = $con->createCommand($sql);
$res = $command->execute();
print_r($res);
//删除
$sql = "delete from user where id=1";
$command = $con->createCommand($sql);
$res = $command->execute();
print_r($res);
//查询结果
$sql = "select * from user";
$command = $con->createCommand($sql);
$res = $command->queryAll();
print_r($res);
}
}
运行后的结果为：
Array
(
[0] => Array
(
[id] => 1
[integral] => 3000
[name] => aa
)
[1] => Array
(
[id] => 2
[integral] => 2000
[name] => aa
)
[2] => Array
(
[id] => 3
[integral] => 1000
[name] => bb
)
)
1
1
Array
(
[0] => Array
(
[id] => 2
[integral] => 2000
[name] => aa
)
[1] => Array
(
[id] => 3
[integral] => 1000
[name] => bb
)
[2] => Array
(
[id] => 4
[integral] => 999
[name] => www.111com.net
)
)
|
这种方法，有些时候还是能够用得到的。
Php代码
| 代码如下 |
复制代码 |
|
$connection = Yii::app()->db;
$sql = "SELECT * FROM `project` ORDER BY id DESC";
$command = $connection->createCommand($sql);
$result = $command->queryAll();
print_r($result);
|
例2
Php代码
| 代码如下 |
复制代码 |
|
$db = Yii::app()->db; //you have to define db connection in config/main.php
$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";
}
|
例3
Php代码
| 代码如下 |
复制代码 |
|
$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";
}
|
说明：把查询条件作为参数（如例2），比较安全，可直接避免注入。要是直接用在SQL语句中，最好要经过防注入处理
