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.

18485569 1371908909572883 6954592115736688669 n

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.

Screenshot from 2017 08 20 21 25 56

Step 2: create an index for the new bucket: commenting

Click on the query tab, insert the query listed below, and click on execute.

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.

Now, open up the file you just cloned in your browser. You should see the following page:

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:

In the db.php,  we connected to Couchbase and opened a bucket.You should change the details to correspond to your Couchbase information.

have some HTML code.

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.

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.

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> .

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.

Peek 2017 08 23 19 35

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

Author

Posted by Laura Czajkowski, Developer Community Manager, Couchbase

Laura Czajkowski is the Snr. Developer Community Manager at Couchbase overseeing the community. She’s responsible for our monthly developer newsletter.

Leave a reply