Finding previous and next database table item with Query Builder in CodeIgniter 4

Created:28 Jan 2022 21:39:38 , in  Web development

Imagine you have a database table Posts with items like blog posts, news and pages. Each of those items has its unique id and type like "page" or "news". Given an id of a database item, your goal is to find the previous and the next item of the same type relative to the id. In this post I look at how to achieve this with Query Builder queries in CodeIgniter 4.

In your Post model, place add next and previous methods:

namespace App\Models;  
use CodeIgniter\Model;
class PostModel extends Model{
.
.
.
 
  public function next($currentPostId,$type = 'post'){
    return $this -> where(['id >' => $currentPostId,'type' => $type]) -> first();
  }

  // find previous post
  public function previous($currentPostId,$type = 'post'){
    return $this -> where(['id <' => $currentPostId,'type' => $type]) -> first(); 
  }  
.
.
.
}

Now, to find the next and previous item of type "page" relative to your current page with id given by $currentPostId, add the following piece of code in your controller:

$currentPostId = 10; // change this according to you needs
$modelPost = new PostModel();
$previous = $modelPost -> previous($currentPostId,'page');
$next = $modelPost -> next($currentPostId,'page'); 

What's left is, consume $previous and $next variables in a view. If you do you will get a simple two way navigation between pages.

That's it. Now you should be able to write a simple backwards and forwards navigation for your database table items in minutes.

This post was updated on 28 Jan 2022 21:40:27

Tags:  php