Analyze the addition, deletion, query and modification of yii database

1. Database access method
Storage the first type
Used when saving tables
Example:

Copy code The code is as follows:

$post=new Post;
$post->title='samplepost';
$post-> ;content='content for thesample post';
$post->createTime=time();/$post->createTime=newCDbexpression_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();

Note that when a table is stored 4 times, 4 handle news need to be created 4 times

Store the second type
After storing, we need to find the serial ID of this record. Do this $profile = new profile;$profile->id;

Store the third
for a safer way to bind variable types so that two records can be stored in the same table

Copy code The code is as follows:

$sql="insert intouser_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){ Modification successful}//Used to judge 
Note: This method can be used for update and delete
$sql="delete from profile whereid=:id";
$command=profile::model()->dbConnection->createCommand( $sql);
$command->bindParam(":id",$userid,PDO::PARAM_INT);
$this->rowflag=$command->execute();
 $sql="update profile setpass=: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();
// Change the updateAll() mode in the same way 
$sql="update user_field_data set flag =:flag where user_id= :user_id and field_id= :field_id ";
Original sql statement
$criteria = newCDbCriteria;
$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)
{
After the update is successful. . . 
}

The fourth update and storage application use the same handle process:
First query whether the record exists, update it if it exists, and create it if it does not exist.
Note: 1. The variable queried for the first time must be consistent with the variable before save(). 2. When storing, you need to new the library object

Copy code The code is as follows:

$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();
}

Query
Note: When the project is not found, the entire object will be empty and needs to be determined like this

Copy the code The code is as follows:

if($rows !== null) When the object is not empty
{
returntrue;
}else{
returnfalse;
}
SELECT

Use
when reading tables. Example:
The first find()

Copy codeThe code is as follows:

// find the first row satisfying the specified condition
$post=Post::model()->find($condition,$params);
 // find the row with postID=10
$post=Post::model()->find('postID=:postID',array(':postID'=>10));
Same statement, expressed in another way
$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

Second find()

Copy code The code is as follows :

$post=Post::model()->find(array(
'select'=>'title',
'condition'=>'postID =:postID',
'params'=>array(':postID'=>10),
));
// find the row with the specified primarykey
$post= Post::model()->findByPk($postID,$condition,$params);
// find the row with the specified attributevalues
$post=Post::model()->findByAttributes( $attributes,$condition,$params);

Example:
The first findByAttributes()
$checkuser= user_field_data::model()-> ;findByAttributes(
array('user_id' =>Yii::app()->user->user_id, 'field_id'=> $fieldid));
The second type of findByAttributes ()
$checkuser =user_field_data::model()->findByAttributes(
$attributes = array('user_id'=>Yii::app()->user->user_id , 'field_id'=> $fieldid));
The third method is that when there are no conditions, params are not needed
$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 SQLstatement
$post= Post::model()->findBySql($sql,$params);
Example
user_field_data::model()->findBySql("selectid from user_field_data where user_id = :user_id and field_id =:field_id ", array(':user_id' =>$userid,':field_id'=>$fieldid));
What is returned at this time is an object
The fourth method is to add other conditions
http://www.yiiframework.com/doc/api/CDbCriteria#limit-detail
$criteria = newCDbCriteria;
$criteria->select='newtime';//Select only Which fields to display must have the same names as those in the library, but you cannot write COUNT(newtime) as name like this
$criteria->join = 'LEFT JOINPost ON Post.id=Date.id'; //1. First. To add a relationship statement with the Post table in the relationship function 2.Date::model()->with('post')->findAll($criteria)
$criteria->group ='newtime' ;
$criteria->limit = 2; //They all start from 0, select a few
$criteria->offset = 2;//Which offset to start from
print_r(Date ::model()->findAll($criteria));
Get the number of rows or other numbers count
// get the number of rows satisfying the specified condition
$n=Post::model() ->count($condition,$params);
// get the number of rows using the specifiedSQL 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);
UPDATE
Example:

Copy code The code is as follows:

$post=Post::model()->findByPk (10);
$post->title='new posttitle';
$post->save(); // save thechange to database
// update the rows matching the specifiedcondition
Post::model()->updateAll($attributes,$condition,$params);

Example: Or refer to the above example

Copy Code The code is as follows:

$c=new CDbCriteria;
$c->condition='something=1';
$c->limit =10;
$a=array('name'=>'NewName');
Post::model()->updateAll($a,$c);
// update the rows matching the specifiedcondition 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 rowssatisfying the specified conditions
Post::model()->updateCounters($counters,$condition,$params);

DELETE
例子：

复制代码代码如下:

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

目前可以取出的
1.//$allquestion=field::model()->findAllBySql("selectlabel from field where step_id = :time1 ", array(':time1'=>1));
2. //$criteria=new CDbCriteria;
//$criteria->select='label,options';
//$criteria->condition='step_id=:postID';
//$criteria->params=array(':postID'=>1);
//$allquestion=field::model()->findAll($criteria);
//$allquestion=field::model()->find("",array("label"));
可以与在models文件夹中的库连接文件relations()函数合用，这样可以联合查询
$criteria=newCDbCriteria;
$criteria->condition='field.step_id=1';
$this->_post=field::model()->with('step')->findAll($criteria);
这样出来的数组里面包含step表中的值，且这个值的条件为step.id=field.step_id
public functionrelations()
{
return array(
'step'=>array(self::BELONGS_TO,'step', 'step_id'),
);
}

Analyze the addition, deletion, query and modification of yii database_PHP tutorial