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

This article provides a detailed analysis of the yii database addition, deletion, and query improvements. For more information, see 1. database access method
Storage Class 1
Used to store tables
Example:
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: When a table is stored for four times, four handle new4.

Storage type 2
After storage, we need to find the stream id of this record to do this $ profile = new profile; $ profile-> id;

Storage 3
It is used to bind variable types in a safer way, so that two records can be stored in the same table.
The code is as follows:
$ 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) {modified successfully} // used to judge
Note: This method can be used for update 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 ();
// Similarly, the updateAll () mode is changed.
$ 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 method is to update and store the app in the same handle process:
First, check whether the record exists. If yes, update the record. If no record exists, create a new record.
Note: 1. the variables queried for the first time must be consistent with those before save. 2. you need to create a new library object again during storage.
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 entire object is not found in the project, it will be empty and need to be determined as follows:
The code is as follows:
If ($ rows! = Null) when the object is not empty
{
Returntrue;
} Else {
Returnfalse;
}
SELECT

Used to read tables
Example:
First find ()
The code is as follows:
// Find thefirst 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 ));
The same statement is 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 ()
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:
First, 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 ));
Third, params is not used when there is no conditions.
$ 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 ));
An object is returned.
Fourth, add other conditions
Http://www.yiiframework.com/doc/api/CDbCriteria#limit-detail
$ Criteria = newCDbCriteria;
$ Criteria-> select = 'newtime'; // select which fields to display are the same as those in the database, but cannot be written as COUNT (newtime) as name.
$ Criteria-> join = 'left JOINPost ON Post. id = Date. ID'; // 1. first, add the relation statement to The Post table in the relation function. 2. date: model ()-> with ('post')-> findAll ($ criteria)
$ Criteria-> group = 'newtime ';
$ Criteria-> limit = 2; // The values start from 0.
$ Criteria-> offset = 2; // start from which offset
Print_r (Date: model ()-> findAll ($ criteria ));
Obtain the number of rows or other count
// Get the number of rows satisfying thespecified 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 satisfyingthe specified condition
$ Exists = Post: model ()-> exists ($ condition, $ params );
UPDATE
Example:
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 example above
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 );

Example
The code is as follows:
$ 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
Example:
The code is as follows:
$ 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

Currently
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 "));
It can be used with the database connection file relations () function in the models folder, so that it can be used for joint query.
$ Criteria = newCDbCriteria;
$ Criteria-> condition = 'Field. step_id = 1 ';
$ This-> _ post = field: model ()-> with ('step')-> findAll ($ criteria );
In this way, the array contains the values in the step table, and the condition for this value is step. id = field. step_id.
Public functionrelations ()
{
Return array (
'Step' => array (self: BELONGS_TO, 'step', 'step _ id '),
);
}