YII2 Database Curd Operations

Source: Internet
Author: User
Tags scalar yii

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.