PHP CPagination::applylimit方法代碼示例

 /**
  * 顯示問題列表
  */
 public function actionIndex()
 {
     $sql = "select `{{question}}`.`id`,`{{question}}`.`question_content`,`{{question}}`.`add_time`,`{{question}}`.`update_time`,`{{question}}`.`answer_count`,`{{question}}`.`view_count`,`{{question}}`.`lock`,`{{question}}`.`focus_count`,`{{question}}`.`comment_count`,`{{question}}`.`best_answer`,`{{question}}`.`ip`,`{{users}}`.`username`,`{{users}}`.`uid`\n\t\t\t\tfrom `{{question}}`\t\t\t\t\n\t\t\t\tleft join `{{users}}` on (`{{users}}`.`uid` = `{{question}}`.`published_uid`)";
     $condition = array();
     //根據uid搜索
     if (isset($_GET['uid'])) {
         $condition[] = "`{{users}}`.`uid`={$_GET['uid']} ";
     }
     if (isset($_POST['content'])) {
         $condition[] = " `{{question}}`.`question_content` like '%{$_POST['content']}%'";
     }
     $conditions = implode('AND', $condition);
     //var_dump($conditions);
     if ($conditions) {
         $conditions = ' where ' . $conditions;
         $sql .= $conditions;
     }
     $sql .= " order by `{{question}}`.`add_time` desc ";
     $connection = Yii::app()->db;
     $criteria = new CDbCriteria();
     $models = $connection->createCommand($sql)->queryAll();
     $count = count($models);
     $pages = new CPagination($count);
     $pages->pageSize = 12;
     $pages->applylimit($criteria);
     $models = $connection->createCommand($sql . " LIMIT :offset,:limit");
     $models->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $models->bindValue(':limit', $pages->pageSize);
     $models = $models->queryAll();
     $this->render('index', array('models' => $models, 'pages' => $pages, 'count' => $count));
 }

 public function actionIndex($uid)
 {
     //自己才能看自己的個人中心
     if ($uid != Yii::app()->user->id) {
         $this->redirect('/');
     }
     //顯示用戶資料
     $user_model = Users::model()->findByPk($uid);
     //獲取ype
     if (isset($_GET['type']) && $_GET['type'] == 'focus') {
         //獲取關注的問題
         $sql = "select `{{question}}`.`id`,`{{question}}`.`question_content`,`{{question}}`.`add_time`,`{{question}}`.`published_uid`,`{{question}}`.`lock`,`{{question}}`.`best_answer`,`{{question}}`.`answer_count`,`{{question}}`.`view_count` from `{{question}}` left join `{{question_focus}}` on (`{{question_focus}}`.`question_id`=`{{question}}`.`id`) where `{{question_focus}}`.`uid`={$uid} order by `{{question_focus}}`.`add_time` desc";
     } elseif (isset($_GET['type']) && $_GET['type'] == 'answer') {
         //獲取回複的問題
         $sql = "select `{{question}}`.`id`,`{{question}}`.`question_content`,`{{question}}`.`add_time`,`{{question}}`.`published_uid`,`{{question}}`.`lock`,`{{question}}`.`best_answer`,`{{question}}`.`answer_count`,`{{question}}`.`view_count` from `{{question}}` left join `{{answer}}` on (`{{answer}}`.`question_id`=`{{question}}`.`id`) where `{{answer}}`.`uid`={$uid}";
     } else {
         //獲取自己發布的問題
         $sql = "select `{{question}}`.`id`,`{{question}}`.`question_content`,`{{question}}`.`add_time`,`{{question}}`.`published_uid`,`{{question}}`.`lock`,`{{question}}`.`best_answer`,`{{question}}`.`answer_count`,`{{question}}`.`view_count` from `{{question}}` where `{{question}}`.`published_uid`={$uid} order by `{{question}}`.`add_time` desc";
     }
     $connection = Yii::app()->db;
     $criteria = new CDbCriteria();
     $models = $connection->createCommand($sql)->queryAll();
     $count = count($models);
     $pages = new CPagination($count);
     $pages->pageSize = 10;
     $pages->applylimit($criteria);
     $models = $connection->createCommand($sql . " LIMIT :offset,:limit");
     $models->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $models->bindValue(':limit', $pages->pageSize);
     $models = $models->queryAll();
     $this->render('index', array('user_model' => $user_model, 'models' => $models, 'pages' => $pages, 'count' => $count));
 }

 public function actionIndex($id)
 {
     //搜索內容
     $sql = "select `{{question}}`.`id`,`{{topic}}`.`topic_title`,`{{question}}`.`question_content`,`{{question}}`.`lock`,`{{question}}`.`best_answer`,`{{question}}`.`view_count`,`{{question}}`.`answer_count` from `{{question}}` left join `{{topic_question}}` on (`{{topic_question}}`.`question_id`=`{{question}}`.`id`) left join `{{topic}}` on (`{{topic}}`.`id`=`{{topic_question}}`.`topic_id`) where `{{topic}}`.`id`={$id} ";
     $question_models = Yii::app()->db->createCommand($sql)->queryAll();
     $connection = Yii::app()->db;
     $criteria = new CDbCriteria();
     $count = count($question_models);
     $pages = new CPagination($count);
     $pages->pageSize = 10;
     $pages->applylimit($criteria);
     $question_models = $connection->createCommand($sql . " LIMIT :offset,:limit");
     $question_models->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $question_models->bindValue(':limit', $pages->pageSize);
     $question_models = $question_models->queryAll();
     $this->render('index', array('count' => $count, 'question_models' => $question_models, 'pages' => $pages));
 }

 public function actionIndex()
 {
     $sql = "select `{{users}}`.`uid`,`{{users}}`.`username`,`{{users}}`.`email`,`{{users}}`.`avatar_file`,`{{users}}`.`sex`,`{{users}}`.`birthday`,`{{users}}`.`reg_time`,`{{users}}`.`reg_ip`,`{{users}}`.`last_login`,`{{users}}`.`last_ip` from `{{users}}`";
     //查找用戶
     if (isset($_POST['content'])) {
         $sql .= " where `{{users}}`.`username` like '%{$_POST['content']}%'";
     }
     $connection = Yii::app()->db;
     $criteria = new CDbCriteria();
     $models = $connection->createCommand($sql)->queryAll();
     $count = count($models);
     $pages = new CPagination($count);
     $pages->pageSize = 14;
     $pages->applylimit($criteria);
     $models = $connection->createCommand($sql . " LIMIT :offset,:limit");
     $models->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $models->bindValue(':limit', $pages->pageSize);
     $models = $models->queryAll();
     $this->render('index', array('models' => $models, 'pages' => $pages, 'count' => $count));
 }

 public function actionIndex($word)
 {
     //獲取搜索關鍵字
     if ($word == '') {
         $this->error('未填入搜索關鍵字');
     }
     //搜索內容
     $sql = "select `id`,`question_content`,`lock`,`best_answer`,`view_count`,`answer_count` from {{question}} where `question_content` like '%{$word}%'";
     $question_models = Yii::app()->db->createCommand($sql)->queryAll();
     $connection = Yii::app()->db;
     $criteria = new CDbCriteria();
     $count = count($question_models);
     $pages = new CPagination($count);
     $pages->pageSize = 10;
     $pages->applylimit($criteria);
     $question_models = $connection->createCommand($sql . " LIMIT :offset,:limit");
     $question_models->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $question_models->bindValue(':limit', $pages->pageSize);
     $question_models = $question_models->queryAll();
     $this->render('index', array('count' => $count, 'question_models' => $question_models, 'pages' => $pages, 'word' => $word));
 }

 /**
  * 顯示主頁
  * @param String $order
  */
 public function actionIndex($order = "new")
 {
     //根據不同的order 選擇不同的sql語句
     switch ($order) {
         case 'new':
             $sql = "select `{{question}}`.`id` as `question_id`,`{{question}}`.`question_content`,`{{question}}`.`add_time`,`{{question}}`.`answer_count`,`{{question}}`.`view_count`,`{{question}}`.`lock`,`{{question}}`.`best_answer`,`{{users}}`.`avatar_file`,`{{users}}`.`uid`\n\t\t\t\tfrom `{{question}}`\n\t\t\t\tleft join `{{users}}` on (`{{question}}`.`published_uid`=`{{users}}`.`uid`) order by `{{question}}`.`add_time` desc\n\t\t\t\t";
             break;
             //熱門查看人數排序
         //熱門查看人數排序
         case 'hot':
             $sql = "select `{{question}}`.`id` as `question_id`,`{{question}}`.`question_content`,`{{question}}`.`add_time`,`{{question}}`.`answer_count`,`{{question}}`.`view_count`,`{{question}}`.`lock`\n\t\t\t\t,`{{users}}`.`avatar_file`,`{{users}}`.`uid`\n\t\t\t\tfrom `{{question}}`\n\t\t\t\tleft join `{{users}}` on (`{{question}}`.`published_uid`=`{{users}}`.`uid`) order by `{{question}}`.`view_count` desc\n\t\t\t\t";
             break;
             //未回複數排序
         //未回複數排序
         case 'unresponsive':
             $sql = "select `{{question}}`.`id` as `question_id`,`{{question}}`.`question_content`,`{{question}}`.`add_time`,`{{question}}`.`answer_count`,`{{question}}`.`view_count`,`{{question}}`.`lock`\n\t\t\t\t,`{{users}}`.`avatar_file`,`{{users}}`.`uid`\n\t\t\t\tfrom `{{question}}`\n\t\t\t\tleft join `{{users}}` on (`{{question}}`.`published_uid`=`{{users}}`.`uid`) where `{{question}}`.`answer_count` = 0\n\t\t\t\t";
             break;
             //默認根據時間排序
         //默認根據時間排序
         default:
             $sql = "select `{{question}}`.`id` as `question_id`,`{{question}}`.`question_content`,`{{question}}`.`add_time`,`{{question}}`.`answer_count`,`{{question}}`.`view_count`,`{{question}}`.`lock`\n\t\t\t\t,`{{users}}`.`avatar_file`,`{{users}}`.`uid`\n\t\t\t\tfrom `{{question}}`\n\t\t\t\tleft join `{{users}}` on (`{{question}}`.`published_uid`=`{{users}}`.`uid`) order by `{{question}}`.`add_time` desc\n\t\t\t\t";
             break;
     }
     $connection = Yii::app()->db;
     $criteria = new CDbCriteria();
     $models = $connection->createCommand($sql)->queryAll();
     $count = count($models);
     $pages = new CPagination($count);
     $pages->pageSize = 10;
     $pages->applylimit($criteria);
     $models = $connection->createCommand($sql . " LIMIT :offset,:limit");
     $models->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $models->bindValue(':limit', $pages->pageSize);
     $models = $models->queryAll();
     //獲取話題
     $topic_models = Topic::model()->findAll(array('select' => 'id,topic_title', 'order' => 'discuss_count desc', 'limit' => '25'));
     $this->pageTitle = "首頁";
     $this->render('index', array('models' => $models, 'pages' => $pages, 'topic_models' => $topic_models));
 }

 public function actionSharecontact()
 {
     $organID = Commonmodel::getOrganID();
     $dealer = Dealer::model()->find('userID=:userid', array(':userid' => $organID));
     $share = BusinessShare::model()->findAll('InitiatorID=:intorID and Status=2', array(':intorID' => $organID));
     foreach ($share as $key => $value) {
         $shareID .= ',' . $value['ShareID'];
     }
     $shareID = ltrim($shareID, ",");
     //查出shareID
     $sql = "select a.id,a.contact_user_id,a.customertype,\n\t\t   a.cooperationtype,a.name,a.sex,\n\t\t   a.companyname,a.phone,a.province,a.city,a.area,a.email,\n\t\t   a.weixin,a.QQ,a.create_time,a.Status,a.jiapart_ID,a.update_time,\n\t\t   (select b.category from tbl_customer_category b where a.customercategory=b.id and a.user_id={$organID}) as customercategory\n\t\t   from tbl_business_contacts a where \n\t\t   user_id='{$organID}' and Status=0  and a.contact_user_id in({$shareID})";
     if ($_POST) {
         $search['name'] = $_POST['name'];
         $search['phone'] = $_POST['phone'];
         $search['keyword'] = $_POST['keyword'];
         if ($search) {
             if ($search['name']) {
                 $sql .= " and name like'%{$search['name']}%'";
             }
             if ($search['phone']) {
                 $sql .= " and phone like '%{$search['phone']}%'";
             }
             if ($search['keyword']) {
                 $sql .= " and (companyname like '%{$search['keyword']}%') ";
             }
         }
     }
     $sql .= "order by create_time desc";
     $criteria = new CDbCriteria();
     $models = Yii::app()->db->createCommand($sql)->queryAll();
     $count = count($models);
     $pages = new CPagination($count);
     $pages->pageSize = intval($_GET['rows']);
     $pages->applylimit($criteria);
     $models = Yii::app()->db->createCommand($sql . " LIMIT :offset,:limit");
     $models->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $models->bindValue(':limit', $pages->pageSize);
     $models = $models->queryAll();
     foreach ($models as $key => $value) {
         $data[$key]['id'] = $value['id'];
         $data[$key]['Initiator'] = F::msubstr($dealer['organName']);
         $data[$key]['companyID'] = $value['contact_user_id'];
         //$data[$key]['customertype'] = $value['customertype'];
         $data[$key]['cooperationtype'] = $value['cooperationtype'];
         $data[$key]['customercategory'] = $value['customercategory'];
         $data[$key]['name'] = $value['name'];
         $data[$key]['sex'] = $value['sex'];
         $data[$key]['companyname2'] = F::msubstr($value['companyname']);
         $data[$key]['companyname'] = $value['companyname'];
         $data[$key]['phone'] = $value['phone'];
         $data[$key]['province'] = $value['province'];
         $data[$key]['city'] = $value['city'];
         $data[$key]['area'] = $value['area'];
         $data[$key]['address'] = F::msubstr(Area::getCity($value['province']) . Area::getCity($value['city']) . Area::getCity($value['area']));
         $data[$key]['email'] = $value['email'];
         $data[$key]['weixin'] = $value['weixin'];
         $data[$key]['QQ'] = $value['QQ'];
         $data[$key]['create_time'] = $value['create_time'];
         $data[$key]['Status'] = $value['Status'];
         $data[$key]['jiapart_ID'] = $value['jiapart_ID'];
         $data[$key]['update_time'] = $value['update_time'];
         //$data[$key]['checked']=true;
     }
     $rs = array('total' => $count, 'rows' => !empty($data) ? $data : array());
     echo json_encode($rs);
 }

 public static function getAll($params, &$pages, &$comLists)
 {
     $sql = $params['sql'];
     if (!$sql) {
         return false;
     }
     $pageSize = $params['pageSize'];
     $_size = isset($pageSize) ? $pageSize : 30;
     $com = Yii::app()->db->createCommand($sql)->query();
     //添加限製，最多取1000條記錄
     //todo，按不同情況分不同最大條數
     $total = $com->rowCount > 1000 ? 1000 : $com->rowCount;
     $pages = new CPagination($com->rowCount);
     $criteria = new CDbCriteria();
     $pages->pageSize = $_size;
     $pages->applylimit($criteria);
     $com = Yii::app()->db->createCommand($sql . " LIMIT :offset,:limit");
     $com->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $com->bindValue(':limit', $pages->pageSize);
     $comLists = $com->queryAll();
 }

 public function actionMessage($m)
 {
     $uid = Yii::app()->session['uid'];
     $data['uid'] = $uid;
     $sql = "select * from {{icontact}} where userid = {$uid} ";
     switch ($m) {
         case 2:
             break;
         case 1:
             $sql .= " and isread = 1 ";
             break;
         case 0:
             $sql .= " and isread = 0 ";
             break;
     }
     $order = " order by time desc ";
     $sql .= $order;
     $criteria = new CDbCriteria();
     $model = Yii::app()->db->createCommand($sql)->queryAll();
     $pages = new CPagination(count($model));
     $pages->pageSize = 7;
     $pages->applylimit($criteria);
     $model = Yii::app()->db->createCommand($sql . " LIMIT :offset,:limit");
     $model->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $model->bindValue(':limit', $pages->pageSize);
     $commentInfo = $model->queryAll();
     $data['pages'] = $pages;
     $data['messageInfo'] = $commentInfo;
     $this->render("imessage", $data);
 }

 public function actionArticles()
 {
     $uid = Yii::app()->session['uid'];
     $sql = "select b.*,l.label from {{blog}} b,{{label}} l where b.userid = {$uid} and b.labelid = l.id order by b.time desc ";
     $criteria = new CDbCriteria();
     $model = Yii::app()->db->createCommand($sql)->queryAll();
     $pages = new CPagination(count($model));
     $pages->pageSize = 7;
     $pages->applylimit($criteria);
     $model = Yii::app()->db->createCommand($sql . " LIMIT :offset,:limit");
     $model->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $model->bindValue(':limit', $pages->pageSize);
     $blogs = $model->queryAll();
     $data['pages'] = $pages;
     $data['blogs'] = $blogs;
     $this->render('articles', $data);
     //			$criteria = new CDbCriteria();		//AR的另一種寫法
     //			$criteria->condition = "userid=".Yii::app()->session['uid'];
     //			$blogs = new Blog();
     //			$total = $blogs->count($criteria);	//統計條數
     //
     //			$pager = new CPagination($total);
     //			$pager->pageSize = 7;
     //			$pager->applyLimit($criteria);
     //			$blogs = $blogs->findAll($criteria);
     //			$data = array('pages'=>$pager,'blogs'=>$blogs);
     //			$this->render('articles',$data);
 }

 public function actionArticles()
 {
     $uid = Yii::app()->session['uid'];
     $sql = "select b.*,l.label from {{blog}} b,{{label}} l where b.userid = {$uid} and b.labelid = l.id order by b.time desc ";
     $criteria = new CDbCriteria();
     $model = Yii::app()->db->createCommand($sql)->queryAll();
     $pages = new CPagination(count($model));
     $pages->pageSize = 7;
     $pages->applylimit($criteria);
     $model = Yii::app()->db->createCommand($sql . " LIMIT :offset,:limit");
     $model->bindValue(':offset', $pages->currentPage * $pages->pageSize);
     $model->bindValue(':limit', $pages->pageSize);
     $blogs = $model->queryAll();
     $data['pages'] = $pages;
     $data['blogs'] = $blogs;
     $this->render('articles', $data);
 }