MSSQL+CI分页类Pagination实现两个及两个以上关联表的分页效果

judy_zyzyx 发表于 2008-5-8 11:16:03

因为MSSQL不提供带偏移量的查询语句,所以本人很头疼,后来在群友的支持下终于完成并实现了,现在将控制器部分的代码粘贴如下,希望象我一样的菜鸟能有所收获.

function advstation()
{

//初始参数设置
$now_table='advstation';
$base_url='/index.php/webadvmag/advstation/';
$per_page=10;
$segment_value=3;

   //调用类库
$this->load->helper(array('form', 'url'));
//调用分页类
$this->load->library('pagination');
$config['base_url'] = $base_url;
$sum=$config['total_rows'] = $this->db->count_all('advstation');
$config['per_page'] = $per_page;
$config['uri_segment']=$segment_value;

$this->pagination->initialize($config);

   //组织mssql查询语句开始
if(($this->uri->segment(3))>0){
$offset=$this->uri->segment($segment_value);
}else{
$offset=0;
}
//最后一页条数设置
if(($sum-$offset)<$per_page){
$per_page=$sum-$offset;
}
$sql = "SELECT * FROM (select top ".$per_page." * from (select top ".($per_page+$offset)." advstationid,advstation,advsize,channel from advstation a,channels b where a.channelid=b.channelid order by advstationid DESC) AS A order by advstationid ASC) AS B ORDER BY advstationid DESC";

      //查询语句组织完成
$query=$this->db->query($sql);

//取得查询结果及分页信息
$data['pages']=$this->pagination->create_links();
$data[$now_table.'_fields'] = $query->result_array();
/*分页显示结束*/

//调用视图
$this->load->view('adminweblogin/adv_station_list',$data);
}

Hex 发表于 2008-5-8 11:28:36

感谢分享你的经验！加分！

kkito 发表于 2008-5-8 13:24:00

哦
我之前也折腾了这个东东
mssql好像有
http://codeigniter.org.cn/forums/thread-72-1-2.html
站长贴的不知道行不行
我用access也碰到类似问题

说说那个sql吧，我的做法是
select top $limit * from (select top $offset * from table order by ordercolum desc) order by ordercolum
然后把结果array_reverse一下
不知道那个效率高些

这样做还有bug，就是分页到了最后一页，比如每页5个，总共12个，第三页应该两个啊，但这么查最后一页也是5个，让人不爽。

我的程序在家，晚上贴上来

judy_zyzyx 发表于 2008-5-8 16:52:58

最后一页可以用程序控制一下

//最后一页条数设置
   if(($sum-$offset)<$per_page){
            $per_page=$sum-$offset;
   }

kkito 发表于 2008-5-8 19:48:33

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class MY_Model extends Model{
function MY_Model(){
//parent::Controller();
parent::Model();
}

function getPagination(){

}

function getPagiItems($limit , $offset , $conditions = array() , $order = ""){
$offset = $offset + $limit;
//$this->db->order_by("recordtime", "desc");
//$query = $this->db->get("article");
$query = $this->db->query("select top $limit * from (select top $offset * from $this->tableName ".$this->generateConditions($conditions)." ".$this->getOrderString($order)." ) ".$this->getOppsiteOrder($order));

//数组是顺序的颠倒的，需要换一下。
return array_reverse($query->result());
}

function getPagiCount($conditions){
$query = $this->db->query("select count(*) as numrows from $this->tableName ".$this->generateConditions($conditions));
if ($query->num_rows() == 0)
{
return '0';
}

$row = $query->row();
return $row->numrows;
}

function pagiOrderFlag($order){
if(strpos($order , "desc") === false){
return true;
}else{
return false;
}
}

function getOrderWithnoDirection($order){
//TODO maybe use regex should be better
if($this->pagiOrderFlag($order)){
return str_replace(" asc" , "" ,$order);
}else{
return str_replace(" desc" , "" ,$order);
}
}

function getOppsiteOrder($order){
if($order == ""){
return "";
}
$flag = $this->pagiOrderFlag($order);
$order = $this->getOrderWithnoDirection($order);
if($flag){
$order = $order." desc";
}
return "order by ".$order;
}

function getOrderString($order){
if($order == ""){
return "";
}
return "order by ".$order;
}

function generateConditions($conditions){
$ret = "";
$connect = " and ";
foreach(array_keys($conditions) as $key){
if(is_array($conditions[$key])){
if(sizeof($conditions[$key]) == 3){
$connect = " ".$conditions[$key]." ";
}
$operate = " ".$conditions[$key]." ";
$conditions[$key] = $conditions[$key];
}else{
$operate = " = ";
}
if($this->is_date($conditions[$key])){
$value = $conditions[$key];
}elseif(is_string($conditions[$key])){
$value = "'".str_replace("'" , "''" , $conditions[$key])."'";
}else{
$value = $conditions[$key];
}
$cond = $key.$operate.$value;
if($ret == ""){
$ret = $cond;
}else{
$ret = $ret.$connect.$cond;
$connect = " and ";
}
}
if($ret == ""){
return $ret;
}else{
return "where ".$ret;
}
}

function is_date($date){
//echo "$date\n";
//return ereg("\d" , $date);
//$subject = "#1233-23-23#";
$pattern = '/^#\d{4}-\d{2}-\d{2}#$/';
return preg_match($pattern , $date);
//echo "1";

}

}
?>

kkito 发表于 2008-5-9 16:19:00

原帖由 judy_zyzyx 于 2008-5-8 16:52 发表 http://codeigniter.org.cn/forums/images/common/back.gif
//最后一页条数设置
if(($sum-$offset)
对啊~
有道理
呵呵
你这么一说我到是觉得可以放在sql里面一起处理掉的
恩，不错不错

xieyuejie 发表于 2012-9-26 17:29:01

难以言谢

页: [1]

CodeIgniter 中国开发者社区's Archiver

MSSQL+CI分页类Pagination实现两个及两个以上关联表的分页效果

最后一页可以用程序控制一下