来自数据库限制的Codeigniter分页，偏移量 [英] Codeigniter Pagination From Database limit, offset

问题描述

我在CI 3.0中启动了一个Web应用程序，一切运行正常，我的分页工作正常，但是存在一个我无法弄清楚的问题...

I started a web application in CI 3.0, everything working smooth, I got my pagination working, but there is a problem wich I cannot figure out...

例如，我有以下URL: localhost/statistics/api_based 导航时，显示$ query结果，生成链接，确定. 但是，例如，当我导航到第2页时，URL将变为: localhost/statistics/dll_based/index/2 和第3页将成为 localhost/statistics/api_based/index/3 ，因此我正在使用段. 现在的问题是生成的查询:

For example, I have the following URL: localhost/statistics/api_based When navigate, $query results are displayed, links are generated, OK. But, when I navigate to page 2 for example, the URL will become: localhost/statistics/dll_based/index/2 and page 3 will become localhost/statistics/api_based/index/3 , so therefore I am using segments. Now the problem is the query that is generated:

SELECT * FROM `shield_api` ORDER BY `id` ASC limit 20 offset 2

偏移量2 -是页码2，如果我正确的话，它应该是20. 我每页显示20个结果，所以第一页正确显示了1-20的结果，但是第二页将显示2-21的结果，所以我明白了，这不行...

offset 2 - is the page number 2, and it should be 20, if I am correct. I am displaying 20 results per page, so first page is correctly showing results from 1 - 20, but then page 2 will display results from 2 - 21, so you get my point, it`s not OK...

这是我的控制人:

function index($offset = 0) {
        // Enable SSL?
        maintain_ssl ( $this->config->item ( "ssl_enabled" ) );

        // Redirect unauthenticated users to signin page
        if (! $this->authentication->is_signed_in ()) {
            redirect ( 'account/sign_in/?continue=' . urlencode ( base_url () . 'statistics/api_based' ) );
        }

        if ($this->authentication->is_signed_in ()) {
            $data ['account'] = $this->account_model->get_by_id ( $this->session->userdata ( 'account_id' ) );
        }

        $per_page = 20;
        $qry = "SELECT * FROM `shield_api` ORDER BY `id` ASC";

        //$offset = ($this->uri->segment ( 4 ) != '' ? $this->uri->segment ( 4 ) : 0);

        $config ['total_rows'] = $this->db->query ( $qry )->num_rows ();
        $config ['per_page'] = $per_page;
        $config ['uri_segment'] = 4;
        $config ['base_url'] = base_url () . '/statistics/api_based/index';
        $config ['use_page_numbers'] = TRUE;
        $config ['page_query_string'] = FALSE;
        $config ['full_tag_open'] = '<ul class="pagination">';
        $config ['full_tag_close'] = '</ul>';
        $config ['prev_link'] = '&laquo;';
        $config ['prev_tag_open'] = '<li>';
        $config ['prev_tag_close'] = '</li>';
        $config ['next_link'] = '&raquo;';
        $config ['next_tag_open'] = '<li>';
        $config ['next_tag_close'] = '</li>';
        $config ['cur_tag_open'] = '<li class="active"><a href="#">';
        $config ['cur_tag_close'] = '</a></li>';
        $config ['num_tag_open'] = '<li>';
        $config ['num_tag_close'] = '</li>';
        $config ["num_links"] = round ( $config ["total_rows"] / $config ["per_page"] );

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

        $data ['pagination_links'] = $this->pagination->create_links ();

        //$data ['per_page'] = $this->uri->segment ( 4 );

        $data ['offset'] = $offset;

        $qry .= " limit {$per_page} offset {$offset} ";

        if ($data ['pagination_links'] != '') {
            $data ['pagermessage'] = 'Showing ' . ((($this->pagination->cur_page - 1) * $this->pagination->per_page) + 1) . ' to ' . ($this->pagination->cur_page * $this->pagination->per_page) . ' results, of ' . $this->pagination->total_rows;
        }

        $data ['result'] = $this->db->query ( $qry )->result_array ();

        $this->load->view ( 'statistics/api_based', isset ( $data ) ? $data : NULL );
    }

有人可以指出我的问题吗?任何帮助都非常感谢.

Can someone point me to my problem ? Any help is apreciated.

推荐答案

您可能对LIMIT的工作方式有误解.

You may have a misunderstanding of how LIMIT works.

限制为一个值可设置要返回的最大数量.

Limit with one value sets the maximum number to be returned.

LIMIT 10

将从与您的查询匹配的结果的开头开始最多检索10行.

Will retrieve at most 10 rows, starting at the beginning of the results matching your query.

具有两个值的限制设置开始位置(偏移量，以行为单位，而不是页面)和要返回的最大行数.

Limit with two values sets the start position (offset in rows, not pages) and the maximum number of rows to be returned.

LIMIT 20, 10

从第20行开始最多检索10行.

Will retrieve at most 10 rows, starting at row 20.

因此，您需要在这里稍微修改一下逻辑:

So, you'll need to modify your logic a bit here:

$start = max(0, ( $offset -1 ) * $per_page);
$qry .= ' LIMIT {$start}, {$per_page}';

这篇关于来自数据库限制的Codeigniter分页，偏移量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！