如何将查询结果拆分为多页（分页）和排序查询数据？

Question

In my model I get the entire order table.

return $this->db->get('order')->result();

In my view i do a foreach to loop through the data.

<?php foreach ($orders as $order) : ?>
<tr>
   <td><?php echo $order->id; ?></td>
   <td><?php echo $order->order_status; ?></td>
   <td><?php echo $order->cart; ?></td>
     . . . 
<?php endforeach; ?>

Ok so that works like a charm. However I need to be able to do some data arranging here.

1. I want to be able to sort records by id so that I can get the latest on top
2. I want to split the results (pagination) say every 25 records and add the (go to next page link)

I'm using Codeigniter and building on the MVC pattern. So is their a library perhaps or built-in functions in CI that allow me accomplish these 2 objectives? I appreciate all helpful advice, code examples and general guidance. Thank you in advance for your time! :)

Edit 1

Sorry I didn't mention this.. Can someone give me an example of how the query needs to be structured return $this->db->get('order', 25)->result(); isn't exactly giving me the desired result here.

Answer 1

You can use Datatables jquery plugin along with server side implementation , There is already an implementation of datatables wrapper class for code ignitor below is the link for the repository

It is called Ignited dataTables

Ignited datatables at github

You can fork the above repository and try out with your needs

and also using datatables has another advantage , you can specify server side calls for almost all nice to have functionalities

such as search in table , pagination , sorting , etc

EDIT : - After looking at your edit , I found that you are also having problem in fetching data in server side , Here is the codeignitor model function , Put this function in the model class and instantiate and call in your model .

Pass limit and sort by columns names appropriately

function getResult($limit, $offset, $sort_by, $sort_order) {

        $sort_order = ($sort_order == 'desc') ? 'desc' : 'asc';
        $sort_columns = array('id', 'title', 'category', 'rating', 'price');

            // set default column name to sortby as per your need 
        $sort_by = (in_array($sort_by, $sort_columns)) ? $sort_by : 'title';

        // results query  
            // Change column names as per your need 

        $q = $this->db->select('id, title, category,  rating, price')
            ->from('Order')
            ->limit($limit, $offset)
            ->order_by($sort_by, $sort_order);

        $ret['rows'] = $q->get()->result();

        // count query
        $q = $this->db->select('COUNT(*) as count', FALSE)
            ->from('Order');

        $tmp = $q->get()->result();

        $ret['num_rows'] = $tmp[0]->count;

        return $ret;
    }