Use Airtable as a CMS with PHP

Airtable is an incredible way to store, organize, and collect data. It has an incredibly intuitive interface, it’s fast and most importantly it handles many types of media and complicated relations with ease. And it has an JSON API out of the box. It makes a perfect headless CMS for content based websites! BUT there are limitations. Granted the limitations offered from Airtable are extremely generous and most sites would probably never hit them, it’s something that has weighed on my decision to use Airtable as a CMS for a long time. So let’s talk about how we work around this!

If you don’t have an Airtable account already create one here https://airtable.com/invite/r/lmPnYpVO 

Let’s start by looking at the API call. There are several ways you can call this API but the simplest and most universal is just with a curl call. You can find your API url by going to https://airtable.com/api and selecting the base you would like to retrieve data from. You can then find your API key in your Account Overview by clicking on your profile in the upper right corner and selecting Account.

Then you will need to append the ?api_key=API_KEY to the url you retrieved.

<?php
// note that you will need to append your API key to the url that you find at https://airtable.com/api
// if you would like your data to be sorted in a particular way add the view paramater to the url and the name of the view you would like to dictate results order
$url =
  "https://api.airtable.com/v0/BASE_ID/TABLE_NAME?api_key=API_KEY&view=VIEW_NAME";
$curl = curl_init($url);
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
$data = curl_exec($curl);
curl_close($curl);

$data = json_decode($data, true);
?>
<?php foreach ($data["records"] as $record): ?>
// your html goes here
<?php endforeach; ?>

You should be pulling in data now!

Now let’s solve the two bigger problems here. First, you may notice that the API call is not exactly fast. I don’t know if Airtable does that on purpose or if that is just how it is but it seems a bit sluggish to me personally. The second problem is the API rate limiting. Airtable does give a very generous 5 calls per second limit that would probably be pretty hard to hit for most sites but we want to prepare for that day you make it to the front page of reddit 😉

So let’s talk cacheing. Cacheing will solve both our speed and rate limiting problems. Now you could certainly do this with a “pre-built” solution, whether that is a package or something your hosting provider offers or with Cloudflare, but the solution I have is self supported and so simple to implement that you might as well give it a shot.

We will need two files, top-cache.php and bottom-cache.php(see below), and they will be included in the top and bottom(respectively) of any page that you want to cache. This should include any page that is making an Airtable API call but could include all of your pages.

Once implemented you will notice that when a page is visited a new html file will be created that will include the prefix cache-. Once that file is created it will be served in place of the PHP file it represents until the cache time expires. Awesome right?! If you open up one of those cache files you will see that it is the HTML rendered version of your PHP with all of the Airtable data already populated in it, so the next person to visit that page doesn’t have to wait for the API call and it won’t count against your rate limit.

top-cache.php

<?php

$url = $_SERVER["SCRIPT_NAME"];
$break = Explode("/", $url);
$file = $break[count($break) - 1];
$cachefile = "cached-" . substr_replace($file, "", -4) . ".html";
// Time cache is live in milliseconds, make this as long as you would like. The last number represents minutes, currently set to expire after 24hrs.
$cachetime = 1000 * 60 * 1440;

if (file_exists($cachefile) && time() - $cachetime < filemtime($cachefile)) {
    echo "<!-- Cached copy, generated " .
    date("H:i", filemtime($cachefile)) .
    " -->\n";
    readfile($cachefile);
    exit();
}
ob_start();
?>

bottom-cache.php

<?php
$cached = fopen($cachefile, 'w');
fwrite($cached, ob_get_contents());
fclose($cached);
ob_end_flush();
?>

If you would like to learn about this cacheing technique in more detail see my post on building a static php site.