【www.bbyears.com--php常用代码】
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.111cn.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.111cn.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语句中,最好要经过防注入处理