Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Pagination in Couchbase Server With N1QL and PHP

DZone's Guide to

Pagination in Couchbase Server With N1QL and PHP

If you are building applications that deal with large databases, knowing how to use pagination to get rows by page is critical.

· Database Zone
Free Resource

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

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)
  • Basic knowledge of N1QL
  • Basic knowledge of PHP (optional, since any language can be used)
  • An available Couchbase SDK

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

This involves three simple steps.

Step 1: Create a New Bucket

Log into your Couchbase admin area and create a new bucket name commenting.

Step 2: Create an Index for the New Bucket

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

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.

$ 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 three PHP files: config.php, db.php, and index.php.

The config.php file is where we define the information of our database:

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

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

This one has some HTML code:

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

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

&nbsp;//Select all contentin our query 

$no_per_page = 4; //This is the number of Items we want per page

&nbsp;$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` &nbsp;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: This 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: This clause specifies the number of objects to be skipped. If a LIMIT clause is also present, 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>.

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

    <nav aria-label="Page navigation example">
<ul class="pagination">
<?php for($i = 1; $i <= $number_of_pages; $i++): ?>
<li class="page-item"><a class="page-link" href="?page_id=<?=$i?>"><?=$i?></a></li>
<?php endfor; ?>
</ul>
</nav>

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. 

Thanks for reading. Let me know what you think or if you have any questions!

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

Topics:
database ,tutorial ,couchbase ,n1ql ,php ,pagination ,html

Published at DZone with permission of Gideon Onwuka, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}