首页 > 文章 > 后端开发 > 分页原理是什么

分页原理是什么

不言原创: 2018-05-31 11:33:182826浏览

数据分页需要以下几个条件：

1. 参与分页的总条数【$msg_count】，该值通过数据库查询可以获取到;

2. 每页显示的条数【$pagesize】，这个数值由自己定义;

3. 当前页的页码数【$page】，该数值通过地址栏传递和接收；

4. 可以通过以上资料计算出总页数【$pagecount】，此处需要借助ceil();

【$pagecount = ceil($msg_count/$pagesize);】

5. 数据库查询借助sql语句中的【limit】来实现数据的变化：

例如：

select * from 表名 where 条件 limit $startnum , $pagesize;

而$startnum = ($page-1)*$pagesize;

实例：

/**
 * 取得上次的过滤条件
 * @param   string  $param_str  参数字符串，由list函数的参数组成
 * @return  如果有，返回array(&#39;filter&#39; => $filter, &#39;sql&#39; => $sql)；否则返回false
 */
function get_filter($param_str = &#39;&#39;)
{
    $filterfile = basename(PHP_SELF, &#39;.php&#39;);//string basename ( string $path [, string $suffix ] )　　　　　　　　　　　　　　　　　　　　　　　　　　　　　返回路径中的文件名部分如果文件名是以 suffix 结束的，那这一部分也会被去掉。 
    if ($param_str)
    {
        $filterfile .= $param_str;
    }
    if (isset($_GET[&#39;uselastfilter&#39;]) && isset($_COOKIE[&#39;ECSCP&#39;][&#39;lastfilterfile&#39;])
        && $_COOKIE[&#39;ECSCP&#39;][&#39;lastfilterfile&#39;] == sprintf(&#39;%X&#39;, crc32($filterfile)))
    {
        return array(
            &#39;filter&#39; => unserialize(urldecode($_COOKIE[&#39;ECSCP&#39;][&#39;lastfilter&#39;])),
            &#39;sql&#39;    => base64_decode($_COOKIE[&#39;ECSCP&#39;][&#39;lastfiltersql&#39;])
        );
    }
    else
    {
        return false;
    }
}

/**
 * 保存过滤条件
 * @param   array   $filter     过滤条件
 * @param   string  $sql        查询语句
 * @param   string  $param_str  参数字符串，由list函数的参数组成
 */
function set_filter($filter, $sql, $param_str = &#39;&#39;)
{
    $filterfile = basename(PHP_SELF, &#39;.php&#39;);
    if ($param_str)
    {
        $filterfile .= $param_str;
    }
    setcookie(&#39;ECSCP[lastfilterfile]&#39;, sprintf(&#39;%X&#39;, crc32($filterfile)), time() + 600);
    setcookie(&#39;ECSCP[lastfilter]&#39;,     urlencode(serialize($filter)), time() + 600);
    setcookie(&#39;ECSCP[lastfiltersql]&#39;,  base64_encode($sql), time() + 600);
}

/**
 * 供货商资源管理
 * @param    bool    $is_pagtion
 * @return    array   $arr
 */
function suppliers_resource_manage($is_pagtion=true)
{
    global $db,$ecs;
    $result = get_filter();
    if ($result === false)
    {
        $aiax = isset($_GET[&#39;is_ajax&#39;]) ? $_GET[&#39;is_ajax&#39;] : 0;
        /* 过滤信息 */
        $filter[&#39;sort_by&#39;] = empty($_REQUEST[&#39;sort_by&#39;]) ? &#39;r.resource_id&#39; : trim($_REQUEST[&#39;sort_by&#39;]);
        $filter[&#39;sort_order&#39;] = empty($_REQUEST[&#39;sort_order&#39;]) ? &#39;DESC&#39; : trim($_REQUEST[&#39;sort_order&#39;]);
        $filter[&#39;resource_id&#39;] = empty($_REQUEST[&#39;resource_id&#39;])  ? &#39;&#39; : $_REQUEST[&#39;resource_id&#39;];
        $filter[&#39;admin_name&#39;] = empty($_REQUEST[&#39;admin_name&#39;])  ? &#39;&#39; : trim($_REQUEST[&#39;admin_name&#39;]);
        $filter[&#39;resource_name&#39;] = empty($_REQUEST[&#39;resource_name&#39;])  ? &#39;&#39; : trim($_REQUEST[&#39;resource_name&#39;]);
        $filter[&#39;admin_id&#39;]  = isset($_REQUEST[&#39;admin_id&#39;])?intval($_REQUEST[&#39;admin_id&#39;]):&#39;-1&#39;;
        $filter[&#39;resource_type&#39;] = empty($_REQUEST[&#39;resource_type&#39;]) ? &#39;-1&#39; : intval($_REQUEST[&#39;resource_type&#39;]);
        $filter[&#39;resource_status&#39;] = empty($_REQUEST[&#39;resource_status&#39;]) ? &#39;-1&#39; : intval($_REQUEST[&#39;resource_status&#39;]);
        $filter[&#39;resource_rank&#39;] = empty($_REQUEST[&#39;resource_rank&#39;]) ? &#39;-1&#39; : intval($_REQUEST[&#39;resource_rank&#39;]);
        $filter[&#39;resource_key&#39;] = empty($_REQUEST[&#39;resource_key&#39;])  ? &#39;&#39; : trim($_REQUEST[&#39;resource_key&#39;]);
        $filter[&#39;menuid&#39;] = 7; 
        $where = &#39;WHERE 1 &#39;;
        /* 分页大小 */
        $filter[&#39;page&#39;] = empty($_REQUEST[&#39;page&#39;]) || (intval($_REQUEST[&#39;page&#39;]) <= 0) ? 1 : intval($_REQUEST[&#39;page&#39;]);

        if (isset($_REQUEST[&#39;page_size&#39;]) && intval($_REQUEST[&#39;page_size&#39;]) > 0)
        {
            $filter[&#39;page_size&#39;] = intval($_REQUEST[&#39;page_size&#39;]);
        }
        elseif (isset($_COOKIE[&#39;ECSCP&#39;][&#39;page_size&#39;]) && intval($_COOKIE[&#39;ECSCP&#39;][&#39;page_size&#39;]) > 0)
        {
            $filter[&#39;page_size&#39;] = intval($_COOKIE[&#39;ECSCP&#39;][&#39;page_size&#39;]);
        }
        else

        {
            $filter[&#39;page_size&#39;] = 15;
        }
        
        if(!empty($filter[&#39;resource_id&#39;]))
        {
            $resource_id = $filter[&#39;resource_id&#39;] == -1 ? 0 : $filter[&#39;resource_id&#39;];
            $where .= " and r.resource_id=&#39;".$resource_id."&#39;";
        }
        if($filter[&#39;resource_name&#39;]){
            $where .= " AND r.resource_name like &#39;%".mysql_like_quote($filter[&#39;resource_name&#39;])."%&#39; ";
        }
        if($filter[&#39;admin_id&#39;] > -1)
        {
            $where .= " AND r.admin_id = &#39;".$filter[&#39;admin_id&#39;]."&#39; ";
        }
        if ($filter[&#39;resource_type&#39;] > -1) 
        {
            $where .= " AND r.resource_type = &#39;" . $filter[&#39;resource_type&#39;] . "&#39;";
        }
        if ($filter[&#39;resource_status&#39;] > -1) 
        {
            $where .= " AND r.resource_status = &#39;" . $filter[&#39;resource_status&#39;] . "&#39;";
        }
        if ($filter[&#39;resource_rank&#39;] > -1) 
        {
            $where .= " AND r.resource_rank = &#39;" . $filter[&#39;resource_rank&#39;] . "&#39;";
        }
        if($filter[&#39;resource_key&#39;]){
            $where .= " AND r.remark like &#39;%".mysql_like_quote($filter[&#39;resource_key&#39;])."%&#39; ";
        }
        /* 记录总数 */
        $sql = "SELECT COUNT(r.resource_id) FROM " . $ecs->table(&#39;suppliers_resource&#39;)." as r 
                    LEFT JOIN ".$ecs->table(&#39;admin_user&#39;) . " as u
                        ON u.user_id = r.admin_id  " . $where;
        $filter[&#39;record_count&#39;]   = $db->getOne($sql);
        $filter[&#39;page_count&#39;]     = $filter[&#39;record_count&#39;] > 0 ? ceil($filter[&#39;record_count&#39;] / $filter[&#39;page_size&#39;]) : 1;
        /* 查询 */
        $sql = "SELECT r.resource_id, u.user_name as admin_name, r.resource_name, r.resource_link, r.resource_type, r.resource_status, r.resource_rank, r.remark, r.add_time FROM " . $ecs->table(&#39;suppliers_resource&#39;) . " as r 
                    LEFT JOIN " . $ecs->table(&#39;admin_user&#39;) . " as u ON u.user_id = r.admin_id  " . $where;

        $sort_by  = $filter[&#39;sort_by&#39;];
        $sort_order = $filter[&#39;sort_order&#39;];
        $sql .="GROUP BY r.resource_id ORDER BY " . $sort_by . " " . $sort_order;
        if($is_pagtion)
        {
            $sql .=" LIMIT ".($filter[&#39;page&#39;] - 1)*$filter[&#39;page_size&#39;].",".$filter[&#39;page_size&#39;];
        }
        set_filter($filter, $sql);
    }
    else
    {
        $sql    = $result[&#39;sql&#39;];
        $filter = $result[&#39;filter&#39;];
    }
    $query=$sql;

    $row = $db->getAll($sql);
    /* 格式话数据 */
    foreach ($row AS $key => $value)
    {   
        if ($row[$key][&#39;resource_type&#39;] == 1) {
            $row[$key][&#39;resource_type&#39;] = &#39;中模&#39;;
        }elseif ($row[$key][&#39;resource_type&#39;] == 2) {
            $row[$key][&#39;resource_type&#39;] = &#39;泳装&#39;;
        }elseif ($row[$key][&#39;resource_type&#39;] == 3) {
            $row[$key][&#39;resource_type&#39;] = &#39;阿里&#39;;
        }elseif ($row[$key][&#39;resource_type&#39;] == 2) {
            $row[$key][&#39;resource_type&#39;] = &#39;17网&#39;;
        }
        if ($row[$key][&#39;resource_status&#39;] == 1) {
            $row[$key][&#39;resource_status&#39;] = &#39;已审核&#39;;
        }elseif ($row[$key][&#39;resource_status&#39;] == 2) {
            $row[$key][&#39;resource_status&#39;] = &#39;已弃用&#39;;
        }
        if ($row[$key][&#39;resource_rank&#39;] == 1) 
        {
            $row[$key][&#39;resource_rank&#39;] = &#39;A&#39;;
        }elseif ($row[$key][&#39;resource_rank&#39;] == 2) {
            $row[$key][&#39;resource_rank&#39;] = &#39;B&#39;;
        }elseif ($row[$key][&#39;resource_rank&#39;] == 3) {
            $row[$key][&#39;resource_rank&#39;] = &#39;C&#39;;
        }
        if(strpos($row[$key][&#39;resource_link&#39;], &#39;http://&#39;) === false)
        {
            if (strpos($row[$key][&#39;resource_link&#39;], &#39;https://&#39;) === false) 
            {
                $row[$key][&#39;resource_link&#39;] = substr_replace($row[$key][&#39;resource_link&#39;], &#39;http://&#39;, 0, 0);
            }
        }
    }
    $arr = array(&#39;result&#39; => $row, &#39;filter&#39; => $filter, &#39;page_count&#39; => $filter[&#39;page_count&#39;], &#39;record_count&#39; => $filter[&#39;record_count&#39;],&#39;query&#39;=>$query);
    return $arr;
}

分页原理是什么

相关文章