Create a JSON REST API with PHP and MySQL On Ubuntu 20.04

Published on February 20, 2021•Updated on November 21, 2023

Introduction

An application programming interface (API) is a middleware that allows two applications to talk to each other through a representational state transfer (REST) channel. For instance, if you're developing a mobile application that uses a cloud database, you may create a PHP API to interface the Android client to a remote MySQL database. Another use-case of an API is to open your popular application to the end-users. Companies including Vultr, Google, Facebook, and Twitter have a public API. In this case, consumers accessing your application will use dedicated URL endpoints to submit and query data without going through your application's graphical user interface (GUI).

Creating an API for your application allows end-users to integrate your services flexibly, leading to a better user experience. Also, when your API endpoints return data in JavaScript object notation (JSON) format, it can be used by mobile, desktop applications, tablets, and embedded devices without changing the code in the backend. In this guide, you'll create an open JSON REST API for your hypothetical online store with PHP on Ubuntu 20.04. When you complete this guide, you should call your API endpoints to retrieve products from your database without going through a GUI.

Prerequisites

Before you begin following along with this tutorial, ensure you have the following:

An Ubuntu 20.04 server.
A sudo user.
A LAMP Stack. This guide works fine with either the MySQL or the MariaDB server.

Create a Sample Database

SSH to your server and run the command below to log in to the MySQL command-line interface (CLI).

$ sudo mysql -u root -p

Enter the root password of your MySQL server and press Enter to proceed. After you get the mysql> prompt, issue the command below to create a store_api database.

mysql> CREATE DATABASE store_api;

Next, create a non-root MySQL user. You will use this user's credentials to connect to the MySQL database in your PHP code.

mysql> CREATE USER 'api_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON store_api.* TO 'api_user'@'localhost';
mysql> FLUSH PRIVILEGES;

If you're using MariaDB instead of MySQL, use this command instead to create the user.

MariaDB> GRANT ALL PRIVILEGES on store_api.* TO 'api_user'@'localhost' identified by 'EXAMPLE_PASSWORD';

Switch to the newly created database.

mysql> USE store_api;

Next, you'll create a products table. This table lists the currently available items for purchase in your hypothetical store, including their prices. Without an API, end-users accessing your web applications must go through a User Interface such as a web application or desktop software to get the product list. However, later in this tutorial, you'll learn how to expose this table information through a PHP API.

Create the products table.

mysql> CREATE TABLE products (
       product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       product_name  VARCHAR(50),
       cost_price  DOUBLE,
       retail_price DOUBLE
       ) ENGINE = InnoDB;

Populate the products table with some records.

mysql> INSERT INTO products (product_name, cost_price, retail_price) VALUES ('LEATHER JACKET', '89.23', '99.95');
mysql> INSERT INTO products (product_name, cost_price, retail_price) VALUES ('SILVER COAT', '44.00', '60.00');
mysql> INSERT INTO products (product_name, cost_price, retail_price) VALUES ('REXI BELT', '14.49', '18.85');
mysql> INSERT INTO products (product_name, cost_price, retail_price) VALUES ('SUEDE SHOE', '24.00', '36.00');
mysql> INSERT INTO products (product_name, cost_price, retail_price) VALUES ('WOOLEN SWEATER', '14.45', '18.00');

Ensure the records are in place by running a SELECT query against the products table.

mysql> SELECT
       product_id,
       product_name,
       cost_price,
       retail_price
       FROM products;

MySQL should display the following list.

+------------+----------------+------------+--------------+
| product_id | product_name   | cost_price | retail_price |
+------------+----------------+------------+--------------+
|          1 | LEATHER JACKET |      89.23 |        99.95 |
|          2 | SILVER COAT    |         44 |           60 |
|          3 | REXI BELT      |      14.49 |        18.85 |
|          4 | SUEDE SHOE     |         24 |           36 |
|          5 | WOOLEN SWEATER |      14.45 |           18 |
+------------+----------------+------------+--------------+
5 rows in set (0.00 sec)

Exit from the MySQL command-line interface.

mysql> QUIT;

Configure Apache ModRewrite

Your API endpoints will rely heavily on Apache's re-write rules. You need to make some configuration changes to the Apache server to make this possible. First, enable the rewrite module.

$ sudo a2enmod rewrite

Then, open the /etc/apache2/apache2.conf configuration file using nano for editing.

$ sudo nano /etc/apache2/apache2.conf

Locate the <Directory>...</Directory> directive as shown below.

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

Then, change the line AllowOverride None to AllowOverride All.

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

The AllowOverride All directive allows .htaccess files to override the base Apache configurations. Restart Apache to reload the changes.

$ sudo systemctl restart apache2

Create a `.htaccess` File

First, create a working directory. In a real-world application, your API can have several endpoints. Therefore, it is a good idea to create a project directory to place all your PHP classes and other configuration files. In this case, you're using v1 (version 1) as the base directory's name.

When your API grows, and you need to version it, you can create other directories with the version number at the end of the file name(e.g. v2, v3, v4).

$ sudo mkdir -p /var/www/html/api/v1

Create a new .htaccess File in the /var/www/html/api/v1 directory.

$ sudo nano /var/www/html/api/v1/.htaccess

Enter the information below into the file.

RewriteEngine On
RewriteBase /api/v1

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d

RewriteRule (.*)$ index.php?request=$1 [QSA,NC,L]

Save and close the file.

The .htaccess file code explanation:

The first line, as shown below in the .htaccess files, enables Apache mod_rewrite module.
```
  RewriteEngine On
  ...
```
The second line specifies the URL that gets sent through the rewrite rules. In other words, the line below sets the rewrite base for the rewrite rules that you're specifying below it.
```
  ...
  RewriteBase /api/v1
  ...
```
The lines below tell Apache to continue processing the rules underneath if a file/directory with a specified name in the URL doesn't exist.
```
  ...
  RewriteCond %{REQUEST_FILENAME} !-f
  RewriteCond %{REQUEST_FILENAME} !-d
  ...
```
At the end of the .htaccess file, you've instructed apache to rewrite any requests routed to the /var/www/html/api/v1/ directory to an index.php file in the same directory. For instance, if a user requests the URL http://localhost/api/v1/products/1, the rewrite rule will redirect this to 'http://localhost/api/v1/index.php?request=products/1'
```
  ...
  RewriteRule (.*)$ index.php?request=$1 [QSA,NC,L]
  ...
```

Create a Base `index.php` File

Next create an index.php file in your /var/www/html/api/v1/ folder. This file will act as a router for all requests coming into your API.

Open the file using nano.

$ sudo nano /var/www/html/api/v1/index.php

Then, enter the information below into the file.

<?php

header("Content-type:application/json");

function load_class($class) {
    require_once $class . '.php';
}

spl_autoload_register('load_class');

$http_verb = $_SERVER['REQUEST_METHOD'];

if ($_SERVER['REQUEST_METHOD'] == 'GET') {
    foreach($_GET as $key => $value) {
        $params[$key] = $value;
    }
}

$request  = explode('/', $_REQUEST['request']);
$resource = $request[0];

if (isset($request[1])) {
    $resource_id = $request[1];
} else {
    $resource_id = '';
}

if ($resource == 'products') {
    $request = new Products;
}

if ($http_verb == 'GET') {

    if (!empty($resource_id)) {
        $response = $request->read($resource_id);
    } else {
        $response = $request->read($resource_id, $params);
    }

    echo $response;
}

Save and close the file when you're through with editing.

The .index file code explanation:

The first line is a PHP header that tells web clients to treat the data in JSON format.
```
  ...
  header("Content-type:application/json");
  ...
```
The following code allows you to automatically load PHP classes without declaring a require [FILENAME] statement. This is very useful if your API has many class files that you want to be loaded. Including the files manually might be a tedious task. So, this is where the code below comes into play.
```
  ...
  function load_class($class) {
      require_once $class . '.php';
  }

  spl_autoload_register('load_class');
  ...
```
The statement below retrieves the $_SERVER['REQUEST_METHOD'] and stores it in a $http_verb variable. Next, the code uses a PHP if (...) {...} statement to check if the user's requested method is GET. If this is true, all the GET variables are passed to the $params array to be processed later.
```
  ...
  $http_verb = $_SERVER['REQUEST_METHOD'];

  if ($_SERVER['REQUEST_METHOD'] == 'GET') {
      foreach($_GET as $key => $value) {
          $params[$key] = $value;
      }
  }
  ...
```
When a user requests your API endpoints(e.g. http://localhost/api/v1/products/1), the request is split into two variables. The first variable is the resource name, for example products, and the second variable is the resource_id, for example 1. If an API consumer requests the products resource a new class of type Products is instantiated, as shown below.
```
  ...
  $request  = explode('/', $_REQUEST['request']);
  $resource = $request[0];

  if (isset($request[1])) {
      $resource_id = $request[1];
  } else {
      $resource_id = '';
  }

  if ($resource == 'products') {
      $request = new Products;
  }
  ...
```
In the index.php file, you're using the PHP if(...){...} to check if the user is making a GET request against the resource. Then, you're sending this request to the products class and calling a read method.
If the user has specified the $resource_id, for example by calling the URL http://localhost/api/v1/products/1, you're simply sending that request to the products class without specifying any GET parameters.

However, if the API caller has not specified the resource_id, such as by calling http://localhost/api/v1/products), you're allowing an option where URL parameters can be specified to filter the records even further.

  ...
  if ($http_verb == 'GET') {

      if (!empty($resource_id)) {
          $response = $request->read($resource_id);
      } else {
          $response = $request->read($resource_id, $params);
      }

      echo $response;
  }
  ...

Create a `products.php` Class File

When you make calls to the products resource(e.g. http://localhost/api/v1/products or http://localhost/api/v1/products/1) you'll be routed to a class file named Products. So, create the file.

$ sudo nano /var/www/html/api/v1/Products.php

Enter the information below into the file.

<?php

class Products
{
    public function read($resource_id, $params = '')
    {
        try {

            $db_name     = 'store_api';
            $db_user     = 'api_user';
            $db_password = 'EXAMPLE_PASSWORD';
            $db_host     = 'localhost';

            $pdo = new PDO('mysql: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);

            $data = [];

            $sql  = "select *
                    from products
                    ";

            if (!empty($resource_id)) {

                $sql .= " where product_id = :product_id";
                $data['product_id'] = $resource_id;

            } else {

                $filter = '';

                if (isset($params['product_name']) ) {
                    $filter .=" and product_name = :product_name";
                    $data['product_name'] = $params['product_name'];
                }

                $sql .= " where product_id > 0 $filter";
            }

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

            $products = [];

            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $products[] = $row;
            }

            $response = [];

            $response['data'] =  $products;

            if (!empty($resource_id)) {
               $response['data'] = array_shift($response['data']);
            }

           return json_encode($response, JSON_PRETTY_PRINT);

    } catch (PDOException $ex) {
            $error = [];
            $error['message'] = $ex->getMessage();

            return $error;
    }
    }
}

Save and close the file when you're through with editing.

The /var/www/html/api/v1/Products.php Explanation:

First, you're creating a new Products class that you call in the index.php file wherever there is a new products resource request.
```
  <?php

  class Products
  {
  ...
  }
```
Then, you're creating a new PHP read function inside the class. When a function is created in a class, it is referred to as a method. Up to this point, you may call this Products class by creating a new object with the class name(e.g. new Products. Remember this is what you've done in the .index.php file.
```
  <?php

  class Products
  {
      public function read($resource_id, $params = '')
      {
      ...
      }
  }
```

The lines below specify the credentials for the database that you created earlier in this tutorial. You're then using the PDO to connect to the database.

  ...
  $db_name     = 'store_api';
  $db_user     = 'api_user';
  $db_password = 'EXAMPLE_PASSWORD';
  $db_host     = 'localhost';

  $pdo = new PDO('mysql: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);
  ...

In the below code, you're initializing an empty data array. You will use this array as a placeholder for named parameters when sending the request to the database using PDO to avoid any chances of SQL injection. Next, you're constructing an SQL statement that fetches data from the products table.

If the $resource_id is specified when the read method is executed from the index.php file, you're simply querying a single product. Otherwise, an SQL for retrieving all records with some filter parameters is executed as shown below.

  ...
  $data = [];

  $sql  = "select *
          from products
          ";

  if (!empty($resource_id)) {

      $sql .= " where product_id = :product_id";
      $data['product_id'] = $resource_id;

  } else {

      $filter = '';

  if (isset($params['product_name']) ) {
      $filter .=" and product_name = :product_name";
      $data['product_name'] = $params['product_name'];
  }

      $sql .= " where product_id > 0 $filter";
  }
  ...

Finally, you're executing the SQL statement and returning the data as a JSON object. Otherwise, you're raising a JSON error. ...

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

  $products = [];

  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $products[] = $row;
  }

  $response = [];

  $response['data'] =  $products;

  if (!empty($resource_id)) {
      $response['data'] = array_shift($response['data']);
  }

  return json_encode($response, JSON_PRETTY_PRINT);

  } catch (PDOException $ex) {
      $error = [];
      $error['message'] = $ex->getMessage();

      return $error;
  ...

Test the API

The final step is testing the codes that you've developed. Use the Linux curl command to execute the API endpoint below.

$ curl localhost/api/v1/products

You should get an output displaying all products in JSON format.

{
    "data": [
        {
            "product_id": 1,
            "product_name": "LEATHER JACKET",
            "cost_price": 89.23,
            "retail_price": 99.95
        },
        {
            "product_id": 2,
            "product_name": "SILVER COAT",
            "cost_price": 44,
            "retail_price": 60
        },
        {
            "product_id": 3,
            "product_name": "REXI BELT",
            "cost_price": 14.49,
            "retail_price": 18.85
        },
        {
            "product_id": 4,
            "product_name": "SUEDE SHOE",
            "cost_price": 24,
            "retail_price": 36
        },
        {
            "product_id": 5,
            "product_name": "WOOLEN SWEATER",
            "cost_price": 14.45,
            "retail_price": 18
        }
    ]
}

To retrieve a single product, specify a resource id at the end of the URL as shown below.

Product Id 1:

  $ curl localhost/api/v1/products/1

Output.

  {
      "data": {
          "product_id": 1,
          "product_name": "LEATHER JACKET",
          "cost_price": 89.23,
          "retail_price": 99.95
      }
  }

Product Id 2:

  $ curl localhost/api/v1/products/2

Output.

  {
      "data": {
          "product_id": 2,
          "product_name": "SILVER COAT",
          "cost_price": 44,
          "retail_price": 60
      }
  }

To filter the products by the product_name field, such as to view only the SUEDE SHOE, run the command below.

  $ curl localhost/api/v1/products?product_name=SUEDE%20SHOE

Output.

  {
      "data": [
          {
              "product_id": 4,
              "product_name": "SUEDE SHOE",
              "cost_price": 24,
              "retail_price": 36
         }
      ]
  }

As you can confirm from the JSON responses above, your PHP API is working as expected.

Conclusion

You've implemented a PHP API that outputs data in JSON format with MySQL and Ubuntu 20.04 server in this guide. You may tweak the code in this tutorial to suit your needs. Remember, you may extend your API to accept other HTTP verbs/methods like POST, PUT and DELETE.

Create a JSON REST API with PHP and MySQL On Ubuntu 20.04

Introduction

Prerequisites

Create a Sample Database

Configure Apache ModRewrite

Create a `.htaccess` File

Create a Base `index.php` File

Create a `products.php` Class File

Test the API

Conclusion

Products

Features

Marketplace

Resources

Company

Create a JSON REST API with PHP and MySQL On Ubuntu 20.04

Introduction

Prerequisites

Create a Sample Database

Configure Apache ModRewrite

Create a .htaccess File

Create a Base index.php File

Create a products.php Class File

Test the API

Conclusion

Create a `.htaccess` File

Create a Base `index.php` File

Create a `products.php` Class File