如何在带有$ this-> db-> query（）的codeigniter中使用分页？ [英] How to use pagination in codeigniter with $this->db->query()?

问题描述

我正在开发 codeigniter 上的项目，在创建记录的分页时遇到了困难。

I am developing a project on codeigniter, I am facing difficulty while creating pagination of records.

实际上，当我使用 $ this-> db-> get（）时，它的分页工作正常。但是，当使用 $ this-> db-> query（）进行分页时，分页不起作用，下面是我的 controller中的代码。

Actually, when I user $this->db->get() then its okay, pagination work fine. But when do pagination using $this->db->query() then pagination is nor working, following is the code in my controller.

        $this->load->library('pagination');

    $pagination_config['base_url'] = base_url().'welcome/index';
    $pagination_config['per_page'] = 3;
    $pagination_config['num_links'] = 3;
    $pagination_config['total_rows'] = $this->db->get('properties')->num_rows();

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

    $q = $this->db->query("SELECT title FROM properties", $pagination_config['per_page']);

    $r = $q->result();

    foreach($r as $p){
        echo $p->title.'<br />';
    }

    echo '<br />';
    echo '<br />';
    echo $this->pagination->create_links();

我也尝试在查询中使用 LIMIT ，如下所示，

I also tried LIMIT in query like following,

$q = $this->db->query("SELECT title FROM properties LIMIT 3");

它会显示您的记录，但在所有页面上都一样。

and it is showing thee records but on all pages as same.

推荐答案

CodeIgniter分页入门

无论分页是什么，通常都希望使用URI段指定页面，除非您希望URI段指定偏移量。在我的示例中，我假设需要页面。

Getting Started with CodeIgniter Pagination

Regardless of what you are paginating, you will normally want a URI segment designating a page, unless you want your URI segment to designate an offset. In my example, I'll assume the desire for pages.

因此，假设我们要对一些foo进行分页，并且我们的数据库包含一个名为foos的表。假装我们有很多foos（65）。在我们的Foos控制器中，我们有一些方法：

So, lets say we want to paginate some foos, and our database contains a table called foos. Pretend we have plenty of foos (65). In our Foos controller, we have some methods:

public function index()
{
    $this->page();
}

public function page( $page = 1 )
{
    // Here we get and show our foos
}

当我们想获取当前页面的foos的结果时，我们有一个页码参数，这就是我们所知道的要获取的一组foo，但是首先我们还需要foo的总数。因此，在我们的page方法中，我们可以在模型上调用一个方法来完成所有工作：

When we want to get results for the current page of foos, we have a page number parameter, and that's how we know what set of foos to get, but first we also need a total count of our foos. So inside our page method, we can call a method on the model to do all the work:

public function page( $page = 1 )
{
    // Here we get and show our foos
    $this->load->model('foos_model');
    $view_data['foos'] = $this->foos_model->get_foos( $page );
}

模型中的工作全部完成

请记住，在模型中，我们需要foos，分页链接创建以及此页面的foos的总数。让我们开始计算foos：

The Work is All Done in the Model

Remember, in the model we need a total count of our foos, pagination link creation, and then the foos for this page. Let's start with counting foos:

public function get_foos( $page )
{
    // First count all foos
    $count = $this->db->count_all('foos');
}

接下来，创建分页链接。假设我们要每页10个foos：

Next, create the pagination links. Let's say we want 10 foos per page:

public function get_foos( $page )
{
    // First count all foos
    $count = $this->db->count_all('foos');

    // Create the pagination links
    $this->load->library('pagination');
    $this->load->helper('url');

    $paging_conf = [
        'uri_segment'      => 3,
        'per_page'         => 10,
        'total_rows'       => $count,
        'base_url'         => site_url('foos/page'),
        'first_url'        => site_url('foos'),
        'use_page_numbers' => TRUE
    ];
    $this->pagination->initialize($paging_conf);

    // Create the paging buttons for the view
    $this->load->vars('pagination_links', $this->pagination->create_links());
}

现在是时候获取我们将显示的实际foo集了。注意偏移量的计算：

Now it's time to get the actual set of foos we will display. Notice the calculation of the offset:

public function get_foos( $page )
{
    // First count all foos
    $count = $this->db->count_all('foos');

    // Create the pagination links
    $this->load->library('pagination');
    $this->load->helper('url');

    $paging_conf = [
        'uri_segment'      => 3,
        'per_page'         => 10,
        'total_rows'       => $count,
        'base_url'         => site_url('foos/page'),
        'first_url'        => site_url('foos'),
        'use_page_numbers' => TRUE
    ];
    $this->pagination->initialize($paging_conf);

    // Create the paging buttons for the view
    $this->load->vars('pagination_links', $this->pagination->create_links());

    // The pagination offset
    $offset = $page * $paging_conf['per_page'] - $paging_conf['per_page'];

    // Get our set of foos
    $query = $this->db->get('foos', $paging_conf['per_page'], $offset);
}

最后，在将它们传递回控制器之前，请确保存在foos：

Finally, make sure there were foos before passing them back to the controller:

public function get_foos( $page )
{
    // First count all foos
    $count = $this->db->count_all('foos');

    // Create the pagination links
    $this->load->library('pagination');
    $this->load->helper('url');

    $paging_conf = [
        'uri_segment'      => 3,
        'per_page'         => 10,
        'total_rows'       => $count,
        'base_url'         => site_url('foos/page'),
        'first_url'        => site_url('foos'),
        'use_page_numbers' => TRUE
    ];
    $this->pagination->initialize($paging_conf);

    // Create the paging buttons for the view
    $this->load->vars('pagination_links', $this->pagination->create_links());

    // The pagination offset
    $offset = $page * $paging_conf['per_page'] - $paging_conf['per_page'];

    // Get our set of foos
    $query = $this->db->get('foos', $paging_conf['per_page'], $offset);

    // Make sure we have foos
    if( $query->num_rows() > 0 )
        return $query->result();

    // Else return default
    return NULL;
}

回到我们的控制器中，我们可以将foos传递给视图：

Back in our controller, we can pass the foos to the view:

public function page( $page = 1 )
{
    // Here we get and show our foos
    $this->load->model('foos_model');
    $view_data['foos'] = $this->foos_model->get_foos( $page );

    // Load the view and pass in the foos
    $this->load->view('foos_view', $view_data);
}

显示分页链接和foos

在视图中，我们现在可以显示分页链接和foos：

Display the pagination links and foos

In the view, we now have the ability to display our pagination links and foos:

echo $pagination_links;

if( ! empty( $foos ) )
{
    foreach( $foos as $foo )
        echo $foo->name . '<br />';
}

结论

使用CodeIgniter进行分页非常简单。如果您有疑问或问题，请阅读CodeIgniter的文档： https：// www.codeigniter.com/userguide3/libraries/pagination.html?highlight=pagination

从功能上讲，只要您没有犯错误，就可以使用$ this-> db-> query（）将产生与$ this-> db-> get（）相同的结果。在我的示例中，它看起来像这样：

Functionally, as long as you don't make mistakes, the use of $this->db->query() would produce the same results as $this->db->get(). In the case of my example, it would look like this:

// $query = $this->db->get('foos', $paging_conf['per_page'], $offset);

$query = $this->db->query(
    'SELECT *
    FROM foos
    LIMIT ' . $offset . ', ' . $paging_conf['per_page'] );

这篇关于如何在带有$ this-> db-> query（）的codeigniter中使用分页？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！