分页与排序在PHP中的实现与应用-优快云博客

本文链接：https://blog.youkuaiyun.com/hudeyong926/article/details/99540456

'defaultOrder'=>array('title' => false), false是升序，true是倒叙

1.自定义分页

使用CPagination呈现相关信息到页码. 当数据需要渲染到多页时,我们可能使用去呈现信息，诸如itemCount, pageSize, currentPage,等等. 这些信息被传递到pagers

/**
 * 分页查询
 * @param $modelClass
 * @param int $page
 * @param string $where
 * @return mixed
 */
public function getPagerList($modelClass, $page = 1, $where = '')
{
    $pageSize = Yii::app()->params['pageSize'];
    $offset = ($page - 1) * $pageSize;

    $sql = $this->_listSql($modelClass, $where, '*');
    $command = Yii::app()->db->createCommand($sql . " LIMIT :offset,:limit");
    $command->bindValue(':offset', $offset);
    $command->bindValue(':limit', $pageSize);
    $result = $command->queryAll();
    if (empty($result)) {
        $result = [];
    }
    return $result;
}

/**
 * 获取sql
 * @param $modelClass
 * @param $where
 * @param string $field
 * @return string
 */
private function _listSql($modelClass, $where, $field = 'COUNT(*)')
{
    //下面多表联合查询
    if (stripos($modelClass, 'SELECT') !== false && stripos($modelClass, 'JOIN') !== false) {
        $sql = $modelClass;
    } else {
        $sql = "SELECT $field FROM " . CActiveRecord::model($modelClass)->tableSchema->name;
    }
    if ($where) $sql .= " WHERE {$where}";
    return $sql;
}

function extractSelectField($query) {
    // 使用正则表达式匹配 SELECT 和 FROM 之间的部分
    $pattern = '/(?i)select\s+(.*?)\s+from/';
    preg_match($pattern, $query, $matches);

    // 检查是否有匹配项
    if (isset($matches[1])) {
        return trim($matches[1]);  // 返回匹配的部分，并去除多余的空格
    } else {
        return null;  // 如果没有匹配到，返回 null
    }
}

/**
 * 获取总数
 * @param $modelClass
 * @param string $where
 * @return mixed
 */
public function getCount($modelClass, $where = "")
{
    $sql = $this->_listSql($modelClass, $where);
    $field = $this->extractSelectField($sql);
    $sql = str_replace($field, 'count(1)', $sql);
    $count = Yii::app()->db->createCommand($sql)->queryScalar();
    if (empty($count)) {
        $count = 0;
    }
    return $count;
}

不使用分页，取所有记录

public function searchExport()
{
    $model = new Orders('search');
    if (isset($_GET['Orders'])) {
        $model->attributes = $_GET['Orders'];
    }
    $dataProvider = $model->search();
    $dataProvider->setPagination(false); //禁用分页
    return $dataProvider->getData();
}

PostController.php

function actionIndex()
{
    $criteria = new CDbCriteria();
    $criteria->compare("log_id", ">0");
    $count = Ipad3SerialNumber::model()->count($criteria);

    $pages = new CPagination($count);
    // elements per page  
    $pages->pageSize = 2;
    $pages->applyLimit($criteria);

    // sorting  
    $sort = new CSort('Ipad3SerialNumber');
    $sort->attributes = array(
        'title',
    );
    $sort->defaultOrder = array('get_time' => true);
    $sort->applyOrder($criteria);

    $models = Ipad3SerialNumber::model()->findAll($criteria);
    $this->render('list', array(
        'models' => $models,
        'pages' => $pages,
        'sort' => $sort,
    ));
}

view

<?php
<p><?php echo $sort->link('id')?></p>
<p><?php echo $sort->link('title')?></p>
<ol>
<?php foreach($models as $model):?>
<li>
<h2><?php echo $model->id?> - <?php echo $model->title?></h2>
</li>
<?php endforeach?>
</ol>
<?php $this->widget('CLinkPager', array(
	'pages' => $pages,
	'header'=>''
));
$this->widget('CListPager', array(  
	'pages'=>$pages, 
	'header'=>'-'
));
?>

点击$sort的link可以动态排序

2系统也有封装的分页，排序GRIDView 覆盖public $pager = 'CardPager'属性和renderPager()方法

enableSorting=false全部禁用排序，单列禁用排序sortable =false,enablePagination=false

public function actionAR(){
	/****用法一：*****/
	$criteria = new CDbCriteria();  
	$criteria->compare('serviceType',$arr);
	$dataProvider=new CActiveDataProvider('Post',array(
		'criteria' => $criteria,
	));

	/****用法二：*****/
	$dataProvider=new CActiveDataProvider('Post', array(
		/*'criteria'=>array(
			'condition'=>'status=1 AND tags like :tags',
			'params'=>array(':tags'=>$_GET['tags']),
			'order'=>'create_time DESC',
			'with'=>array('author'),
		),*/
		'pagination'=>array(
			'pageSize'=>100,
		),
		'sort'=>array(
			'defaultOrder'=> array('title'=>true),
		),
	));
	$this->render('index', array(
		'dataProvider' => $dataProvider,
	));
}

public function actionArray(){ //推荐不用，数组大内存溢出
	$rawData= array(array(
		'name'=>'István Beregszászi',
		'duty'=>'core framework development',
		'active'=>false,
	));
    //$rawData=Yii::app()->db->createCommand($sql)->queryAll();
	$dataProvider = new CArrayDataProvider($rawData, array(
		'sort'=>array(
			'attributes'=>array('name', 'id', 'active'),
			'defaultOrder'=>array('active' => true, 'name' => false),
		),
		'pagination'=>array(
			'pageSize'=>10,
		),
	));
	$this->render('index', array(
		'dataProvider' => $dataProvider,
	));
}

public function actionSQL(){
	$count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM film')->queryScalar();
	$sql = 'SELECT film_id,title FROM film';
	$dataProvider=new CSqlDataProvider($sql,array(
		'keyField'=>'film_id',  //sql in select table column default id
		'totalItemCount'=>$count,
		'sort'=>array(
			'attributes'=>array('title'), //sql table columns
			'defaultOrder'=>array('title' => true),
		),
		'pagination'=>array(
			'pageSize'=>100,
		),
	));
	$this->render('index', array(
		'dataProvider' => $dataProvider,
	));
}

view

<?php foreach($dataProvider->data as $film):?>
<?php echo $film->title?>
<?php endforeach?>
<?php $this->widget('CLinkPager',array('pages'=>$dataProvider->pagination))?>

也可以套CGridView(推荐) summaryText，{summary}自定义总计信息

<?php
$this->widget('zii.widgets.grid.CGridView', array(
	'dataProvider'=>$dataProvider,
	'template'=>'{items}{summary}{pager}',
	'summaryText'=>"第{start}-{end}条, 共 {count} 条,页数：{page}/{pages}页",
	'columns'=>array(
		array(  
			'name' => 'title',   
			'header' => '订单号', 
			'value'=>'$data[title]',  //if is_array
		)  
	),
));?>

<?php $this->widget('application.components.widgets.grid.GridView', array(
    'dataProvider'=>$dataProvider,
    'template' => "{pager}",
)); ?>

3分页扩展

mypager是一个非常简单的扩展，结合在一个 CListPager 和 CLinkPager ，允许用户选择他们想要去或者点击一个页面按钮 CLinkPager ），或选择从下拉列表中的页面（ CListPager ）页 CGridView 。
CLinkPager首页，末页CSS默认不显示

/**
 * Hide first and last buttons by default.
 */
ul.yiiPager .first,
ul.yiiPager .last
{
	display:none;
}

<?php $this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'customer-grid',
	'dataProvider'=>$model->search(),
	'pager'=>array(
		//'class'=>'CLinkPager', //LinkListPager
		'maxButtonCount'=>8,
		'firstPageLabel'=>'首页',
		'lastPageLabel'=>'末页',
		'nextPageLabel'=>'下一页',
		'prevPageLabel'=>'上一页',
		'header'=>'',
		'cssFile'=>false,    //如果使用主css文件，那么这里可以写false,如果使用其它附加css文件，我们这里填写css文件名。
	), 
));?>

视图的代码

<ul class="pagination">
    <?php
    $firstPage = '<li class="hidden-xs">'.CHtml::link('1', array('', 'page'=>1)).'</li>';
    $lastPage = '<li>'.CHtml::link($pageCount, array('', 'page'=>$pageCount)).'</li>';
    $prevPage = '<li>'.CHtml::link('上一页', array('', 'page'=>$currentPage-1)).'</li>';
    $nextPage = '<li>'.CHtml::link('下一页', array('', 'page'=>$currentPage+1)).'</li>';
    $morePage = '<li class="disabled hidden-xs"><span>...</span></li>';

    if($currentPage > 1)
        echo $prevPage;

    if($currentPage > 3)
        echo $firstPage.$morePage;

    for($i = $currentPage-2; $i <= $currentPage+2; $i++) {

        $liStr = '<li class="hidden-xs">'.CHtml::link($i, array('', 'page'=>$i)).'</li>';
        if($i<1 || $i>$pageCount)
            $liStr = '';

        if($currentPage == $i)
            $liStr = '<li class="active">'.CHtml::link($i.' <span class="sr-only">(current)</span>',
                    array('', 'page'=>$i)).'</li>';

        echo $liStr;
    }

    if($currentPage < $pageCount-2)
        echo $morePage;

    if($currentPage < $pageCount)
        echo $nextPage;
    ?>
</ul>