Onwuka Gideon is a freelance full stack developer with years of experience designing and coding web applications and solving complex problems. He loves security, writing, and discussing new technology.
When building applications that deal with a large number of documents, it is important to use pagination to get rows by page.
In this article, I’ll demonstrate how to implement pagination when working with N1QL and PHP.
A little about N1QL
Couchbase N1QL is a declarative query language that extends SQL for JSON. You can query data via native framework and language integration, a fluent API, or the JDBC/ODBC drivers. N1QL gives developers an expressive, powerful, and complete language for querying and manipulating data.
Prerequisites
- Basic knowledge of Couchbase and have it set up on your server (Couchbase installation)
- Basic knowledge of N1QL
- Basic knowledge of PHP (optional, since any language can be used)
- An available Couchbase SDK (Go here)
What we would build
For the purpose of this article, we’ll quickly set up a PHP environment where we’ll basically pull data from the database, paginate the data, and display it.
Setting up the environment
Step 1: create a new bucket
Log in to your Couchbase admin area and create a new bucket name commenting.
Step 2: create an index for the new bucket: commenting
Click on the query tab, insert the query listed below, and click on execute.
|
1 |
create primary index on `commenting` using gsi; |
If everything goes smoothly, we are good to start creating documents.
Step 3: clone the project starter file
I’ve created the basic file structure of the files we’ll be using for this tutorial.
Open up your command line, then clone the repository from github.
|
1 |
$ git clone git@github.com:dongido001/php-couchbase-pagination_complete.git |
Now, open up the file you just cloned in your browser. You should see the following page:
The file structure
The repository you just cloned contains 3 PHP files: config.php, db.php, and index.php.
The config.php file is where we define the information of our database:
|
1 2 3 4 5 6 |
<?php //some configs define("C_USERNAMAE", "Administrator"); define("C_PASSWORD", "password"); define("C_URL", "http://127.0.0.1:8091/"); //eg: localhost:8091 define("DEFAULT_BUCKET", "commenting"); |
In the db.php, we connected to Couchbase and opened a bucket.You should change the details to correspond to your Couchbase information.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<?php $authenticator = new \Couchbase\ClassicAuthenticator(); $authenticator->cluster(C_USERNAMAE, C_PASSWORD); $cluster = new \Couchbase\Cluster(C_URL); $cluster->authenticate($authenticator); $bucket = $cluster->openBucket(DEFAULT_BUCKET); In the `index.php`, we have some PHP code at the top of the file and at the bottom, we also |
have some HTML code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
//index.php <?php //include config details require_once('config.php'); //include connection to the database. require_once('db.php'); if( $_SERVER['REQUEST_METHOD'] == "POST"){ $uid = uniqid(); $comment = [ "_id" => $uid, "name" => $_POST["name"], "comment" => $_POST["comment"], "created_at" => date("Y-m-d H:i:s") ]; $bucket->insert($uid, $comment); } ?> ... |
I included the two files, config.php and db.php then I wrote logic to insert some comments into the database when the form is submitted.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
//index.php ... <!DOCTYPE html> <html lang="en"> <head> <!-- Required meta tags --> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <!-- Bootstrap CSS --> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/css/bootstrap.min.css" integrity="sha384-/Y6pD6FV/Vv2HJnA6t+vslU6fwYXjCFtcEpHbNJ0lyAFsXTsjBbfaDjzALeQsN6M" crossorigin="anonymous"> </head> <body> <div class="container"> <div class="container" style="margin-left: auto; margin-right: auto; width: 400px;"> <h4 class="text-center">Add a comment</h4> <form action="" method="POST"> <div class="form-group"> <label for="name">Name:</label> <input type="text" class="form-control" name="name" required> </div> <div class="form-group"> <label for="comment">Comment:</label> <textarea class="form-control" rows="5" name="comment" required> </textarea> </div> <button type="submit" class="btn btn-primary">Submit</button> </form> <br /> <div > <h4 class="text-center">Listing comments</h4> </div> <div id="pagination"> </div> </div> </div> </body> </html> |
The HTML code displays a form for adding and listing comments.
Now let’s get started!
Add as many comments from the page as you like. We will be paginating that data soon.
Fetch paginated content in the database
Add the following to the header part of index.php that is below every PHP code at the top.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
//Select all contentin our query $no_per_page = 4; //This is the number of Items we want per page $current_page = (isset($_GET['page_id'])) ? $_GET['page_id'] : 1; //Gets the current page, if not set, default to page 1 $skip = ($current_page - 1) * $no_per_page; //Gets the total number of page we want to skip when making request. When we are on page one, We don't need to skip any Item. $query = \Couchbase\N1qlQuery::fromString("SELECT * FROM `commenting` LIMIT $no_per_page OFFSET $skip"); $result = $bucket->query($query); //count total number of result in the database $total = $bucket->query(\Couchbase\N1qlQuery::fromString("SELECT COUNT(comment) total FROM `commenting`")); $total = $total->rows[0]->total; $number_of_pages = ceil( $total/$no_per_page ); //calculate number of pages |
I’ve added comments to the code above, which describes what each does. Here we also used LIMIT AND OFFSET which enabled us to LIMIT the number of results each query is meant to return. The OFFSET was used to skip over an amount of data when querying depending on the page number we are currently accessing.
LIMIT: The LIMIT clause specifies the maximum number of objects that can be returned in a result set by SELECT. A negative value or a value greater than 9223372036854775295 (result of 1 – 512) is considered as LIMIT 0.
OFFSET: The OFFSET clause specifies a number of objects to be skipped. If a LIMIT clause is also present, the OFFSET is applied prior to the LIMIT. The OFFSET value must be a non-negative integer.
Populate the result of the query to the page
Add the code below to index.php inside this tag, immediately after <h4 class="text-center">Listing comments</h4> .
|
1 2 3 4 5 6 7 8 9 10 |
<?php foreach( $result->rows as $comment ):?> <div> <div class="alert alert-success" role="alert"> <h5 class="alert-heading"> By: <?=$comment->commenting->name?>, Created At: <?=$comment->commenting->created_at?> </h5><br> <p> <?=$comment->commenting->comment?></p> <hr> </div> </div> <?php endforeach; ?> |
Add the paginated link
Add the code below in between this html tag <div id="pagination"> </div> at the bottom part of index.php.
|
Here we are just printing out the number of pages to the page.
Yeah! Our pagination is working now.
Conclusion
In this article, you have learned how to implement pagination using N1QL. We’ve also reviewed OFFSET and LIMIT and how to basically run queries in Couchbase using N1QL. You can get the complete app here: (git@github.com:dongido001/php-couchbase-pagination_complete.git)
Thanks for reading. Let me know what you think or if you have any questions.
This post is part of the Couchbase Community Writing Program
English