yii生成sql语句操作数据库实例,直接实施SQL语句

yii框架使用原生态的sql语句也是足以对数据库张开操作的,以下正是事无巨细的操作代码,很详细:

例1

$connection = Yii::app()->db;  
$sql = “SELECT * FROM `project` ORDER BY id DESC”;  
$command = $connection->createCommand($sql);  
$result = $command->queryAll();  
print_r($result);  

yii操作数据库的落到实处形式有过三种,以下计算出一套yii操作数据库的言语:

 代码如下

$connection = Yii::app()->db;
$sql = “SELECT * FROM `project` ORDER BY id DESC”;
$command = $connection->createCommand($sql);
$result = $command->queryAll();

 

1. 询问数据(select)

<?php
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”;
www.5929.com,$command = $con->createCommand($sql);
$res = $command->execute();
print_r($res);
//查询结果
$sql = “select * from user”;
$command = $con->createCommand($sql);
$res = $command->queryAll();
print_r($res);

print_r($result); 

yii生成sql语句操作数据库实例,直接实施SQL语句。$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”;  

 代码如下

}

例2

}   

$con = Yii::app()->db;
//查询一行数据
$noteInfo = $con->createCommand(“select * from note where
note_id='{$nid}'”)->queryRow();
//查询多行数据
$noteInfo = $con->createCommand(“select * from note where
note_id='{$nid}'”)->queryAll();

}

 $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”;
}

表明:把询问条件作为参数(如例2),相比较安全,可一直防止注入。若是直接用在SQL语句中,最佳要通过防注入管理。 

 代码如下

Array
(
    [0] => Array
        (
            [id] => 1
            [integral] => 3000
            [name] => aa
        )

yii生成sql语句操作数据库实例,直接实施SQL语句。 例3

 

1、$admin=Admin::model()->findAll ($condition,$params);
该情势是根据三个规范查询三个成团,如: 
findAll(“username=:name”,array(“:name”=>$username));   

    [1] => Array
        (
            [id] => 2
            [integral] => 2000
            [name] => aa
        )

 $sql = “select sum(if(starttime>’09:00:00′,1,0)) as late,

 

2、$admin=Admin::model()->findAllByPk($postIDs,$condition,$params);
findAllByPk($id,”name like ‘:name’ and
age=:age”,array(‘:name’=>$name,’age’=>$age));   
该办法是依据主键查询三个汇聚,可以选用多少个主键,如: 
findAllByPk(array(1,2));  

    [2] => Array
        (
            [id] => 3
            [integral] => 1000
            [name] => bb
        )

  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”;
}

 $sql = “select sum(if(starttime>’09:00:00′,1,0)) as late,
 

3、$admin=Admin::model()->findAllByAttributes($attributes,$condition,$params);

)

例4

  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”;  
}  

该方法是依靠法则查询二个凑合,能够是多少个尺码,把原则放到数组里面,如: 
findAllByAttributes(array(‘username’=>’admin’));

1

$oDbConnection = Yii::app()->db; // Getting database connection (config/main.php has to set up database
// Here you will use your complex sql query using a string or other yii ways to create your query
$oCommand = $oDbConnection->createCommand(‘SELECT * FROM my_table WHERE myAttr = :myValue’);
// Bind the parameter
$oCommand->bindParam(‘:myValue’, $myValueThatCameFromPostOrAnywereElse, PDO::PARAM_STR);
 

 

4、$admin=Admin::model()->findAllBySql($sql,$params);
该方法是基于SQL语句询问多少个数组,如:
findAllBySql(“select *from admin
whereusername=:name”,array(‘:name’=>’admin’));

1

$oCDbDataReader = $oCommand->queryAll(); // Run query and get all results in a CDbDataReader 

  1. 加上数量(insert)

Array
(
    [0] => Array
        (
            [id] => 2
            [integral] => 2000
            [name] => aa
        )

 

$con = Yii::app()->db;
$datas[‘title’] =
Yii::app()->request->getParam(‘title’);
$datas[‘content’] = Yii::app()->request->getParam(‘content’);;
$datas[‘add_time’] = time();
//入库
$res = $con->createCommand()->insert(‘note’,$datas);

    [1] => Array
        (
            [id] => 3
            [integral] => 1000
            [name] => bb
        )

评释:把询问条件作为参数(如例2),相比安全,可一贯制止注入。倘若直接用在SQL语句中,最佳要透过防注入管理。

$admin=newAdmin;       
$admin->username=$username;
$admin->password=$password;
if($admin->save ()>0){
   echo “增多职业有成”; 
}else{ 
   echo “增加退步”; 
}
 
 
 
$userLimit = new UserLimit();
$userLimit->item = 0.30000;
if($userLimit->insert (){
 
}

    [2] => Array
        (
            [id] => 4
            [integral] => 999
            [name] => www.111cn.net
        )

come from  
 

3. 修改数据(update)

)

 

 代码如下

这种办法,有个别时候还能够用获得的。

$con = Yii::app()->db;
$content = ‘abc’;
$nid = ‘123’;
$con->createCommand()->update(‘note’,
 array(
 ‘content’ => $content,
 ‘last_update’ => time()
 ), ‘note_id=:nid’, array(‘:nid’ => $nid));

Php代码

$userLimitRet = UserLimit::model()->findByPk (array (
                                ‘user_id’ => $userId,
                                ‘category_id’ => $v
                        ));
$userLimitRet->order = $order;
if (! $userLimitRet->update ()) {
}
 
 
 
 
 
$userAmount=userAmount::model()->findByPk ($userId);
$userAmount->credit=Yii::app()->request->getParam(‘credit’,10000);
$ret = $userAmount->save ();
 
 
 

 代码如下

1、Post::model()->updateAll ($attributes,$condition,$params);
 $count
=Admin::model()->updateAll(array(‘username’=>’11111′,’password’=>’11111′),’password=:pass’,array(‘:pass’=>’1111a1’));
if($count>0){ 
   echo “修改成功”; 
}else{ 
   echo “修改战败”; 
}
 
 
 
 
 
$ret = $this->updateAll (array (
                                ‘set’ => $Set
                        ), ” user_id in ( ” . $userIds . ” ) and
category_id = ” . $categoryId);
if($ret){
    return true;
}else {
    return false;
}
 
  2、Post::model()->updateByPk
($pk,$attributes,$condition,$params);
 $count
=Admin::model()->updateByPk(1,array(‘username’=>’admin’,’password’=>’admin’));
$count
=Admin::model()->updateByPk(array(1,2),array(‘username’=>’admin’,’password’=>’admin’),’username=:name’,array(‘:name’=>’admin’));
if($count>0){ 
   echo “修改成功”; 
}else{ 
   echo “修改退步”; 

    $connection = Yii::app()->db; 
    $sql = “SELECT * FROM `project` ORDER BY id DESC”; 
    $command = $connection->createCommand($sql); 
    $result = $command->queryAll(); 
    print_r($result); 

4. 去除数据(delete)

例2

 代码如下

Php代码

$con = Yii::app()->db;
$con->createCommand()->delete(‘note’,
‘note_id=:note_id’,array(‘:note_id’=>123));

 代码如下

    $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语句中,最佳要透过防注入处理

Leave a Comment.