我在CI 3.0中启动了一个web应用程序,一切都很顺利,我的分页工作正常,但有一个问题我无法解决。。。
例如,我有以下URL:localhost/statistics/api_based导航时,将显示$query结果,并生成链接,确定。但是,例如,当我导航到第2页时,URL将变为:localhost/sttatistics/dll_based/index/2和第3页将变为host/sttatistics/api_based/index.3,因此我使用段。现在的问题是生成的查询:
SELECT * FROM `shield_api` ORDER BY `id` ASC limit 20 offset 2
偏移量2-是页码2,如果我是正确的,它应该是20。我每页显示20个结果,所以第一页正确地显示了1-20的结果,但第二页将显示2-21的结果,所以你明白我的意思,这不好…
这是我的控制器:
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'] = '«';
$config ['prev_tag_open'] = '<li>';
$config ['prev_tag_close'] = '</li>';
$config ['next_link'] = '»';
$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 );
}
有人能给我指一下我的问题吗?任何帮助都是值得的。
您可能对LIMIT
的工作原理有误解。
带有一个值的限制设置要返回的最大数字。
LIMIT 10
将检索最多10行,从与查询匹配的结果的开头开始。
有两个值的Limit设置起始位置(以行而非页面为单位的偏移量)和要返回的最大行数。
LIMIT 20, 10
将从第20行开始检索最多10行。
所以,你需要在这里修改一下你的逻辑:
$start = max(0, ( $offset -1 ) * $per_page);
$qry .= ' LIMIT {$start}, {$per_page}';