You want the setup CakePHP Application with LAMP on Ubuntu?

You want the setup CakePHP Application with LAMP on Ubuntu?

Learn how to install CakePHP on Ubuntu

Introduction

CakePHP is a popular and feature-rich PHP web framework. It solves many of the common problems in web development, such as interacting with a database, shielding against SQL injections, and generating view code. It adheres to the model-view-controller (MVC) pattern, which decouples various parts of the application, effectively allowing developers to work on different parts of the app in parallel. It also provides built-in security and authentication. To create a basic database app is a seamless process, which makes CakePHP useful for prototyping. However, you can use CakePHP to create fully developed web applications for deployment as well.

In this tutorial, you will deploy an example CakePHP web application to a production environment. To achieve this, you'll set up an example database and user, configure Apache, connect your app to the database, and turn off debug mode. You'll also use CakePHP's bake command to automatically generate article models.

Prerequisites

Before you begin this tutorial, you will need:

  • A server running Ubuntu 18.04 with root access and a sudo, non-root account, you can set this up by following this initial server setup guide.
  • A LAMP stack installed according to How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu 18.04. At the time of this writing, PHP 7.2 is the latest version.
  • Composer (a PHP package manager) installed on your server. For a guide on how to do that, visit How To Install and Use Composer on Ubuntu 18.04. You only need to complete the first two steps from that tutorial.
  • Apache secured with Let's Encrypt. To complete this prerequisite, you'll first need to set up virtual hosts following Step 5 of How To Install Apache on Ubuntu 18.04. You can then follow How To Secure Apache with Let's Encrypt on Ubuntu 18.04 to secure Apache with Let's Encrypt. When asked, enable mandatory HTTPS redirection.
  • A fully registered domain name. This tutorial will use example.com throughout. You can purchase a domain name on Namecheap, get one for free on Freenom, or use the domain registrar of your choice.
  • Both of the following DNS records set up for your server. You can follow this introduction to DigitalOcean DNS for details on how to add them.
  • An A record with example.com pointing to your server's public IP address.
  • An A record with www.example.com pointing to your server's public IP address.
Step 1 — Installing Dependencies

To prepare for your application, you'll begin by installing the PHP extensions that CakePHP needs.

Start off by updating the package manager cache:

sudo apt update

CakePHP requires the mbstringintl, and simplexml PHP extensions, which add support for multibyte strings, internationalization, and XML processing. You have installed mbstring as part of the Composer prerequisite tutorial. You can install the remaining libraries with one command:

sudo apt install php7.2-intl php7.2-xml -y

Remember that the version numbers above (7.2) will change with new versions of PHP.

You installed the required dependencies for CakePHP. You're now ready to configure your MySQL database for production use.

Step 2 — Setting Up a MySQL Database

Now, you'll create a MySQL database to store information about your blog's articles. You'll also create a database user that your application will use to access the database. You'll modify the database privileges to achieve this separation of control. As a result, bad actors won't be able to cause issues on the system even with database credentials, which is an important security precaution in a production environment.

Launch your MySQL shell:

sudo mysql -u root -p

When asked, enter the password you set up during the initial LAMP installation.

Next, create a database:

CREATE DATABASE cakephp_blog;

You will see output similar to:

Output
Query OK, 1 row affected (0.00 sec)

Your CakePHP app will use this new database to read and store production data.

Then, instruct MySQL to operate on the new cakephp_blog database:

USE cakephp_blog;

You will see output similar to:

Output
Database changed

Now you'll create a table schema for your blog articles in the cakephp_blog database. Run the following command to set this up:

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(50),

body TEXT,

created DATETIME DEFAULT NULL,

modified DATETIME DEFAULT NULL
);

You've created a schema with five fields to describe blog articles:

  • id: is the unique identifier of an article, set up as a primary key.
  • title: is the title of an article, declared as a text field containing a maximum of 50 characters.
  • body: is the text of the article, declared as TEXT field.
  • created: is the date and time of a record's creation.
  • modified: is the date and time of a record's modification.

The output will be similar to:

Output
Query OK, 0 rows affected (0.01 sec)

You have created a table for storing articles in the cakephp_blog database. Next, populate it with example articles by running the following command:

INSERT INTO articles (title, body, created)
VALUES ('Sample title', 'This is the article body.', NOW());

You've added an example article with some sample data for the title and body text.

You will see the following output:

Output
Query OK, 0 rows affected (0.01 sec)

In order to connect the CakePHP app to the database, you need to create a new database user and restrict its privileges:

GRANT ALL PRIVILEGES ON cakephp_blog.* TO 'cake_user'@'localhost' IDENTIFIED BY 'password';

This command grants all privileges to all the tables in the database.

Remember to replace password with a strong password of your choice.

To update your database with the changes you've made, reload by running:

FLUSH PRIVILEGES;

You've just created a new database user, cake_user and given the user privileges only on the cakephp_blog database, thus tightening security.

Exit the MySQL terminal by entering exit.

You've created a new database with a schema, populated it with example data, and created an appropriate database user. In the next step, you will set up the CakePHP app itself.

Step 3 — Creating the Blog Application

In this section, you'll use Composer to install an example CakePHP app. It is advantageous to use Composer as it allows you to install CakePHP from your command line and it automatically sets up certain file permissions and configuration files.

First, navigate to the Apache web server folder:

cd /var/www/example.com/html

Apache uses this directory to store files visible to the outside world. The root user owns this directory, and so your non-root user, sammy, can't write anything to it. To correct this, you'll change the file system permissions by running:

sudo chown -R sammy .

You'll now create a new CakePHP app via Composer:

composer create-project --prefer-dist cakephp/app cake-blog

Here you have invoked composer and instructed it to create a new project with create-project--prefer-dist cakephp/app tells composer to use CakePHP as a template with cake-blog as the name of the new application.

Keep in mind that this command may take some time to finish.

When Composer asks you to set up folder permissions, answer with y.

In this section, you created a new CakePHP project with Composer. In the next step, you will configure Apache to point to the new app, which will make it viewable in your browser.

Step 4 — Configuring Apache to Point to Your App

Now, you'll configure Apache for your new CakePHP application, as well as enable .htaccessoverriding, which is a CakePHP requirement. This entails editing Apache configuration files.

For actual routing to take place, you must instruct Apache to use .htaccess files. These are configuration files that will be in subdirectories of the application (where needed), and then Apache uses the files to alter its global configuration for the requested part of the app. Among other tasks, they will contain URL rewriting rules, which you'll be adjusting now.

Start off by opening the Apache global configuration file (apache2.conf) using your text editor:

sudo nano /etc/apache2/apache2.conf

Find the following block of code:

/etc/apache2/apache2.conf


...
<Directory /var/www/>
Options Indexes FollowSymLinks
AllowOverride None
Require all granted
</Directory>
...

Change AllowOverride from None to All, like the following:

/etc/apache2/apache2.conf


...
<Directory /var/www/>
Options Indexes FollowSymLinks
AllowOverride All
Require all granted
</Directory>
...

Save and close the file.

Next, you will instruct Apache to point to the webroot directory in the CakePHP installation. Apache stores its configuration files on Ubuntu 18.04 in /etc/apache2/sites-available. These files govern how Apache processes web requests.

During the Let's Encrypt prerequisite tutorial, you enabled HTTPS redirection; therefore only allowing HTTPS traffic. As a result, you'll only edit the example.com-le-ssl.conf file, which configures HTTPS traffic.

First, open the example.com-le-ssl.conf configuration file:

sudo nano /etc/apache2/sites-available/example.com-le-ssl.conf

You need to change only one line, the one that sets up DocumentRoot and tells Apache from where to serve content to the browser. Find the following line in the file:

/etc/apache2/sites-available/example.com-le-ssl.conf

DocumentRoot /var/www/example.com/html

Edit this line to point to the CakePHP installation, by adding the following highlighted content:

/etc/apache2/sites-available/example.com-le-ssl.conf

DocumentRoot /var/www/example.com/html/cake-blog/webroot

Save the file and exit the editor.

Afterwards, restart Apache to reflect the new configuration:

sudo systemctl restart apache2

Now you can visit https://your_domain/ in your browser.

You'll see the default CakePHP success page. You'll notice that there is a block indicating that your application can't connect to the database. In the next step you'll resolve this by connecting your app to the database.

You've now enabled .htaccess overriding, and pointed Apache to the correct webroot directory.

Step 5 — Connecting Your App to the Database

In this section, you will connect your database to your application so that your blog can access the articles. You'll edit CakePHP's default config/app.php file to set up the connection to your database.

Navigate to the app folder:

cd /var/www/example.com/html/cake-blog

Open the config/app.php file, by running the following command:

sudo nano config/app.php

Find the Datasources block (it looks like the following):

/var/www/example.com/html/cake-blog/config/app.php
...
'Datasources' => [
'default' => [
'className' => 'Cake\Database\Connection',
'driver' => 'Cake\Database\Driver\Mysql',
'persistent' => false,
'host' => 'localhost',
...
//'port' => 'non_standard_port_number',
'username' => 'cake_user',
'password' => 'password',
'database' => 'cakephp_blog',
...

For 'username' replace my_app with your database user's username (this tutorial uses: cake_user), secret with your database user's password, and the second my_app with the database name (cakephp_blog in this tutorial).

Save and close the file.

Refresh the app in your browser and observe the success message under the Database section. If it shows an error, double check your configuration file against the preceding steps.

In this step, you've connected the CakePHP app to your MySQL database. In the next step, you'll generate the model, view, and controller files that will make up the user interface for interacting with the articles.

Step 6 — Creating the Article User Interface

In this section, you'll create a ready-to-use article interface by running the CakePHP bakecommand, which generates the article model. In CakePHP, baking generates all required models, views, and controllers in a basic state, ready for further development. Every database app must allow for create, read, update, and delete (CRUD) operations, which makes CakePHP's bakefeature useful for automatically generating code for these operations. Within a couple of minutes, you get a full prototype of the app, ready to enter, store, and edit the data.

Models, views, and controllers pertain to the MVC pattern. Their roles are:

  • Models represent the data structure.
  • Views present the data in a user-friendly way.
  • Controllers act upon user requests and serve as an intermediary between views and models.

CakePHP stores its CLI executable under bin/cake. While it is mostly used for baking, it offers a slew of other commands, such as the ones for clearing various caches.

The bake command will check your database, and generate the models based on the table definitions it finds. Start off by running the following command:

./bin/cake bake all

By passing the all command, you are instructing CakePHP to generate models, controllers, and views all at once.

Your output will look like this:

Output
Bake All

Possible model names based on your database:

  • articles
    Run cake bake all [name] to generate skeleton files.

It has properly detected the articles definition from your database, and is offering to generate files for that model.

Bake it by running:

./bin/cake bake all articles

Your output will look like this:

Output
Bake All

One moment while associations are detected.

Baking table class for Articles...

Creating file /var/www/example.com/html/cake-blog/src/Model/Table/ArticlesTable.php
Wrote /var/www/example.com/html/cake-blog/src/Model/Table/ArticlesTable.php
Deleted /var/www/example.com/html/cake-blog/src/Model/Table/empty

Baking entity class for Article...

Creating file /var/www/example.com/html/cake-blog/src/Model/Entity/Article.php
Wrote /var/www/example.com/html/cake-blog/src/Model/Entity/Article.php
Deleted /var/www/example.com/html/cake-blog/src/Model/Entity/empty

Baking test fixture for Articles...

Creating file /var/www/example.com/html/cake-blog/tests/Fixture/ArticlesFixture.php
Wrote /var/www/example.com/html/cake-blog/tests/Fixture/ArticlesFixture.php
Deleted /var/www/example.com/html/cake-blog/tests/Fixture/empty
Bake is detecting possible fixtures...

Baking test case for App\Model\Table\ArticlesTable ...

Creating file /var/www/example.com/html/cake-blog/tests/TestCase/Model/Table/ArticlesTableTest.php
Wrote /var/www/example.com/html/cake-blog/tests/TestCase/Model/Table/ArticlesTableTest.php

Baking controller class for Articles...

Creating file /var/www/example.com/html/cake-blog/src/Controller/ArticlesController.php
Wrote /var/www/example.com/html/cake-blog/src/Controller/ArticlesController.php
Bake is detecting possible fixtures...

...

Baking add view template file...

Creating file /var/www/example.com/html/cake-blog/src/Template/Articles/add.ctp
Wrote /var/www/example.com/html/cake-blog/src/Template/Articles/add.ctp

Baking edit view template file...

Creating file /var/www/example.com/html/cake-blog/src/Template/Articles/edit.ctp
Wrote /var/www/example.com/html/cake-blog/src/Template/Articles/edit.ctp
Bake All complete.

In the output, you will see that CakePHP has logged all the steps it took to create a functional boilerplate for the articles database.

Now, navigate to the following in your browser:

https://your_domain/articles

You'll see a list of articles currently in the database, which includes one row titled Sample Title. The bake command created this interface allowing you to create, delete, and edit articles. As such, it provides a solid starting point for further development. You can try adding a new article by clicking the New Article link in the sidebar.

In this section, you generated model, view, and controller files with CakePHP's bake command. You can now create, delete, view, and edit your articles, with all your changes immediately saved to the database.

In the next step, you will disable the debug mode.

Step 7 — Disabling Debug Mode in CakePHP

In this section, you will disable the debug mode in CakePHP. This is crucial because in debug mode the app shows detailed debugging information, which is a security risk. You'll complete this step after you've completed the development of your application.

Open the config/app.php file using your favorite editor:

sudo nano config/app.php

Near the start of the file there will be a line for the 'debug' mode. When you open the file 'debug'mode will be set to true. Change this to false as per the following:

config/app.php
...
'debug' => filter_var(env('DEBUG', false), FILTER_VALIDATE_BOOLEAN),
...

Once you've turned debug mode off, the home page, located under src/Templates/Pages/home.ctp, will show an error.

Note: If you haven't changed the default route or replaced the contents of home.ctp, the home page of your app will now show an error. This is because the default home page serves as a status dashboard during development, but does not work with debug mode disabled.

You've disabled debug mode. Any errors and exceptions that occur from now, along with their stack traces, won't be shown to the end user, tightening the security of your application.

However, after, disabling debug mode, your home.ctp will show an error. If you've completed this step only for the purposes of this tutorial, you can now redirect your home page to the articles listing interface while keeping debug mode disabled. You'll achieve this by editing the contents of home.ctp.

Open home.ctp for editing:

sudo nano src/Template/Pages/home.ctp

Replace its contents with the following:

src/Template/Pages/home.ctp

<meta http-equiv="refresh" content="0; url=./Articles" />
<p><a href="./Articles">Click here if you are not redirected</a></p>

This HTML redirects to the Articles controller. If the automatic redirection fails, there is also a link for users to follow.

In this step, you disabled debug mode for security purposes and fixed the home page's error by redirecting the user to the blog post listing interface that the Articles controller provides.

Conclusion

You have now successfully set up a CakePHP application on a LAMP stack on Ubuntu 18.04. With CakePHP, you can create a database with as many tables as you like, and it will produce a live web editor for the data.


How to Install PHP on Ubuntu 18.04

PHP is one of the most popular programming language currently in the world. PHP has different versions 5.6, 7.0, 7.1 and 7.2. Ubuntu 18.04 ships with default PHP 7.2 support, We can easily install PHP 7.2 on Ubuntu. Ubuntu 18.04 is the current latest version of Ubuntu. This tutorial outlines how to install PHP 7.2 on Ubuntu 18.04.

Install PHP on Ubuntu 18.04

PHP is one of the most popular programming language currently in the world. PHP has different versions 5.6, 7.0, 7.1 and 7.2. Ubuntu 18.04 ships with default PHP 7.2 support, We can easily install PHP 7.2 on Ubuntu. Ubuntu 18.04 is the current latest version of Ubuntu. This tutorial outlines how to install PHP 7.2 on Ubuntu 18.04.


Prerequisites

Before you start installing PHP on Ubuntu 18.04. You must have a non-root user account on your server with sudo privileges.


How to Install PHP 7.0 on Ubuntu 18.04

Update the apt package manager index typing following command:

sudo apt update

Run the following command to install PHP on your server. The following command will install php7.2.

sudo apt install php7.0

Check PHP version and confirm the installation by running following command

php -v

Following are some basic PHP extensions needs to be installed on your server

sudo apt install php7.0-curl php7.0-mysql php7.0-common php7.0-cli php7.0-gd php7.0-opcache

How to Install PHP 7.2 on Ubuntu 18.04

Update the apt package manager index typing following command:

sudo apt update

Run the following command to install PHP on your server. The following command will install php7.2.

sudo apt install php7.2

Check PHP version and confirm the installation by running following command


php -v

Following are some basic PHP extensions needs to be installed on your server

sudo apt install php7.2-curl php7.2-mysql php7.2-common php7.2-cli php7.2-gd php7.2-opcache

Conclusion

In this tutorial, you have learned how to install PHP on Ubuntu 18.04 successfully with some of its basic extensions useful for frameworks and tested successfully. If you have any of the queries regarding this then you can comment below.

How To Implement Pagination in MySQL with PHP on Ubuntu 18.04

How To Implement Pagination in MySQL with PHP on Ubuntu 18.04

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.

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.

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.

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.

How To Install Linux, Nginx, MySQL, PHP (LEMP stack) on Ubuntu 18.04?

How To Install Linux, Nginx, MySQL, PHP (LEMP stack) on Ubuntu 18.04?

This guide demonstrates how to install a LEMP stack on an Ubuntu 18.04 server. The Ubuntu operating system takes care of the first requirement. We will describe how to get the rest of the components up and running.

Introduction

The LEMP software stack is a group of software that can be used to serve dynamic web pages and web applications. This is an acronym that describes a Linux operating system, with an Nginx (pronounced like “Engine-X”) web server. The backend data is stored in the MySQL database and the dynamic processing is handled by PHP.

This guide demonstrates how to install a LEMP stack on an Ubuntu 18.04 server. The Ubuntu operating system takes care of the first requirement. We will describe how to get the rest of the components up and running.

Step 1 – Installing the Nginx Web Server

In order to display web pages to our site visitors, we are going to employ Nginx, a modern, efficient web server.

All of the software used in this procedure will come from Ubuntu’s default package repositories. This means we can use the apt package management suite to complete the necessary installations.

Since this is our first time using apt for this session, start off by updating your server’s package index. Following that, install the server:

sudo apt update
sudo apt install nginx

On Ubuntu 18.04, Nginx is configured to start running upon installation.

If you have the ufw firewall running, as outlined in the initial setup guide, you will need to allow connections to Nginx. Nginx registers itself with ufw upon installation, so the procedure is rather straightforward.

It is recommended that you enable the most restrictive profile that will still allow the traffic you want. Since you haven’t configured SSL for your server in this guide, you will only need to allow traffic on port 80.

Enable this by typing:

sudo ufw allow 'Nginx HTTP'

You can verify the change by running:

sudo ufw status

This command’s output will show that HTTP traffic is allowed:

OutputStatus: active

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
Nginx HTTP                 ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)
Nginx HTTP (v6)            ALLOW       Anywhere (v6)

With the new firewall rule added, you can test if the server is up and running by accessing your server’s domain name or public IP address in your web browser.

If you do not have a domain name pointed at your server and you do not know your server’s public IP address, you can find it by running the following command:

ip addr show eth0 | grep inet | awk '{ print $2; }' | sed 's/\/.*$//'

This will print out a few IP addresses. You can try each of them in turn in your web browser.

As an alternative, you can check which IP address is accessible, as viewed from other locations on the internet:

curl -4 icanhazip.com

Type the address that you receive in your web browser and it will take you to Nginx’s default landing page:

http://server_domain_or_IP

If you see the above page, you have successfully installed Nginx.

Step 2 – Installing MySQL to Manage Site Data

Now that you have a web server, you need to install MySQL (a database management system) to store and manage the data for your site.

Install MySQL by typing:

sudo apt install mysql-server

The MySQL database software is now installed, but its configuration is not yet complete.

To secure the installation, MySQL comes with a script that will ask whether we want to modify some insecure defaults. Initiate the script by typing:

sudo mysql_secure_installation

This script will ask if you want to configure the VALIDATE PASSWORD PLUGIN.

Warning: Enabling this feature is something of a judgment call. If enabled, passwords which don’t match the specified criteria will be rejected by MySQL with an error. This will cause issues if you use a weak password in conjunction with software which automatically configures MySQL user credentials, such as the Ubuntu packages for phpMyAdmin. It is safe to leave validation disabled, but you should always use strong, unique passwords for database credentials.

Answer Y for yes, or anything else to continue without enabling.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:

If you’ve enabled validation, the script will also ask you to select a level of password validation. Keep in mind that if you enter 2 – for the strongest level – you will receive errors when attempting to set any password which does not contain numbers, upper and lowercase letters, and special characters, or which is based on common dictionary words.

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1

Next, you’ll be asked to submit and confirm a root password:

Please set the password for root here.

New password:

Re-enter new password:

For the rest of the questions, you should press Y and hit the ENTER key at each prompt. This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so that MySQL immediately respects the changes we have made.

Note that in Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) to access the user.

If using the auth_socket plugin to access MySQL fits with your workflow, you can proceed to Step 3. If, however, you prefer to use a password when connecting to MySQL as root, you will need to switch its authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal:

sudo mysql

Next, check which authentication method each of your MySQL user accounts use with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

Output+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

In this example, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change password to a strong password of your choosing:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:

FLUSH PRIVILEGES;

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

SELECT user,authentication_string,plugin,host FROM mysql.user;

Output+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

You can see in this example output that the root MySQL user now authenticates using a password. Once you confirm this on your own server, you can exit the MySQL shell:

exit

Note: After configuring your root MySQL user to authenticate with a password, you’ll no longer be able to access MySQL with the sudo mysql command used previously. Instead, you must run the following:

mysql -u root -p

After entering the password you just set, you will see the MySQL prompt.

At this point, your database system is now set up and you can move on to installing PHP.

Step 3 – Installing PHP and Configuring Nginx to Use the PHP Processor

You now have Nginx installed to serve your pages and MySQL installed to store and manage your data. However, you still don’t have anything that can generate dynamic content. This is where PHP comes into play.

Since Nginx does not contain native PHP processing like some other web servers, you will need to install php-fpm, which stands for “fastCGI process manager”. We will tell Nginx to pass PHP requests to this software for processing.

Note: Depending on your cloud provider, you may need to add Ubuntu’s universe repository, which includes free and open-source software maintained by the Ubuntu community, before installing the php-fpm package. You can do this by typing:

sudo add-apt-repository universe

Install the php-fpm module along with an additional helper package, php-mysql, which will allow PHP to communicate with your database backend. The installation will pull in the necessary PHP core files. Do this by typing:

sudo apt install php-fpm php-mysql

You now have all of the required LEMP stack components installed, but you still need to make a few configuration changes in order to tell Nginx to use the PHP processor for dynamic content.

This is done on the server block level (server blocks are similar to Apache’s virtual hosts). To do this, open a new server block configuration file within the /etc/nginx/sites-available/ directory. In this example, the new server block configuration file is named example.com, although you can name yours whatever you’d like:

sudo nano /etc/nginx/sites-available/example.com

By editing a new server block configuration file, rather than editing the default one, you’ll be able to easily restore the default configuration if you ever need to.

Add the following content, which was taken and slightly modified from the default server block configuration file, to your new server block configuration file:

server {
        listen 80;
        root /var/www/html;
        index index.php index.html index.htm index.nginx-debian.html;
        server_name example.com;

        location / {
                try_files $uri $uri/ =404;
        }

        location ~ \.php$ {
                include snippets/fastcgi-php.conf;
                fastcgi_pass unix:/var/run/php/php7.2-fpm.sock;
        }

        location ~ /\.ht {
                deny all;
        }
}

Here’s what each of these directives and location blocks do:

  • listen — Defines what port Nginx will listen on. In this case, it will listen on port 80, the default port for HTTP.
  • root — Defines the document root where the files served by the website are stored.
  • index — Configures Nginx to prioritize serving files named index.php when an index file is requested, if they’re available.
  • server_name — Defines which server block should be used for a given request to your server. Point this directive to your server’s domain name or public IP address.
  • location / — The first location block includes a try_files directive, which checks for the existence of files matching a URI request. If Nginx cannot find the appropriate file, it will return a 404 error.
  • location ~ \.php$ — This location block handles the actual PHP processing by pointing Nginx to the fastcgi-php.conf configuration file and the php7.2-fpm.sock file, which declares what socket is associated with php-fpm.
  • location ~ /\.ht — The last location block deals with .htaccess files, which Nginx does not process. By adding the deny all directive, if any .htaccess files happen to find their way into the document root they will not be served to visitors.

After adding this content, save and close the file. Enable your new server block by creating a symbolic link from your new server block configuration file (in the /etc/nginx/sites-available/ directory) to the /etc/nginx/sites-enabled/ directory:

sudo ln -s /etc/nginx/sites-available/example.com /etc/nginx/sites-enabled/

Then, unlink the default configuration file from the /sites-enabled/ directory:

sudo unlink /etc/nginx/sites-enabled/default

Note: If you ever need to restore the default configuration, you can do so by recreating the symbolic link, like this:

sudo ln -s /etc/nginx/sites-available/default /etc/nginx/sites-enabled/

Test your new configuration file for syntax errors by typing:

sudo nginx -t

If any errors are reported, go back and recheck your file before continuing.

When you are ready, reload Nginx to make the necessary changes:

sudo systemctl reload nginx

This concludes the installation and configuration of your LEMP stack. However, it’s prudent to confirm that all of the components can communicate with one another.

Step 4 – Creating a PHP File to Test Configuration

Your LEMP stack should now be completely set up. You can test it to validate that Nginx can correctly hand .php files off to the PHP processor.

To do this, use your text editor to create a test PHP file called info.php in your document root:

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

Enter the following lines into the new file. This is valid PHP code that will return information about your server:

<?php
phpinfo();

When you are finished, save and close the file.

Now, you can visit this page in your web browser by visiting your server’s domain name or public IP address followed by /info.php:

http://your_server_domain_or_IP/info.php

You should see a web page that has been generated by PHP with information about your server:

If you see a page that looks like this, you’ve set up PHP processing with Nginx successfully.

After verifying that Nginx renders the page correctly, it’s best to remove the file you created as it can actually give unauthorized users some hints about your configuration that may help them try to break in. You can always regenerate this file if you need it later.

For now, remove the file by typing:

sudo rm /var/www/html/info.php

With that, you now have a fully-configured and functioning LEMP stack on your Ubuntu 18.04 server.

Conclusion

A LEMP stack is a powerful platform that will allow you to set up and serve nearly any website or application from your server.