How can I get the current post's index by slug?

I am working on an online newspaper/blogging application with CodeIgniter 3.1.8 and Bootstrap 4.

At the bottom of the single post view, I want to add a link to the next post (as well as one to the previous post). For this, I need to get the data (slug, title, etc), of the next post (row in the posts table).

For this purpose, I have added this methods to my Posts_model model:

PHP Code:

/* Next post */
public function get_next_post($slug) {
        $query = $this->db->get('posts');
        $row_index = 6;
        $data = $query->row_array($row_index);    
        if ($query->num_rows() > 0) {
            $data = $query->next_row();
            return $data;
        }
}

/* Prev post */
public function get_prev_post($slug) {
        $query = $this->db->get('posts');
        $row_index = 6;
        $data = $query->row_array($row_index);    
        if ($query->num_rows() > 0) {
            $data = $query->previous_row();
            return $data;
        }
 } 


IMPORTANT:

The posts are displayed newest on top:

PHP Code:

public function get_posts($limit, $offset) {
    $this->db->select('posts.*, categories.name as post_category, authors.first_name as author_fname, authors.last_name as author_lname');
    $this->db->order_by('posts.id', 'DESC');
        $this->db->join('categories', 'posts.cat_id = categories.id', 'inner');
        $this->db->join('authors', 'posts.author_id = authors.id', 'inner');
    $query = $this->db->get('posts', $limit, $offset);
    return $query->result();
} 


If I could get the current post's index by slug, I could replace this hardcoded index of the 7th post - $row_index = 6 - and the problem would be solved.

How do I do that?

What determines the order of the posts? Is it the ID, or the date? Why not create a function where you pass in the ID or the date of the current post, to get the next one. The SQL would be something like:

SELECT * FROM `posts` WHERE `ID` > ? ORDER BY `ID` ASC LIMIT 1

This will get you the next post in the list by ID. If your posts are ordered by date rather than ID, it would be:

SELECT * FROM `posts` WHERE `date` > ? ORDER BY `date` ASC LIMIT 1

Hey, thanks for the help.

Getting the next (newer) post works:

PHP Code:

public function get_next_post($slug) {
    $this->db->where('id >', $this->get_post($slug)->id);
    $this->db->limit(1);
    return $this->db->get('posts')->row_array();
} 


Getting the previous (older) post does not work:

PHP Code:

public function get_prev_post($slug) {
  $this->db->where('id <', $this->get_post($slug)->id);
  $this->db->limit(1);
  return $this->db->get('posts')->row_array();
} 


It always returns the first post (lowest id) in the entire posts table.

The posts are ordered by id, DESCENDING (heigh ids on top of low ones).

How do I fix this?

What determines the order of the posts?  Is it the ID, or the date?  Why not create a function where you pass in the ID or the date of the current post, to get the next one.  The SQL would be something like:

SELECT * FROM `posts` WHERE `ID` > ? ORDER BY `ID` ASC LIMIT 1

This will get you the next post in the list by ID.  If your posts are ordered by date rather than ID, it would be:

SELECT * FROM `posts` WHERE `date` > ? ORDER BY `date` ASC LIMIT 1