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

Source: Internet
Author: User
Tags joins yii

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 ']

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.