John Davidson

Message:

Pagination worked fine in ASC order but I just can't get it to work in DESC order.

I have 10 test videos in my database with ids from 1-10, in the first batch I want to get the last 5 videos (ids: 10,9,8,7,6) and in the next batch the remaining ones (id:5,4,3,2,1).

The problem is that I always get empty results when using DESC order, here is my code:

    $limit = 5;



    $this->db->select('*');

    $this->db->from('participant_videos');

    $this->db->order_by('id','DESC');

    $this->db->limit($limit,$index); // currently index is 10

    $query=$this->db->get();

    

    $videos = $query->result_array(); // empty array

    echo $this->db->last_query();

    // QUERY: SELECT * FROM `participant_videos` ORDER BY `id` DESC LIMIT 10, 5



    return [

        'videos' => $videos,

        'index' => empty($videos) ? 0 : $index - $limit,

    ]; 

First time the index is determined like this:

 public function get_latest_video () {

    

    $video = $this->db->select("*")->limit(1)->order_by('id',"DESC")->get("participant_videos")->row_array();

    

    return !empty($video['id']) ? $video['id'] : null; // index (10)



}

This is my table structure and content:

== Table structure for table participant_videos



|------

|Column|Type|Null|Default

|------

|//**id**//|int(11)|No|

|participant_id|int(11)|No|

|video_url|varchar(255)|No|

|video_type|varchar(255)|No|video/mp4

== Dumping data for table participant_videos



|2|2|/uploads/2-6138aef5a0717.mp4|video/mp4

|3|3|/uploads/3-6138b09449800.mp4|video/mp4

|4|4|/uploads/4-6138b0c3b1965.mp4|video/mp4

|5|5|/uploads/5-6138b0efa7aa1.mp4|video/mp4

|6|6|/uploads/6-6138b10667680.mp4|video/mp4

|7|7|/uploads/7-6138b154084a4.mp4|video/mp4

|8|8|/uploads/8-6138b1779bee0.mp4|video/mp4

|9|9|/uploads/9-613b4bc1e1d58.mp4|video/mp4

|10|16|/uploads/16-613b57a76c696.mp4|video/mp4

---

My Answer:
load->model('Video_model');__ $total_rows = $this->Video_model->get_total_videos();__ $offset = $total_rows - $limit;__ $videos = $this->Video_model->get_videos($limit, $offset, 'desc');__ foreach($videos as $video) {__ echo $video->id;__ }__ // Video_model.php__ public function get_total_videos() {__ return $this->db->count_all('videos');__ }__ public function get_videos($limit, $offset, $order) {__ $this->db->order_by('id', $order);__ $this->db->limit($limit, $offset);__ $query = $this->db->get('videos');__ return $query->result();__ }__I have tried different combinations of $limit and $offset values but I always get empty results when using DESC order. What am I doing wrong?