Yii 框架里数据库操作详解

增：
1 第一种

$post=new Post; $post->title="sample post"; $post->content="content for the sample post"; $post->createTime=time();/$post->createTime=new CDbexpression_r("NOW()"); $post->save();   $user_field_data= new user_field_data; $user_field_data->flag=0; $user_field_data->user_id=$profile->id; $user_field_data->field_id=$_POST["emailhiden"]; $user_field_data->value1=$_POST["email"]; $user_field_data->save();
注当一个表存储4次的时候，需要创建4个handle new4次

2 第二种

存储后我们需要找到这条记录的流水id 这样做 $profile = new profile; $profile->id;

3 第三种

用于更加安全的方法，来绑定变量类型 这样可以在同一个表中存储两个记录

$sql="insert into user_field_data(user_id,field_id,flag,value1) values(:user_id,:field_id,:flag,:value1);"; $command=user_field_data::model()->dbConnection->createCommand($sql); $command->bindParam(":user_id",$profile->id,PDO::PARAM_INT); $command->bindParam(":field_id",$_POST["firstnamehiden"],PDO::PARAM_INT); $command->bindParam(":flag",$tmpflag,PDO::PARAM_INT); $command->bindParam(":value1",$_POST["firstname"],PDO::PARAM_STR); $command->execute(); $command->bindParam(":user_id",$profile->id,PDO::PARAM_INT); $command->bindParam(":field_id",$_POST["emailhiden"],PDO::PARAM_INT); $command->bindParam(":flag",$tmpflag,PDO::PARAM_INT); $command->bindParam(":value1",$_POST["email"],PDO::PARAM_STR); $rowchange = $command->execute();   if( $rowchange != 0){ 修改成功 }// 用来判断 //注：update delete都可以用这个方法 $sql="delete from profile where id=:id"; $command=profile::model()->dbConnection->createCommand($sql); $command->bindParam(":id",$userid,PDO::PARAM_INT); $this->rowflag=$command->execute();   $sql="update profile set pass=:pass,role=:role where id=:id"; $command=profile::model()->dbConnection->createCommand($sql); $command->bindParam(":pass",$password,PDO::PARAM_STR); $command->bindParam(":role",$role,PDO::PARAM_INT); $command->bindParam(":id",$userid,PDO::PARAM_INT); $this->rowflag=$command->execute(); // 同理变更updateAll()模式 $sql="update user_field_data set flag = :flag where user_id= :user_id and field_id= :field_id "; 原始sql语句 $criteria = new CDbCriteria; $criteria->condition = "user_id = :user_id and field_id= :field_id"; $criteria->params = array(":user_id" => $userid,":field_id" => $fieldid); $arrupdate = array("flag" => $flag); if(user_field_data::model()->updateAll($arrupdate,$criteria) != 0) { 更新成功后。。。 }


4 第四种

更新和存储应用同一个handle 流程：先查询记录是否存在，若存在就更新，不存在就新创建
注：1. 第一次查询的变量，要跟save()前的变量一致。2. 存储时候需要再次 new一下库对象

$user_field_data = user_field_data::model()->findByAttributes( $attributes = array("user_id" => Yii::app()->user->user_id, "field_id" => $key)); if ($user_field_data !== null) {     $user_field_data->value1 = $value;     $user_field_data->save(); } else {     $user_field_data = new user_field_data;     $user_field_data->user_id = Yii::app()->user->user_id;     $user_field_data->field_id = $key;     $user_field_data->value1 = $value;     $user_field_data->save(); } 删：

$post=Post::model()->findByPk(10); // assuming there is a post whose ID is 10
$post->delete(); // delete the row from the database table
// delete the rows matching the specified condition
Post::model()->deleteAll($condition,$params);
// delete the rows matching the specified condition and primary key(s)
Post::model()->deleteByPk($pk,$condition,$params);

改：

例子：
$post=Post::model()->findByPk(10);
$post->title=’new post title’;
$post->save(); // save the change to database

// update the rows matching the specified condition
Post::model()->updateAll($attributes,$condition,$params);
例子：或者参考上面例子
$c=new CDbCriteria;
$c->condition=’something=1′;
$c->limit=10;
$a=array(‘name’=>’NewName’);
Post::model()->updateAll($a, $c);

// update the rows matching the specified condition and primary key(s)
Post::model()->updateByPk($pk,$attributes,$condition,$params);
例子
$profile = profile::model()->updateByPk(
Yii::app()->user->user_id,
$attributes = array(‘pass’ => md5($_POST["password"]), ‘role’ => 1));

// update counter columns in the rows satisfying the specified conditions
Post::model()->updateCounters($counters,$condition,$params);

查：
注：当项目没查找到整个对象会为空需要这样判定

if($rows !== null) 当对象不为空 {     return true; }else{     return false;

}
SELECT，读表时候用到
find（）
第一种find()

// find the first row satisfying the specified condition $post=Post::model()->find($condition,$params); // 条件查询 $post = Post::model()->find("post_id=:post_id AND status=:status", array(   ":post_id"=>8,   ":status"=>"active",

));
同样的语句，用另种方式表示

$criteria=new CDbCriteria; $criteria->select="title";  // only select the "title" column $criteria->condition="postID=:postID"; $criteria->params=array(":postID"=>10); $post=Post::model()->find($criteria); // $params is not needed
第二种find()
$post=Post::model()->find(array(     "select"=>"title",     "condition"=>"postID=:postID",     "params"=>array(":postID"=>10), )); // find the row with the specified primary key $post=Post::model()->findByPk($postID,$condition,$params);

findByAttributes()
$post=Post::model()->findByAttributes($attributes,$condition,$params);
第一种findByAttributes()

$checkuser = user_field_data::model()->findByAttributes(     array("user_id" => Yii::app()->user->user_id, "field_id" => $fieldid));
第二种findByAttributes()
$checkuser = user_field_data::model()->findByAttributes(     $attributes = array("user_id" => Yii::app()->user->user_id, "field_id" => $fieldid));
第三种当没有conditions时候，不用params
$user_field_data = user_field_data::model()->findAllByAttributes(     $attributes = array("user_id" => ":user_id"),     $condition = "field_id in (:fields)",     $params = array(":user_id" => Yii::app()->user->user_id, ":fields" => "$rule->dep_fields"));


// find the first row using the specified SQL statement
$post=Post::model()->findBySql($sql,$params);
例子
user_field_data::model()->findBySql(“select id from user_field_data where user_id = :user_id and field_id = :field_id “, array(‘:user_id’ => $userid,’:field_id’=>$fieldid));
此时回传的是一个对象

第四种 添加其他条件

$criteria = new CDbCriteria; $criteria->select ="newtime";//选择只显示哪几个字段要与库中名字相同，但是不能COUNT(newtime) as name这样写 $criteria->join = "LEFT JOIN Post ON Post.id=Date.id";//1.先要在relation函数中增加与Post表的关系语句 2.Date::model()->with("post")->findAll($criteria) $criteria->group  = "newtime"; $criteria->limit  = 2; // 都是从0开始，选取几个 $criteria-> offset = 2;// 从哪个偏移量开始 print_r(Date::model()->findAll($criteria));   //得到行数目或者其他数目 count // get the number of rows satisfying the specified condition $n=Post::model()->count($condition,$params); // get the number of rows using the specified SQL statement $n=Post::model()->countBySql($sql,$params); // check if there is at least a row satisfying the specified condition $exists=Post::model()->exists($condition,$params);     原文转自新浪博客《Yii的增删改查》