YII2 Database Curd Operations

1. Simple Enquiry    $admin =admin::model ()->findall ($condition, $params);   $admin =admin::model ()- >findall ("Username=:name", Array (": Name" and "= $username)");      $INFOARR =  Newslist::model ()->findall ("status =  ' 1 '  order by id desc limit 10   ")      //2. FINDALLBYPK (the method is to query a collection based on the primary key, you can use multiple primary keys)    $admin =admin: : Model ()->FINDALLBYPK ($postIDs, $condition, $params);   $admin =admin::model ()->findallbypk ($id, " Name like :name and age=:age ", Array (': name ' = = $name, ' age ' = $age));   $admin = Admin::model ()->findallbypk (array);     //3.findallbyattributes  ( The method is to query a set based on the condition, can be multiple conditions, put the condition into the array)    $admin =admin::model ()->findallbyattributes ($attributes, $ Condition, $params);   $admin =admin::model ()->findallbyattributes (Array (' username ' = ' admin '));      //4.findallbysql  (the method is to query an array based on an SQL statement)    $admin =admin::model ()->findallbysql ($sql, $params);    $admin =admin::model ()->findallbysql ("Select * from admin where username  like :name ", Array (': Name ' = '%ad% '));   user::find ()->all ();     This method returns all data;   user::findone ($id);    This method returns a data   primary key  id=1   (for example);    user::find ()->where ([' Name '  =>  ' lad '])->one ();    This method returns  [' name '  =>  ' lad ']  a piece of data;   user::find ()->where ([' Name '  =>  ' Lad '])->all ();    This method returns all data for  [' name '  =>  ' lad '] ;   user::find ()->orderby (' Id desc ')->all ();    This method is a sort query;   user::findbysql (' SELECT *  from user ')->all ();   This method is to query  user  all the data in the table with the  sql   statement;   user::findbysql (' Select&nbsP;* from user ')->one ();   This method is to query a data in the  user  table with the  sql   statement;   user::find ()->andwhere ([' Sex '  =>  ' male ',  ' age '  =>  ') '->count (' id ');     statistics of the total number of bars that meet the criteria;   user::find ()->one ();     This method returns a data;   User::find ()->all ();     This method returns all data;   user::find ()->count ();      This method returns the number of records,   user::find ()->average ();     This method returns the average of the specified column;   user::find ()->min ();     This method returns the minimum value of the specified column  ;  user::find ()->max ();      This method returns the maximum value of the specified column  ;  user::find ()->scalar ();     This method returns the query result for the first column of the first row of the value;   user::find ()->column ();     This method returns the value of the first column in the query results;   User::find ()->exists ();     This method returns a value indicating whether the data row contains the query results;   user::find ()->batch (10 );   each fetch  10  strip data    user::find ()->each ();   per fetch  10  data,  iterative query    second, The method of querying an object   //an object based on the primary key, such as: FINDBYPK (1);   $admin =admin::model ()->findbypk ($postID, $ Condition, $params);   $admin =admin::model ()->findbypk (1);      // A set of data is queried based on one condition, possibly multiple, but he only returns the first row of data    $row =admin::model ()->find ($condition, $params);   $row = Admin::model ()->find (' Username=:name ', Array (': Name ' = ' admin '));      // The method is to query a set of data according to the conditions, can be more than one condition, put the condition into the array, the query is also the first Data    $admin =admin::model ()->findbyattributes ($attributes, $condition, $params);   $admin =admin::model ()->findbyattributes (Array (' username ' = ' admin '));      //the method is to query a set of data based on an SQL statement, and he queries the first Data    $admin =admin::model ()->findbysql ($ SQL, $params);   $admin =admin::model ()->findbysql ("Select * from admin where  username=:name ", Array (': name ' = = ' admin');      //a method of obtaining SQL, querying an object     $criteria =newcdbcriteria based on find;     $criteria->select= ' username ';// only select the  ' title '  column     $criteria->condition= ' username=:username ';     //Note that this is the condition of a query, And there is only one query condition. Multi-condition with addcondition   $criteria->params=array (":username=> ' admin '");   $ criteria->order = "Id desc";   $criteria->limit = "3";   $post =post:: Model ()->find ($criteria);//  $params  isnot needed      // Statements for multi-conditional queries    $criteria = new cdbcriteria;        $criteria- >addcondition ("id=1");//query condition, i.e. where id = 1    $criteria->addincondition (' ID ', Array (1,2,3,4,5));//For where id in  (1,2,3,4,5,);    $criteria Addnotincondition (' id ', array (1,2,3,4,5));//With the above exactly the same method, is not in    $criteria->addcondition (' id=1 ', ' or ');//This is an OR condition, when multiple conditions are, the condition is or not and     $criteria->addsearchcondition (' name ', ' category ');//search conditions, in fact, represents the. where name like  '% category% '     $criteria->addbetweencondition (' id ',  1, 4) ;//between 1 and 4   $criteria->compare (' id ',  1);    //This method is very special, He will automatically process the addcondition or addincondition.   $criteria according to your parameters (' id ', ' array ');    //that is, if the second argument is an array, it calls addincondition          $criteria select = ' id,parentid,name ';//represents the field to query, the default select= ' * ';    $criteria->join = ' xxx ';  //Connection Table     $criteria->with = ' xxx ';  //call relations    $ criteria->limit = 10;   //Fetch 1 data, if less than 0, do not process     $criteria->offset  = 1;  //Two is combined, it means  limit 10 offset 1,or represent it. limit 1,10    $criteria->order = ' xxx desc,xxx asc '  ;//sorting criteria      $criteria->group = ' group  conditions ';    $criteria->having = ' having   conditions   ';    $criteria->distinct = false;//is the only query    three, the number of queries, Determine if the query has results   //the method is to query the number of records for a set based on a condition, return an int type number    $n =post::model ()->count ($condition, $ params);   $n =post::model ()->count ("Username=:name", Array (": name" = = $username));      //the method is to query the number of records for a collection based on the SQL statement, returning an int type number    $n =post::model ()->countbysql ($sql, $params );   $n =post::model ()->countbysql ("Select * from admin where username=: Name ", Array (': name ' = = ' admin '));      //The method is based on a conditional query query to get the array with no data, if there is data returned by a true, Otherwise not found    $exists =post::model ()->exists ($condition, $params);   $exists =post::model () Exists ("Name=:name", Array (": Name" => $username));   IV, new    $admin = new admin;           $admin->username = $username;   $admin->password = $password;   if ($admin->save ()  > 0) {echo  "Add Success"; }else{echo  "Add Failed"; }   V. Modification of   post::model ()->updateall ($attributes, $condition, $params);   $count =admin::model ()- >updateall (Array (' username ' = ' 11111 ', ' password ' = ' 11111 '), ' password=:p ', Array (':p ' and ' 1111a1 ') );   if ($count > 0) { echo  "Modify succeeded"; }else{echo  "Modify failed"; }       $rt = postlist::model ()->updateall (Array (' status ' = ' 1 '), ' Staff_id=:staff and  host_id=:host ', Array (': Staff ' = + $staff _id, ': host ' = + $host _id));      //$PK primary key , which can be either a collection, $attributes is a collection of fields to modify, $condition condition, $params the value passed in   post::model ()->UPDATEBYPK ($PK, $ attributes, $condition, $params);   $count =admin::model ()->updatebypk (1,array (' username ' = ' Admin ', ' password ' = ') Admin ');   $count =admin::model ()->updatebypk (array), Array (' username ' = ' admin ', ' password ' = > ' admin '), ' Username=:name ', Array (': Name ' = ' admin '),   if ($count >0) {echo  "modified successfully";  } else{echo  "modification Failed";  }     post::model ()->updatecounters ($counters, $ Condition, $params);   $count =admin::model ()->updatecounters (Array (' status ' =>1), ' Username=:name ' , Array (': Name ' = ' admin ');   if ($count > 0) { echo  "Modify Success"; }else{echo  " The modification failed ";  }  //array (' status ' =>1) represents the admin table in the database according to the conditions username= ' admin ', the query out all the results of the Status field is self-added 1    VI. Delete   //deleteall  post::model ()->deleteall ($condition, $params);   $ Count= admin::model ()->deleteall (' username=:name and password=:p ', Array (': Name ' = ') ' Admin ', ':p of the ' (') ' and ' admin ');    $count = admin::model ()->deleteall (' Id in ("" "),"//delete data with ID for these   if ($count > 0) {echo "Delete succeeded"; }else{echo  "delete failed";  }     //deletebypk  post:: Model ()->DELETEBYPK ($PK, $condition, $params);   $count = admin::model ()->deletebypk (1);    $count =admin::model ()->deletebypk (array), ' Username=:name ', Array (': Name ' = ' Admin '));   if ($count >0) {echo  "Delete succeeded"; }else{echo  "delete failed";  }  //createcommand ( Execute native SQL statement)    $sql =  "Select u.account,i.* from sys_user as u left  join user_info as i on u.id=i.user_id ";   $rows =yii:: $app->db-> CreateCommand ($sql)->query ()   foreach ($rows  as  $k  =>  $v) {       echo$v[' add_time '];  }     query returns multiple rows:      $command  =  $connEction->createcommand (' select * from post ');   $posts  =  $command Queryall ();   returns a single line:     $command  =  $connection->createcommand (' SELECT  * from post where id=1 ');   $post  =  $command->queryone ();    querying multiline single-valued:     $command  =  $connection->createcommand (' select  Title from post ');   $titles  =  $command->querycolumn ();   query for scalar values/computed values:      $command  =  $connection->createcommand (' select count (*)  from  post ');   $postCount  =  $command->queryscalar ();   $command  = $ Connection->createcommand (' update post set status=1 where id=1 ');   $ Command->execute ();   $connection->createcommand ()->insert (' user ', [        ' name '  =≫  ' Sam ',       ' age '  => 30,  ]->execute ();     // INSERT  insert multiple lines at once    $connection->createcommand ()->batchinsert (' user ' ,  [' name ',  ' age '], [      [' Tom ', 30],       [' Jane ',  20],      [' Linda ',  25],  ]) Execute ();    // update   $connection->createcommand ()->update (' User ',  [' status '  => 1],  ' age > 30 ')->execute ();     //  DELETE   $connection->createcommand ()->delete (' user ',  ' status = 0 ') Execute ();   //transaction basic structure (multi-table update insert operation use transacted)    $dbTrans = yii::app ()->db-> BeginTransaction ();  try{       $post = new post;        $post' title ' =  ' Hello dodobook!!! ';       if (! $post->save ()) throw newexception ("error processing  Request ",  1);       $dbTrans->commit ();  //   $this- >_end (0, ' Add success!!! ');   }catch (exception$e) {       $dbTrans->rollback ();   //    $this->_end ($e->getcode (), $e->getmessage ());

This article is from the "php/[email protected]" blog, be sure to keep this source http://liang3391.blog.51cto.com/178205/1859532

YII2 Database Curd Operations