Posted in PHP

Caching REST API Response with PHP and MySQL

   Updated on June 16, 2023  1 Comment

While integrating third-party REST API, you may have heard about the term Rate Limit. Usually, vendors allow a certain number of requests within a specific interval. If this limit is exceeded, you start getting an error of “Rate Limit is exceeded” or something like that. This is because to keep the system fast, the vendor has to force this limit per user. Or else, people can send numerous requests which ultimately make their service tremendously slow for others.

To avoid such issues and keep service reliable to all, vendors always recommend caching the response received from the REST API. Caching REST API response benefits the quality of service in the following way.

  • Reduce loads on servers
  • Minimize network requests to the external server
  • Reduce bandwidth, latency

There are several ways of caching the results. Redis, Memcached, and Server disk space can be used for caching purposes. However, in this tutorial, I show you how to cache REST API response with PHP and MySQL. Maybe in the future, I’ll also write about these other caching options.

Caching in REST API

If you cache the request and response, you can fetch the result without hitting the REST API endpoint. Of course, you should also set the expiration time, say 5 minutes. Following this, you fetch response for the same request from the cache for 5 minutes. Once 5 minutes are passed, give a call to the REST API, and cache the newly received response again for 5 minutes.

Now to see it in action, I take an example of Pixabay API. Pixabay provides royalty-free images and videos which can be used on your websites. I’ll hit the Pixabay API’s endpoint to fetch images, store the response in the database for cache purposes and use it for the same request next time.

Database Configuration

It requires creating a table in the database for storing information. I’ll create a cache table having columns – p_key, p_value, and expiration. The below SQL creates this table along with specified columns.

CREATE TABLE `cache` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `p_key` varchar(255) NOT NULL,
  `p_value` text NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

We have to interact with this database table for exchanging data. So write the code for it in the class-db.php file.

<?php
class DB {
    private $dbHost     = "DB_HOST";
    private $dbUsername = "DB_USERNAME";
    private $dbPassword = "DB_PASSWORD";
    private $dbName     = "DB_NAME";
 
    public function __construct(){
        if(!isset($this->db)){
            // Connect to the database
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
            if($conn->connect_error){
                die("Failed to connect with MySQL: " . $conn->connect_error);
            }else{
                $this->db = $conn;
            }
        }
    }
 
    public function get_cache($key) {
        $sql = $this->db->query("SELECT * FROM cache WHERE p_key = '$key'");
        return $sql->fetch_assoc();
    }
 
    public function upsert_cache($arr_data = array()) {
        $key = $arr_data['key'];
        $value = $arr_data['value'];
 
        // check if cache exists by fetching it's details
        $data = $this->get_cache($key);
        $time = time();
 
        if(empty($data)) {
            // insert the cache
            $this->db->query("INSERT INTO cache(p_key, p_value, expiration) VALUES('$key', '$value', '$time')");
        } else {
            // update the cache
            $this->db->query("UPDATE cache SET p_value = '$value', expiration = '$time' WHERE p_key = '$key'");
        }
    }
}

This class file has the below important methods.

  • get_cache() : This method will return the result matching the p_key value. The value of p_key is nothing but a request we intend to do with Pixabay API.
  • upsert_cache() : This method either inserts or updates the row in the cache table.

Get Pixabay API Key

In order to integrate Pixabay REST API, you have to get your API Key. Sign up on Pixabay. After registering with Pixabay visit the linked page again. On the same page, you’ll find your API key under the Search Images document section.

pixabay-api-key

Install Guzzle Library

REST API integration requires sending HTTP requests and handling the response received from the third-party service. For such operations, I mostly use the Guzzle package. This package makes it easy to handle HTTP request and response. Install this library using the command:

composer require guzzlehttp/guzzle

Caching REST API Response

We are all set to achieve the goal of this article. Take note we only cache GET request and their response. You don’t need to cache POST, PUT and DELETE requests.

To get the list of images from Pixabay, you need to hit the https://pixabay.com/api?key=YOUR_API_KEY endpoint. Using a Guzzle, we’ll write the code as follows.

<?php
require_once "vendor/autoload.php";
 
use GuzzleHttp\Client;

define('PIXABAY_API_KEY', 'PASTE_YOUR_API_KEY');

try {
    $client = new Client([
        // Base URI is used with relative requests
        'base_uri' => 'https://pixabay.com',
    ]);
    
    $response = $client->request('GET', '/api', [
        'query' => [
            'key' => PIXABAY_API_KEY,
        ]
    ]);
    
    $body = $response->getBody();
    $result = json_decode($body);
    print_r($result);
} catch (Exception $e) {
    echo $e->getMessage();
}

If you run this code, each time it gives a call to Pixabay’s server. It’s not the ideal way. On the Pixabay documentation, it also mentioned you should cache results for 24 hours.

That being said, let’s see how to cache this REST API response. For demo purposes, I’ll cache the results for 5 minutes. Change this time as per the service you’re integrating. It’ll be 24 hours for Pixabay.

<?php
require_once "vendor/autoload.php";
require_once "class-db.php";
 
use GuzzleHttp\Client;

define('PIXABAY_API_KEY', 'PASTE_YOUR_API_KEY');

$call_api = true;
$db = new DB();
$response = $db->get_cache('list');

if (!empty($response) && ( ( ( time() - $response['expiration'] ) / 60 ) < 5 )) {
    echo "cache";
    $result = json_decode($response['p_value']);
    print_r($result);
    $call_api = false;
}

if ($call_api) {
    echo "api";
    try {
        $client = new Client([
            // Base URI is used with relative requests
            'base_uri' => 'https://pixabay.com',
        ]);
        
        $response = $client->request('GET', '/api', [
            'query' => [
                'key' => PIXABAY_API_KEY,
            ]
        ]);
        
        $body = $response->getBody();

        // insert into cache
        $db->upsert_cache(['key' => 'list', 'value' => $body]);

        $result = json_decode($body);
        print_r($result);
    } catch (Exception $e) {
        echo $e->getMessage();
    }
}

Here, I named a key as ‘list’ because we are fetching a list of images. You should pass this name as per the task performed. The above code first checks if the cache table has a row matching the ‘list’ key. If it exists and is cached before less than 5 minutes, it gets the result from the database instead of hitting the API endpoint. And when we hit the API endpoint, we store the result in the cache table.

I’ll take one more example of caching where we store the result received by passing a search term.

Search Pixabay Images by Keyword

To fetch the images by keyword, we need to pass a search term(‘q’ parameter) along with the API key. Here, I’ll name the key as a search term to store results in the database. The code is mostly the same as what we have written earlier.

<?php
require_once "vendor/autoload.php";
require_once "class-db.php";
 
use GuzzleHttp\Client;

define('PIXABAY_API_KEY', 'PASTE_YOUR_API_KEY');

$search = "waterfall";
$call_api = true;
$db = new DB();
$response = $db->get_cache($search);

if (!empty($response) && ( ( ( time() - $response['expiration'] ) / 60 ) < 5 )) {
    echo "cache";
    $result = json_decode($response['p_value']);
    print_r($result);
    $call_api = false;
}

if ($call_api) {
    try {
        echo "api";
        $client = new Client([
            // Base URI is used with relative requests
            'base_uri' => 'https://pixabay.com',
        ]);
        
        $response = $client->request('GET', '/api', [
            'query' => [
                'key' => PIXABAY_API_KEY,
                'q' => substr(urlencode($search), 0, 100),
            ]
        ]);
        
        $body = $response->getBody();

        // insert into cache
        $db->upsert_cache(['key' => $search, 'value' => $body]);

        $result = json_decode($body);
        print_r($result);
    } catch (Exception $e) {
        echo $e->getMessage();
    }
}

I hope you got an idea about caching REST API response with PHP and MySQL. Give it a try and let me know your thoughts and suggestions in the comment section below.

Related Articles

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.

1 thought on “Caching REST API Response with PHP and MySQL”

Leave a Reply

Your email address will not be published. Required fields are marked *