yii生成sql语句操作数据库_yii生成sql语句操作数据库实例

更新时间:2019-08-23    来源:php常用代码    手机版     字体:

【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语句中,最好要经过防注入处理

本文来源:http://www.bbyears.com/jiaocheng/63865.html