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

======================================== Sub-page

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

======================================== . 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, the method of querying 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

======================================== Three, query the number, 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 of addition
$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 () {

}

======================================== Five, the method 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 =Save ($userAmount);

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, $params an incoming value

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 "failed to modify";
}
The array (' status ' =>1) represents the admin table in the database according to the condition username= ' admin ', and all the 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";
}