Database operation in Yii framework [method of adding, querying, updating and deleting ' ar mode ']

Public Function Getminlimit () {
$sql = "...";
$result = Yii::app ()->db->createcommand ($sql);
$query = $result->queryall ();
Return Array (
$query [0] [' Max '],
);
}

$connection =Yii::app ()->db;
$sql = "Select u.account,i.* from Sys_user as U left joins User_info as I on u.id=i.user_id";
$rows= $connection->createcommand ($sql)->query ();
foreach ($rows as $k = = $v) {
echo $v [' add_time '];
}

======================================== Transactions

$db = Yii::app ()->db;
$dbTrans = $db->begintransaction ();

$dbTrans->commit ();
$dbTrans->rollback ();

======================================== 

Pagination

General wording:
$criteria = new Cdbcriteria;
$count = Dnonline::model ()->count ($criteria);
$pages = new Cpagination ($count);
$pages->pagesize = 5;
$pages->applylimit ($criteria);
$model = Dnonline::model ()->findall ($criteria);

Remember there's a function in the controller paginate ($itemCount, $pageSize =null, $pageVar =null)
CDB notation:

$criteria = new Cdbcriteria;
$sql = "SELECT * from USER";
$model = Yii::app ()->db->createcommand ($sql)->queryall ();
$pages = new Cpagination (count ($model));
$pages->pagesize = 4;
$pages->applylimit ($criteria);
$model =yii::app ()->db->createcommand ($sql. " Limit:offset,:limit ");
$model->bindvalue (': Offset ', $pages->currentpage* $pages->pagesize);
$model->bindvalue (': Limit ', $pages->pagesize);
$model = $model->queryall ();



Or:

$criteria = new Cdbcriteria;
$connection =yii::app ()->db;
$sql = "Select u.account,i.* from Sys_user as U left joins User_info as I on u.id=i.user_id";
$model = Yii::app ()->db->createcommand ($sql)->queryall ();
$pages = new Cpagination (count ($model));
$pages->pagesize = 4;
$pages->applylimit ($criteria);

$model =yii::app ()->db->createcommand ($sql. " Limit:offset,:limit ");
$model->bindvalue (': Offset ', $pages->currentpage* $pages->pagesize);
$model->bindvalue (': Limit ', $pages->pagesize);
$model = $model->queryall ();

========================================

One, query data collection

1, $admin =admin::model ()->findall ($condition, $params);
The method is to query a collection based on a condition, such as:
FindAll ("Username=:name", Array (": name" = = $username));

2, $admin =admin::model ()->findallbypk ($postIDs, $condition, $params);
FINDALLBYPK ($id, "name like ': Name ' and Age=:age", Array (': Name ' = ' $name, ' age ' = $age));
The method is to query a collection based on the primary key, which can use multiple primary keys, such as:
FINDALLBYPK (array);

3, $admin =admin::model ()->findallbyattributes ($attributes, $condition, $params);
The method is to query a set based on the condition, can be multiple conditions, put the conditions in the array, such as:
Findallbyattributes (Array (' username ' = ' admin '));

4, $admin =admin::model ()->findallbysql ($sql, $params);
The method is to query an array based on an SQL statement, such as:
Findallbysql ("Select *from admin whereusername=:name", Array (': Name ' = ' admin '));

========================================

Second, query the method of the image

1, $admin =admin::model ()->findbypk ($postID, $condition, $params);
An object is queried based on the primary key, such as: FINDBYPK (1);

2, $row =admin::model (),find ($condition, $params);
A set of data is queried based on one condition, possibly multiple, but he returns only the first row of data, such as:
Find (' Username=:name ', Array (': Name ' = ' admin '));

$userinfo =userinfo::model ()->find (Array (' condition ' = ' user_id=:user_id ', ' params ' =>array (': user_id ' = > $uid)));
Print_r ($userinfo->user_id);

3, $admin =admin::model ()->findbyattributes ($attributes, $condition, $params);
The method is to query a set of data according to criteria, can be multiple conditions, put the conditions in the array, he queried the first data, such as:
Findbyattributes (Array (' username ' = ' admin '));

4, $admin =admin::model ()->findbysql ($sql, $params);
The method is to query a set of data based on the SQL statement, and he queries the first data, such as:
Findbysql ("Select *from admin whereusername=:name", Array (': Name ' = ' admin '));

5, spell a method of obtaining SQL, in accordance with the find query out an object

$criteria =new Cdbcriteria;
$criteria->select= ' username '; Only select the ' title ' column
$criteria->condition= ' Username=:username ';
$criteria->params=array (':username=> ' admin ');
$post =post::model ()->find ($criteria); $params IsNot Needed

========================================

Third, the number of queries to determine whether the query has results

1, $n =post::model ()->count ($condition, $params);
The method is to query the number of records for a collection based on a condition, and return an int type, such as
Count ("Username=:name", Array (": name" = = $username));

2, $n =post::model ()->countbysql ($sql, $params);
The method is to query the number of records in a collection based on the SQL statement, returning an int type number, such as
Countbysql ("Select *from admin whereusername=:name", Array (': Name ' = ' admin '));

3, $exists =post::model ()->exists ($condition, $params);
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

$sql = "SELECT count (*) as Count from". $this->tablename (). "Where user_id = $userId";
$query = $result->queryall ();
$pageSize = 10;
$count = $query [0] [' count '];
$pageCount = Ceil ($count/$pageSize);
return $pageCount;

======================================== 

Iv. methods to be added
$admin =newadmin;
$admin->username= $username;
$admin->password= $password;
if ($admin->save () >0) {
echo "Add success";
}else{
echo "Add failed";
}

$userLimit = new Userlimit ();
$userLimit->item = 0.30000;
if ($userLimit->insert () {

}

========================================

V. Methods of Modification

$userLimitRet = Userlimit::model ()->findbypk (Array (
' user_id ' = $userId,
' category_id ' = $v
));
$userLimitRet->order = $order;

if (! $userLimitRet->update ()) {
}

$userAmount =useramount::model ()->findbypk ($userId);
$userAmount->credit=yii::app ()->request->getparam (' Credit ', 10000);
$ret = $userAmount->save ();

1, Post::model ()->updateall ($attributes, $condition, $params);
$count =admin::model ()->updateall (Array (' username ' = ' 11111 ', ' password ' = ' 11111 '), ' password=:p ', Array (':p-up ' and ' 1111a1 ');
if ($count >0) {
echo "modified successfully";
}else{
echo "failed to modify";
}

$ret = $this->updateall (Array (
' Set ' = $Set
), "user_id in (". $userIds. ") and category_id =". $CATEGORYID);

if ($ret) {
return true;
}else {
return false;
}

 2, 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 "failed to modify";   
}  
$PK represents a primary key, which can be either a collection, $attributes represents a collection of fields to modify, $condition represents a condition, $ The value passed in by the params

 3, Post::model ()->updatecounters  ($counters, $condition, $params);  
  $count = Admin::model ()->updatecounters (Array (' status ' =>1), ' Username=:name ', Array (': Name ' = ' admin ')); 
if ($count >0) {  
   echo "modified successfully";   
}else{  
   echo "Modify failed";   
}  
Array (' status ' =>1) represents the admin table in the database according to the conditions username= ' admin ', All result status fields of the query are added 1 
 
======================================== 

Vi. Methods of deletion
1, Post::model ()->deleteall ($condition, $params);
$count = Admin::model ()->deleteall (' username=:nameandpassword=:p ", Array (': Name ' = ' Admin ', ':p ' Admin '));
$id =1,2,3
DELETEALL (' ID in ('. $id. ') '); Delete data with ID for these
if ($count >0) {
echo "Delete succeeded";
}else{
echo "Delete Failed";
}
2, 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";
}

Database operation in Yii framework [method of adding, querying, updating and deleting ' ar mode ']