How to Page PostgreSQL Data with PHP

Introduction

Paging is a method of retrieving small, defined chunks from a larger set of data. For example, if you're running an online store with thousands of products, you can display 50 items at a time and give customers navigation buttons to move between the pages.

In PostgreSQL, you can retrieve pages with the LIMIT and OFFSET clauses. Use the LIMIT clause to define the number of rows to retrieve and the OFFSET keyword to specify the number of rows to skip from the beginning.

Prerequisite

To complete this PostgreSQL pagination tutorial, ensure you have the following.

• An Ubuntu 20.04 server.

• A non-root user with sudo rights.

• A PostgreSQL database server.

• Apache webserver and PHP. For this tutorial, you can skip Step - 2. Install a Database Server since you'll use PostgreSQL as the database server.

1. Install PHP Extensions

1. SSH to your server and update the package information index.

   $ sudo apt -y update
   

2. Install the pdo_pgsql driver. PHP requires this extension to communicate to the PostgreSQL server.

   $ sudo apt install -y php-pgsql
   

3. Restart Apache to load the new changes.

   $ sudo systemctl restart apache2
   

2. Create a Test Database

1. Log in to your PostgreSQL server as the postgres user.

   $ sudo -u postgres psql
   

2. Enter the password and press ENTER to proceed.

3. Create a test_shop database.

   postgres=# CREATE DATABASE test_shop;
   

4. Switch to the new test_shop database.

   postgres=# \c test_shop;
   

5. Create a products table.

   test_shop=# CREATE TABLE products (
   
               product_id SERIAL PRIMARY KEY,
   
               product_name VARCHAR (50),
   
               retail_price NUMERIC
   
               );
   

6. Insert some data into the products table.

   test_shop=# INSERT INTO products(product_name, retail_price) VALUES ('RAIN JACKET', 55.60 );
   
               INSERT INTO products(product_name, retail_price) VALUES ('LEATHER BELT', 9.63);
   
               INSERT INTO products(product_name, retail_price) VALUES ('WOOLEN SWEATER', 55.32);
   
               INSERT INTO products(product_name, retail_price) VALUES ('JUMP SUIT', 18.30 );
   
               INSERT INTO products(product_name, retail_price) VALUES ('JEANS HANGER', 2.35);
   
               INSERT INTO products(product_name, retail_price) VALUES ('KITCHEN TOWEL', 1.89);
   
               INSERT INTO products(product_name, retail_price) VALUES ('PEN ORGANIZER', 3.65);
   
               INSERT INTO products(product_name, retail_price) VALUES ('RECHARGEABLE TORCH', 22.60);
   
               INSERT INTO products(product_name, retail_price) VALUES ('COTTON SWABS', 1.23);
   
               INSERT INTO products(product_name, retail_price) VALUES ('TOOTH PICKS', 2.20);
   
               INSERT INTO products(product_name, retail_price) VALUES ('USB CABLE', 3.50);
   
               INSERT INTO products(product_name, retail_price) VALUES ('QUICK CHARGER', 35.60);
   

7. Make sure the records are in place by executing a SELECT statement against the products table.

   test_shop=# SELECT
   
               product_id,
   
               product_name,
   
               retail_price
   
               FROM products;
   

   Confirm the output below.

    product_id |    product_name    | retail_price
   
   ------------+--------------------+--------------
   
             1 | RAIN JACKET        |        55.60
   
             2 | LEATHER BELT       |         9.63
   
             3 | WOOLEN SWEATER     |        55.32
   
             4 | JUMP SUIT          |        18.30
   
             5 | JEANS HANGER       |         2.35
   
             6 | KITCHEN TOWEL      |         1.89
   
             7 | PEN ORGANIZER      |         3.65
   
             8 | RECHARGEABLE TORCH |        22.60
   
             9 | COTTON SWABS       |         1.23
   
            10 | TOOTH PICKS        |         2.20
   
            11 | USB CABLE          |         3.50
   
            12 | QUICK CHARGER      |        35.60
   
   (12 rows)
   

8. Exit PostgreSQL.

   test_shop=# \q
   

3. Create a PHP Script

To display paged PostgreSQL data, you'll connect to your database from a PHP script. Then, you'll display the data on a standard HTML web page.

1. Your PHP file should reside in the root directory of your webserver. To create it, open a new /var/www/html/products.php file using nano.

   $ sudo nano /var/www/html/products.php
   

2. Define a new HTML document and include a title, a head, and a body tag. Then, define an HTML table. You'll use it to list the products from your database.

   <html>
   
     <head>
   
       <title>Paginated PostgreSQL Data</title>
   
     </head>
   
     <body>
   
       <h1 align = 'center'> Products List </h1>
   
       <table align='center' border='1px'>
   
         <tr align='left'>
   
           <th>Id</th>
   
           <th>Product Name</th>
   
           <th>Retail Price</th>
   
         </tr>
   

3. Create the PHP content below to connect to the database that you created earlier. Replace EXAMPLE_PASSWORD with the correct password for the postgres user.

       <?php
   
   
   
           try {
   
               $db_name     = 'test_shop';
   
               $db_user     = 'postgres';
   
               $db_password = 'EXAMPLE_PASSWORD';
   
               $db_host     = 'localhost';
   
   
   
               $pdo = new PDO('pgsql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
   
               $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   
               $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
   

4. Define an $sql_count statement below. Use the PDO prepare and execute methods to get the total count of products and place the results in a $count variable.

               $sql_count = 'select count(*) as count
   
                             from products
   
                            ';
   
   
   
               $stmt = $pdo->prepare($sql_count);
   
               $stmt->execute();
   
               $row_count = $stmt->fetch();
   
               $count     = $row_count['count'];
   

5. In this script, you're retrieving the current page using the page URL variable. The value changes once you click the navigation links. Use the code below to retrieve the $_GET['page'] variable only if set. Otherwise, use 1 as the default $page value.

           if (isset($_GET['page'])) {
   
               $page = $_GET['page'];
   
           } else {
   
               $page = 1;
   
           }
   

6. Assign the variable $per_page a value; this controls the total products that you want to display on each page. For this tutorial, use 5. When you visit this script on a web browser, you'll see 5 products on each page. To instruct the PHP script the total number of pages that it should expect, use the formula $total_pages = ceil($count / $per_page);. This rounds off any decimals since it is not possible to have fractions in pages.

   In this guide, your page count($total_pages) should be 3 since your products table has 12 records. If you divide 12 by 5, you'll get 2.4. After rounding this figure off with the PHP ceil function, you get 3 pages.

   Next, define an $offset variable. This represents the number of rows to skip when executing the $sql statement. Determine the value of this variable using the formula ($page - 1) * $per_page;. To page the results from products table, include the $per_page and $offset variables in the SQL string using the syntax ...limit ' . $per_page . ' offset ' . $offset.

           $per_page  = 5;
   
           $offset = ($page - 1) * $per_page;
   
   
   
           $total_pages = ceil($count / $per_page);
   
   
   
           $sql = 'select
   
                   product_id,
   
                   product_name,
   
                   retail_price
   
                   from products
   
                   limit ' . $per_page . ' offset ' . $offset ;
   

7. Prepare the SQL command above and execute it. When the sql string is completed, it should execute the following commands depending on the current page.

   • Page 1: /products.php?page=1

     'select productid, productname, retail_price from products limit 5 offset 0;

   • Page 2: /products.php?page=2

     'select productid, productname, retail_price from products limit 5 offset 5;

   • Page 3: /products.php?page=3

     'select productid, productname, retail_price from products limit 5 offset 10;

8. Enter the code below to loop through the records and list them on the HTML table.

           $stmt = $pdo->prepare($sql);
   
           $stmt->execute();
   
   
   
           while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   
               echo '<tr>
   
                       <td>' . $row['product_id'] . '</td>
   
                       <td>' . $row['product_name'] . '</td>
   
                       <td align="right">' . $row['retail_price'] . '</td>
   
                     </tr>';
   
           }
   

9. Echo out the current page number and the total pages in a new table row.

           echo "<tr align='center'>"
   
                   . "<td colspan='3'>Page " . $page . " of " . $total_pages . "</td>"
   
                . "</tr>";
   

10. Populate the $pagination_urls variable. This is a list of clickable navigation links for moving between the pages. The First page and Last page links are mandatory and will be active on all pages.

            $pagination_urls = '';
    
    
    
            $pagination_urls .= "<a href='/products.php?page=1'>First </a>";
    

11. To display a Previous page link, get the value of the current page from the $page variable and decrement it once. Use the PHP if (...) {...} else {...} to disable the link by removing the href tag if you're already on the First page.

            if ($page != 1) {
    
                $pagination_urls .= "&nbsp;&nbsp;<a href='/products.php?page=". ($page - 1) . "'>Previous</a>";
    
            } else {
    
                $pagination_urls .= "&nbsp;&nbsp;<a>Previous</a>";
    
            }
    

12. To get a link for the Next page, increment the $page variable once. Also, disable the Next page link if you're already on the Last page.

            if ($page != $total_pages) {
    
                $pagination_urls .= "&nbsp;&nbsp;<a href='/products.php?page=". ($page + 1) . "'>Next</a>";
    
            } else {
    
                $pagination_urls .= "&nbsp;&nbsp;<a>Next</a>";
    
            }
    
    
    
            $pagination_urls .= "&nbsp;&nbsp;<a href='/products.php?page=" . $total_pages ."'>Last</a>";
    

13. Finally, echo out the pagination URLs inside a <td> tag and close the php, table, body, and html tags.

                echo "<tr align='center'>"
    
                        . "<td colspan='3'>" . $pagination_urls . "</td>"
    
                     . "</tr>";
    
    
    
            } catch (PDOException $e) {
    
                echo 'Database error.' . $e->getMessage();
    
            }
    
        ?>
    
        </table>
    
      </body>
    
    </html>
    

When completed, your PHP file should be similar to the following content.

<html>

  <head>

    <title>Paginated PostgreSQL Data</title>

  </head>

  <body>

    <h1 align = 'center'> Products List </h1>

    <table align='center' border='1px'>

      <tr align='left'>

        <th>Id</th>

        <th>Product Name</th>

        <th>Retail Price</th>

      </tr>



    <?php



        try {

            $db_name     = 'test_shop';

            $db_user     = 'postgres';

            $db_password = 'EXAMPLE_PASSWORD';

            $db_host     = 'localhost';



            $pdo = new PDO('pgsql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);

            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);



            $sql_count = 'select count(*) as count

                          from products

                         ';



            $stmt = $pdo->prepare($sql_count);

            $stmt->execute();

            $row_count = $stmt->fetch();

            $count     = $row_count['count'];



            if (isset($_GET['page'])) {

                $page = $_GET['page'];

            } else {

                $page = 1;

            }



            $per_page  = 10;

            $offset = ($page - 1) * $per_page;



            $total_pages = ceil($count / $per_page);



            $sql = 'select

                    product_id,

                    product_name,

                    retail_price

                    from products

                    limit ' . $per_page . ' offset ' . $offset ;





            $stmt = $pdo->prepare($sql);

            $stmt->execute();



            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

                echo '<tr>

                        <td>' . $row['product_id'] . '</td>

                        <td>' . $row['product_name'] . '</td>

                        <td align="right">' . $row['retail_price'] . '</td>

                      </tr>';

            }



            echo "<tr align='center'>"

                    . "<td colspan='3'>Page " . $page . " of " . $total_pages . "</td>"

                 . "</tr>";



            $pagination_urls = '';



            $pagination_urls .= "<a href='/products.php?page=1'>First </a>";





            if ($page != 1) {

                $pagination_urls .= "&nbsp;&nbsp;<a href='/products.php?page=". ($page - 1) . "'>Previous</a>";

            } else {

                $pagination_urls .= "&nbsp;&nbsp;<a>Previous</a>";

            }



            if ($page != $total_pages) {

                $pagination_urls .= "&nbsp;&nbsp;<a href='/products.php?page=". ($page + 1) . "'>Next</a>";

            } else {

                $pagination_urls .= "&nbsp;&nbsp;<a>Next</a>";

            }



            $pagination_urls .= "&nbsp;&nbsp;<a href='/products.php?page=" . $total_pages ."'>Last</a>";



            echo "<tr align='center'>"

                    . "<td colspan='3'>" . $pagination_urls . "</td>"

                 . "</tr>";



        } catch (PDOException $e) {

            echo 'Database error.' . $e->getMessage();

        }

    ?>

    </table>

  </body>

</html>

Save the file by pressing CTRL + X, then Y and ENTER. Your PostgreSQL pagination script is now ready for testing.

4. Test the PHP Script

In a web browser, visit the URL below and replace 192.0.2.1 with your server's correct public IP address or domain name.

http://192.0.2.1/products.php

You should now see the First page displaying 5 products as shown below. As you can see from the output below, the Previous page link is disabled since you're already on the First page.

• Page 1:

  

  Click Next to navigate to the second page and note how the URL on your web browser changes to include the page parameter.

• Page 2:

  

  Again, click Next or Last to navigate to the Last page. The Next link is disabled on this page since you're already on the Last page.

• Page 3:

  

  The above outputs confirm that your PHP script is now working as expected.

Conclusion

In this guide, you've created a sample database and a table. You've then populated the table with sample records and coded a PHP script to page data using the PostgreSQL LIMIT and OFFSET clauses. Use the syntax in this guide to page your database data when working with many records.