Daisy Rees

Daisy Rees

1572399888

How To Implement Pagination in MySQL with PHP on Ubuntu 18.04

Introduction

Pagination is the concept of constraining the number of returned rows in a recordset into separate, orderly pages to allow easy navigation between them, so when there is a large dataset you can configure your pagination to only return a specific number of rows on each page. For example, pagination can help to avoid overwhelming users when a web store contains thousands of products by reducing the number of items listed on a page, as it’s often unlikely a user will need to view every product. Another example is an application that shows records on a mobile device; enabling pagination in such a case would split records into multiple pages that can fit better on a screen.

Besides the visual benefits for end-users, pagination makes applications faster because it reduces the number of records that are returned at a time. This limits the data that needs to be transmitted between the client and the server, which helps preserve server resources such as RAM.

In this tutorial, you’ll build a PHP script to connect to your database and implement pagination to your script using the MySQL LIMIT clause.

Step 1 — Creating a Database User and a Test Database

In this tutorial you’ll create a PHP script that will connect to a MySQL database, fetch records, and display them in an HTML page within a table. You’ll test the PHP script in two different ways from your web browser. First, creating a script without any pagination code to see how the records are displayed. Second, adding page navigation code in the PHP file to understand how pagination works practically.

The PHP code requires a MySQL user for authentication purposes and a sample database to connect to. In this step you’ll create a non-root user for your MySQL database, a sample database, and a table to test the PHP script.

To begin log in to your server. Then log in to your MySQL server with the following command:

sudo mysql -u root -p

Enter the root password of your MySQL server and hit ENTER to continue. Then, you’ll see the MySQL prompt. To create a sample database, which we will call test_db in this tutorial, run the following command:

Create database test_db;

You will see the following output:

OutputQuery OK, 1 row affected (0.00 sec)

Then, create a test_user and grant the user all privileges to the test_db. Replace PASSWORD with a strong value:

GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';

OutputQuery OK, 1 row affected (0.00 sec)

Reload the MySQL privileges with:

FLUSH PRIVILEGES;

OutputQuery OK, 1 row affected (0.00 sec)

Next, switch to the test_db database to start working directly on the test_db database:

Use test_db;

OutputDatabase changed

Now create a products table. The table will hold your sample products—for this tutorial you’ll require only two columns for the data. The product_id column will serve as the primary key to uniquely identify each record. You’ll use the product_name field to differentiate each item by name:

Create table products (product_id BIGINT PRIMARY KEY, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;

OutputQuery OK, 0 rows affected (0.02 sec)

To add ten test products to the products table run the following SQL statements:

Insert into products(product_id, product_name) values ('1', 'WIRELESS MOUSE');
Insert into products(product_id, product_name) values ('2', 'BLUETOOTH SPEAKER');
Insert into products(product_id, product_name) values ('3', 'GAMING KEYBOARD');
Insert into products(product_id, product_name) values ('4', '320GB FAST SSD');
Insert into products(product_id, product_name) values ('5', '17 INCHES TFT');
Insert into products(product_id, product_name) values ('6', 'SPECIAL HEADPHONES');
Insert into products(product_id, product_name) values ('7', 'HD GRAPHIC CARD');
Insert into products(product_id, product_name) values ('8', '80MM THERMAL PRINTER');
Insert into products(product_id, product_name) values ('9', 'HDMI TO VGA CONVERTER');
Insert into products(product_id, product_name) values ('10', 'FINGERPRINT SCANNER');

You’ll see this output:

OutputQuery OK, 1 row affected (0.02 sec)

Verify that the products were inserted to the table by running:

select * from products;

You’ll see the products in your output within the two columns:

Output+------------+-----------------------+
| product_id | product_name          |
+------------+-----------------------+
|          1 | WIRELESS MOUSE        |
|          2 | BLUETOOTH SPEAKER     |
|          3 | GAMING KEYBOARD       |
|          4 | 320GB FAST SSD        |
|          5 | 17 INCHES TFT         |
|          6 | SPECIAL HEADPHONES    |
|          7 | HD GRAPHIC CARD       |
|          8 | 80MM THERMAL PRINTER  |
|          9 | HDMI TO VGA CONVERTER |
|         10 | FINGERPRINT SCANNER   |
+------------+-----------------------+
10 rows in set (0.00 sec)

Exit MySQL:

quit;

With the sample database, table, and test data in place, you can now create a PHP script to display data on a web page.

Step 2 — Displaying MySQL Records Without Pagination

Now you’ll create a PHP script that connects to the MySQL database that you created in the previous step and list the products in a web browser. In this step, your PHP code will run without any form of pagination to demonstrate how non-split records show on a single page. Although you only have ten records for testing purposes in this tutorial, seeing the records without pagination will demonstrate why segmenting data will ultimately create a better user experience and put less burden on the server.

Create the PHP script file in the document root of your website with the following command:

sudo nano /var/www/html/pagination_test.php

Then add the following content to the file. Remember to replace PASSWORD with the correct value of the password that you assigned to the test_user in the previous step:

<?php

try {

    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

    $sql="select * from products";

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

    $stmt->execute();

    echo "<table border='1' align='center'>";

    while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
        echo "<tr>";

        echo "<td>".$row['product_id']."</td>";

        echo "<td>".$row['product_name']."</td>";

        echo "</tr>";

    }

    echo "</table>";

}

  catch(PDOException $e)

{
    echo  $e->getMessage();
}

?>

Save the file by pressing CTRL+X, Y, and ENTER.

In this script you’re connecting to the MySQL database using the PDO (PHP Data Object) library with the database credentials that you created in Step 1.

PDO is a light-weight interface for connecting to databases. The data access layer is more portable and can work on different databases with just minor code rewrites. PDO has greater security since it supports prepared statements—a feature for making queries run faster in a secure way.

Then, you instruct the PDO API to execute the select * from products statement and list products in an HTML table without pagination. The line $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); ensures that the data types are returned as they appear in the database. This means that PDO will return the product_id as an integer and the product_name as a string. $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); instructs PDO to throw an exception if an error is encountered. For easier debugging you’re catching the error inside the PHP try{}...catch{} block.

To execute the /var/www/html/pagination_test.php PHP script file that you’ve created, visit the following URL replacing your-server-IP with the public IP address of your server:

http://your-server-IP/pagination_test.php

You’ll see a page with a table of your products.

MySQL Records Displayed with a PHP script - No Pagination

Your PHP script is working as expected; listing all products on one page. If you had thousands of products, this would result in a long loop as the products are fetched from the database and rendered on the PHP page.

To overcome this limitation, you will modify the PHP script and include the MySQL LIMIT clause and some navigation links at the bottom of the table to add pagination functionality.

Step 3 — Implementing Pagination with PHP

In this step your goal is to split the test data into multiple and manageable pages. This will not only enhance readability but also use the resources of the server more efficiently. You will modify the PHP script that you created in the previous step to accommodate pagination.

To do this, you’ll be implementing the MySQL LIMIT clause. Before adding this to the script, let’s see an example of the MySQL LIMIT syntax:

Select [column1, column2, column n...] from [table name] LIMIT offset, records;

The LIMIT clause takes two arguments as shown toward the end of this statement. The offset value is the number of records to skip before the first row. records sets the maximum number of records to display per page.

To test pagination, you’ll display three records per page. To get the total number of pages, you must divide the total records from your table with the rows that you want to display per page. You then round the resulting value to the nearest integer using PHP Ceil function as shown in the following PHP code snippet example:

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

Following is the modified version of the PHP script with the full pagination code. To include the pagination and navigation codes, open the /var/www/html/pagination_test.php file:

sudo nano /var/www/html/pagination_test.php

Then, add the following highlighted code to your file:

<?php

try {

    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

    /* Begin Paging Info */

    $page=1;

    if (isset($_GET['page'])) {
        $page=filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);
    }

    $per_page=3;

    $sqlcount="select count(*) as total_records from products";
    $stmt = $pdo->prepare($sqlcount);
    $stmt->execute();
    $row = $stmt->fetch();
    $total_records= $row['total_records'];

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

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

    /* End Paging Info */

    $sql="select * from products limit $offset,$per_page";

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

    $stmt->execute();

    echo "<table border='1' align='center'>";

    while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
        echo "<tr>";

        echo "<td>".$row['product_id']."</td>";

        echo "<td>".$row['product_name']."</td>";

        echo "</tr>";

    }

    echo "</table>";

    /* Begin Navigation */

    echo "<table border='1' align='center'>";

    echo "<tr>";

    if( $page-1>=1) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Previous</a></td>";
    }

    if( $page+1<=$total_pages) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Next</a></td>";
    }

    echo "</tr>";

    echo "</table>";

    /* End Navigation */

}

catch(PDOException $e) {
        echo  $e->getMessage();
}

?>

In your file you’ve used additional parameters to execute paging:

  • $page : This variable holds the current page in your script. When moving between the pages, your script retrieves a URL parameter named page using the $_GET['page'] variable.
  • $per_page: This variable holds the maximum records that you want to be displayed per page. In your case, you want to list three products on each page.
  • $total_records: Before you list the products, you’re executing a SQL statement to get a total count of records in your target table and assigning it to the $total_records variable.
  • $offset: This variable represents the total records to skip before the first row. This value is calculated dynamically by your PHP script using the formula $offset=($page-1)*$per_page. You may adapt this formula to your PHP pagination projects. Remember you can change the $per_page variable to suit your needs. For instance, you might change it to a value of 50 to display fifty items per page if you’re running a website or another amount for a mobile device.

Again, visit your IP address in a browser and replace your_server_ip with the public IP address of your server:

http://your_server_ip/pagination_test.php

You’ll now see some navigation buttons at the bottom of the page. On the first page, you will not get a Previous button. The same case happens on the last page where you will not get the Next page button. Also, note how the page URL parameter changes as you visit each page.

MySQL Records Displayed with a PHP script with Pagination - Page 1

MySQL Records Displayed with a PHP script with Pagination - Page 2

Final page of MySQL Records Displayed with a PHP script with Pagination - Page 4

The navigation links at the bottom of the page are achieved using the following PHP code snippet from your file:

. . .
    if( $page-1>=1) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Previous</a></td>";
    }

    if( $page+1<=$total_pages) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Next</a></td>";
    }
. . .

Here, the $page variable represents the current page number. Then, to get the previous page, the code will minus 1 from the variable. So, if you’re on page 2, the formula (2-1) will give you a result of 1 and this will be the previous page to appear in the link. However, keep in mind that it will only show the previous page if there is a result greater or equal to 1.

Similarly, to get to the next page, you add one to the $page variable and you must also make sure that the $page result that we append to the page URL parameter is not greater than the total pages that you’ve calculated in your PHP code.

At this point, your PHP script is working with pagination and you are able to implement the MySQL LIMIT clause for better record navigation.

Conclusion

In this tutorial, you implemented paging in MySQL with PHP on an Ubuntu 18.04 server. You can use these steps with a larger recordset using the PHP script to include pagination. By using pagination on your website or application you can create better user navigation and optimum resource utilization on your server.

#php #mysql #databases #ubuntu

What is GEEK

Buddha Community

How To Implement Pagination in MySQL with PHP on Ubuntu 18.04
Shawn  Pieterse

Shawn Pieterse

1625711252

Installing PHP 8.0 on Ubuntu 20.04 and Ubuntu 18.04

Add PHP Repository

  • Update the repository cache.
  • Install the below packages.
  • Add the repository to your system.
  • Update the repository index.

Install PHP
Install PHP 8.0 on Ubuntu 20.04 / Ubuntu 18.04
Install PHP 7.x on Ubuntu 20.04 / Ubuntu 18.04
Verify PHP Version
PHP Support for Web Server
Both Apache and Nginx do not support PHP language by default when the browser requests the PHP page. So, we need to install the PHP module package to support PHP.

#ubuntu #php 8.0 #ubuntu 20.04 #ubuntu 18.04

Myriam  Rogahn

Myriam Rogahn

1599423060

How to Secure phpMyAdmin Access with Apache on Ubuntu 18.04

How to secure PHPMyAdmin login access in ubuntu apache on aws. Here, we will show you a simple 2 solution to secure PHPMyAdmin login in ubuntu apache on aws web server.

The first solution is to change the PHPMyAdmin login URL. And the second solution is add an extra security layer for access PHPMyAdmin login url in ubuntu 18.04 apache 2 on aws. And prevent the attacks.

Because by default, phpmyadmin login url is located on http:///phpmyadmin. So, The main reason of change phpmyadmin login url in ubuntu apache aws server to prevent attackers attack.

How to Secure phpMyAdmin with Apache 2 on Ubuntu 18.04

Now, you can see the following two solutions to secure PHPMyAdmin login access in ubuntu apache 2 on aws server.

Solution 1 – Change PhpMyAdmin Login Page URL in Apache 2 Ubuntu

In ubuntu, default phpmyadmin login url can be located at apache configuration that name apache.conf.

So, you can use sudo nano /etc/phpmyadmin/apache.conf command to open apache.conf file:

sudo nano /etc/phpmyadmin/apache.conf

Then, you can add the following line with your phpmyadmin url:

Alias /my-phpmyadmin /usr/share/phpmyadmin

Note that, you can replace my-phpmyadmin to your own word.

Now you need to restart apache 2 web server. So type the following command on your ssh terminal to restart apache service:

sudo service apache2 restart

Solution 2 – Secure PHPMyAdmin Access in ubuntu aws

Now, you can add extra security layer for access phpmyadmin login in ubuntu apache 2 on aws web server.

So, first of all, you need to create a password file with users using the htpasswd tool that comes with the Apache package. So open your ssh terminal and type the following command:

sudo htpasswd -c /etc/phpmyadmin/.htpasswd

Note that, You can choose any username instance of myAdmin with above command.

After that, one prompt box appear with password and confirm password. So, you can add password and confirm password here.

New password:
Re-type new password:
Adding password for user myAdmin

Now, you need to configure Apache 2 to password protect the phpMyAdmin directory and use the .htpasswd file.

So, open your ssh terminal and type the below command to open the phpmyadmin.conf file.

sudo nano /etc/apache2/conf-available/phpmyadmin.conf

Then add the following lines in phpmyadmin.conf file and save it:

Options  +FollowSymLinks +Multiviews +Indexes  ## edit this line
DirectoryIndex index.php

AllowOverride None
AuthType basic
AuthName "Authentication Required"
AuthUserFile /etc/phpmyadmin/.htpasswd
Require valid-user

Finally, restart apache web server by using the following command:

sudo service apache2 restart

#aws #mysql #ubuntu #how to change and secure default phpmyadmin login url ubuntu #how to change phpmyadmin login url ubuntu 18.04 #how to secure phpmyadmin access #how to secure phpmyadmin access with apache on ubuntu 18.04

Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

Daisy Rees

Daisy Rees

1572399888

How To Implement Pagination in MySQL with PHP on Ubuntu 18.04

Introduction

Pagination is the concept of constraining the number of returned rows in a recordset into separate, orderly pages to allow easy navigation between them, so when there is a large dataset you can configure your pagination to only return a specific number of rows on each page. For example, pagination can help to avoid overwhelming users when a web store contains thousands of products by reducing the number of items listed on a page, as it’s often unlikely a user will need to view every product. Another example is an application that shows records on a mobile device; enabling pagination in such a case would split records into multiple pages that can fit better on a screen.

Besides the visual benefits for end-users, pagination makes applications faster because it reduces the number of records that are returned at a time. This limits the data that needs to be transmitted between the client and the server, which helps preserve server resources such as RAM.

In this tutorial, you’ll build a PHP script to connect to your database and implement pagination to your script using the MySQL LIMIT clause.

Step 1 — Creating a Database User and a Test Database

In this tutorial you’ll create a PHP script that will connect to a MySQL database, fetch records, and display them in an HTML page within a table. You’ll test the PHP script in two different ways from your web browser. First, creating a script without any pagination code to see how the records are displayed. Second, adding page navigation code in the PHP file to understand how pagination works practically.

The PHP code requires a MySQL user for authentication purposes and a sample database to connect to. In this step you’ll create a non-root user for your MySQL database, a sample database, and a table to test the PHP script.

To begin log in to your server. Then log in to your MySQL server with the following command:

sudo mysql -u root -p

Enter the root password of your MySQL server and hit ENTER to continue. Then, you’ll see the MySQL prompt. To create a sample database, which we will call test_db in this tutorial, run the following command:

Create database test_db;

You will see the following output:

OutputQuery OK, 1 row affected (0.00 sec)

Then, create a test_user and grant the user all privileges to the test_db. Replace PASSWORD with a strong value:

GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';

OutputQuery OK, 1 row affected (0.00 sec)

Reload the MySQL privileges with:

FLUSH PRIVILEGES;

OutputQuery OK, 1 row affected (0.00 sec)

Next, switch to the test_db database to start working directly on the test_db database:

Use test_db;

OutputDatabase changed

Now create a products table. The table will hold your sample products—for this tutorial you’ll require only two columns for the data. The product_id column will serve as the primary key to uniquely identify each record. You’ll use the product_name field to differentiate each item by name:

Create table products (product_id BIGINT PRIMARY KEY, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;

OutputQuery OK, 0 rows affected (0.02 sec)

To add ten test products to the products table run the following SQL statements:

Insert into products(product_id, product_name) values ('1', 'WIRELESS MOUSE');
Insert into products(product_id, product_name) values ('2', 'BLUETOOTH SPEAKER');
Insert into products(product_id, product_name) values ('3', 'GAMING KEYBOARD');
Insert into products(product_id, product_name) values ('4', '320GB FAST SSD');
Insert into products(product_id, product_name) values ('5', '17 INCHES TFT');
Insert into products(product_id, product_name) values ('6', 'SPECIAL HEADPHONES');
Insert into products(product_id, product_name) values ('7', 'HD GRAPHIC CARD');
Insert into products(product_id, product_name) values ('8', '80MM THERMAL PRINTER');
Insert into products(product_id, product_name) values ('9', 'HDMI TO VGA CONVERTER');
Insert into products(product_id, product_name) values ('10', 'FINGERPRINT SCANNER');

You’ll see this output:

OutputQuery OK, 1 row affected (0.02 sec)

Verify that the products were inserted to the table by running:

select * from products;

You’ll see the products in your output within the two columns:

Output+------------+-----------------------+
| product_id | product_name          |
+------------+-----------------------+
|          1 | WIRELESS MOUSE        |
|          2 | BLUETOOTH SPEAKER     |
|          3 | GAMING KEYBOARD       |
|          4 | 320GB FAST SSD        |
|          5 | 17 INCHES TFT         |
|          6 | SPECIAL HEADPHONES    |
|          7 | HD GRAPHIC CARD       |
|          8 | 80MM THERMAL PRINTER  |
|          9 | HDMI TO VGA CONVERTER |
|         10 | FINGERPRINT SCANNER   |
+------------+-----------------------+
10 rows in set (0.00 sec)

Exit MySQL:

quit;

With the sample database, table, and test data in place, you can now create a PHP script to display data on a web page.

Step 2 — Displaying MySQL Records Without Pagination

Now you’ll create a PHP script that connects to the MySQL database that you created in the previous step and list the products in a web browser. In this step, your PHP code will run without any form of pagination to demonstrate how non-split records show on a single page. Although you only have ten records for testing purposes in this tutorial, seeing the records without pagination will demonstrate why segmenting data will ultimately create a better user experience and put less burden on the server.

Create the PHP script file in the document root of your website with the following command:

sudo nano /var/www/html/pagination_test.php

Then add the following content to the file. Remember to replace PASSWORD with the correct value of the password that you assigned to the test_user in the previous step:

<?php

try {

    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

    $sql="select * from products";

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

    $stmt->execute();

    echo "<table border='1' align='center'>";

    while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
        echo "<tr>";

        echo "<td>".$row['product_id']."</td>";

        echo "<td>".$row['product_name']."</td>";

        echo "</tr>";

    }

    echo "</table>";

}

  catch(PDOException $e)

{
    echo  $e->getMessage();
}

?>

Save the file by pressing CTRL+X, Y, and ENTER.

In this script you’re connecting to the MySQL database using the PDO (PHP Data Object) library with the database credentials that you created in Step 1.

PDO is a light-weight interface for connecting to databases. The data access layer is more portable and can work on different databases with just minor code rewrites. PDO has greater security since it supports prepared statements—a feature for making queries run faster in a secure way.

Then, you instruct the PDO API to execute the select * from products statement and list products in an HTML table without pagination. The line $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); ensures that the data types are returned as they appear in the database. This means that PDO will return the product_id as an integer and the product_name as a string. $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); instructs PDO to throw an exception if an error is encountered. For easier debugging you’re catching the error inside the PHP try{}...catch{} block.

To execute the /var/www/html/pagination_test.php PHP script file that you’ve created, visit the following URL replacing your-server-IP with the public IP address of your server:

http://your-server-IP/pagination_test.php

You’ll see a page with a table of your products.

MySQL Records Displayed with a PHP script - No Pagination

Your PHP script is working as expected; listing all products on one page. If you had thousands of products, this would result in a long loop as the products are fetched from the database and rendered on the PHP page.

To overcome this limitation, you will modify the PHP script and include the MySQL LIMIT clause and some navigation links at the bottom of the table to add pagination functionality.

Step 3 — Implementing Pagination with PHP

In this step your goal is to split the test data into multiple and manageable pages. This will not only enhance readability but also use the resources of the server more efficiently. You will modify the PHP script that you created in the previous step to accommodate pagination.

To do this, you’ll be implementing the MySQL LIMIT clause. Before adding this to the script, let’s see an example of the MySQL LIMIT syntax:

Select [column1, column2, column n...] from [table name] LIMIT offset, records;

The LIMIT clause takes two arguments as shown toward the end of this statement. The offset value is the number of records to skip before the first row. records sets the maximum number of records to display per page.

To test pagination, you’ll display three records per page. To get the total number of pages, you must divide the total records from your table with the rows that you want to display per page. You then round the resulting value to the nearest integer using PHP Ceil function as shown in the following PHP code snippet example:

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

Following is the modified version of the PHP script with the full pagination code. To include the pagination and navigation codes, open the /var/www/html/pagination_test.php file:

sudo nano /var/www/html/pagination_test.php

Then, add the following highlighted code to your file:

<?php

try {

    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

    /* Begin Paging Info */

    $page=1;

    if (isset($_GET['page'])) {
        $page=filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);
    }

    $per_page=3;

    $sqlcount="select count(*) as total_records from products";
    $stmt = $pdo->prepare($sqlcount);
    $stmt->execute();
    $row = $stmt->fetch();
    $total_records= $row['total_records'];

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

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

    /* End Paging Info */

    $sql="select * from products limit $offset,$per_page";

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

    $stmt->execute();

    echo "<table border='1' align='center'>";

    while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
        echo "<tr>";

        echo "<td>".$row['product_id']."</td>";

        echo "<td>".$row['product_name']."</td>";

        echo "</tr>";

    }

    echo "</table>";

    /* Begin Navigation */

    echo "<table border='1' align='center'>";

    echo "<tr>";

    if( $page-1>=1) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Previous</a></td>";
    }

    if( $page+1<=$total_pages) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Next</a></td>";
    }

    echo "</tr>";

    echo "</table>";

    /* End Navigation */

}

catch(PDOException $e) {
        echo  $e->getMessage();
}

?>

In your file you’ve used additional parameters to execute paging:

  • $page : This variable holds the current page in your script. When moving between the pages, your script retrieves a URL parameter named page using the $_GET['page'] variable.
  • $per_page: This variable holds the maximum records that you want to be displayed per page. In your case, you want to list three products on each page.
  • $total_records: Before you list the products, you’re executing a SQL statement to get a total count of records in your target table and assigning it to the $total_records variable.
  • $offset: This variable represents the total records to skip before the first row. This value is calculated dynamically by your PHP script using the formula $offset=($page-1)*$per_page. You may adapt this formula to your PHP pagination projects. Remember you can change the $per_page variable to suit your needs. For instance, you might change it to a value of 50 to display fifty items per page if you’re running a website or another amount for a mobile device.

Again, visit your IP address in a browser and replace your_server_ip with the public IP address of your server:

http://your_server_ip/pagination_test.php

You’ll now see some navigation buttons at the bottom of the page. On the first page, you will not get a Previous button. The same case happens on the last page where you will not get the Next page button. Also, note how the page URL parameter changes as you visit each page.

MySQL Records Displayed with a PHP script with Pagination - Page 1

MySQL Records Displayed with a PHP script with Pagination - Page 2

Final page of MySQL Records Displayed with a PHP script with Pagination - Page 4

The navigation links at the bottom of the page are achieved using the following PHP code snippet from your file:

. . .
    if( $page-1>=1) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Previous</a></td>";
    }

    if( $page+1<=$total_pages) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Next</a></td>";
    }
. . .

Here, the $page variable represents the current page number. Then, to get the previous page, the code will minus 1 from the variable. So, if you’re on page 2, the formula (2-1) will give you a result of 1 and this will be the previous page to appear in the link. However, keep in mind that it will only show the previous page if there is a result greater or equal to 1.

Similarly, to get to the next page, you add one to the $page variable and you must also make sure that the $page result that we append to the page URL parameter is not greater than the total pages that you’ve calculated in your PHP code.

At this point, your PHP script is working with pagination and you are able to implement the MySQL LIMIT clause for better record navigation.

Conclusion

In this tutorial, you implemented paging in MySQL with PHP on an Ubuntu 18.04 server. You can use these steps with a larger recordset using the PHP script to include pagination. By using pagination on your website or application you can create better user navigation and optimum resource utilization on your server.

#php #mysql #databases #ubuntu

Alycia  Klein

Alycia Klein

1596719640

How To Install Jenkins on Ubuntu 20.04 / Ubuntu 18.04

Jenkins is an open-source automation server that helps to automate the repetitive tasks involved in the software development process, which includes building, testing, and deployments.

Jenkins was forked from the Oracle Hudson project and written in Java.

Here, we will see how to install Jenkins on Ubuntu 20.04 / Ubuntu 18.04.

Install Jenkins On Ubuntu 20.04

Install Java

Since Jenkins is written in Java, it requires Java 8 or Java 11 to run. Here, I will install the OpenJDK 11 for Jenkins installation.

sudo apt update

sudo apt install -y default-jre apt-transport-https wget

If you want to use the Oracle Java in place of OpenJDK, then use any one of the links to install it.

READ: How To Install Oracle Java on Ubuntu 20.04

READ: How To Install Oracle Java on Ubuntu 18.04

Verify the Java version after the installation.

java -version

Output:

openjdk version "11.0.8" 2020-07-14
OpenJDK Runtime Environment (build 11.0.8+10-post-Ubuntu-0ubuntu120.04)
OpenJDK 64-Bit Server VM (build 11.0.8+10-post-Ubuntu-0ubuntu120.04, mixed mode, sharing)

Add Jenkins Repository

Jenkins provides an official repository for its packages. To use the Jenkins repository, first, we will need to add the Jenkins public key to the system.

wget -q -O - https://pkg.jenkins.io/debian-stable/jenkins.io.key | sudo apt-key add -

Then, add the Jenkins repository to your system.

echo "deb https://pkg.jenkins.io/debian-stable binary/" | sudo tee /etc/apt/sources.list.d/jenkins.list

Install Jenkins

Install Jenkins package using the apt command.

sudo apt update

sudo apt install -y jenkins

The Jenkins service should now be up and running. You can check the status of the Jenkins service using the below command.

sudo systemctl status jenkins

#ubuntu #jenkins #ubuntu 18.04 #ubuntu 20.04