Website incorporating airtable


#1

Hi, this may be a basic question, but could some of the more experienced users here please recommend an easy way to build a website that allows users to query data from an airtable database? I have found a Wordpress plugin but would appreciate advice on whether Wordpress is the best way for a beginner to start trying to build something reasonable. Many thanks, Simon


#2

This question is basic like the tip of an iceberg is small. :slight_smile:

While querying data from Airtable and displaying it on a website is pretty straightforward, figuring out which data to retrieve and how to display it can become extremely complex. Short of fully exposing the Airtable API to your website user, you’ll need to create some sort of “model” that translates your user requests into appropriate Airtable API calls. You’ll also end up figuring out caching, error handling, date conversion, and writing all the helper functions you’ll discover you need.

For example, an itinerary management solution I created for a travel agency required departure and arrival date/times. Seemed easy enough but then I was asked to calculate flight duration based on those two fields. Creating the two fields in Airtable was easy, figuring out how to store/convert the origin/destination timezones and calculate the resulting difference was a fair challenge. Simply storing data doesn’t mean you can “simply” display data.

Regarding Wordpress vs something else, it depends on what you’re already familiar with. I’ve integrated Airtable data into Wordpress and CodeIgniter. Wordpress is nice because it’s already such a full featured framework that allows for custom fields on posts and has wp_remote_get, transients, etc.

One of the most complex issues you’ll discover is how you should map your pages to your data. Will pages themselves be mapped to airtable records via their URL? Or will you use code on each page to request a specific set of records based on some other field? If you have WYSIWYG editor do you need shortcodes to fetch specific data or will you always use PHP in a template file? Will you use views to manage what’s visible online or checkboxes called ‘Enabled’ (or something similar). When you fetch related records will you respect the existing order they’re in or will you want to reorder them by a field in the related table?

If you’re just getting started and want to play around I would recommend starting with a simple PHP file and not worry about building a website or choosing a framework. This will let you get familiar with the Airtable API and practice creating complex filterByFormula parameters and looping through results. You’ll learn about pagination and related records, views and JSON result structure.

If you have a specific project/need, let me know and perhaps I’ll be able to get more specific also.


#3

Hi Chester, Thanks very much for your reply. I can see that there is a lot to learn, so your iceberg analogy seems appropriate!

I will describe a little about what I am trying to do and why, in case you can give me any pointers, and then I will look for websites / books that will get me going - recommendations would be welcome.

Initially my children and I thought it would fun and interesting to try to build a website / app and we came up with the idea of a database of drinks or snack from cafes (e.g., Starbucks, etc) where the user could enter their location, max walking distance, drink preferences and maximum spend and the website would return a map or list showing what they could get that meets their requirements. The plan was that we would input the data into a database manually and then build a simple query. It is more of a learning exercise than anything else, and one of the main objectives was for them to feel that if they have an idea, just get on and try it, etc.

However it seems a bit more complicated that I imagined, especially as I have not yet acquired the programming skills for myself to get it going! We have tried a wordpress template and started with an SQL database, although once we got beyond the simple template it got complicated. Then we found Airtable which will make the database part simpler and more intuitive for them, but now I’m not sure how to build the link to the website in a way that looks clean and easy for the user. So I thought better to ask here and get some advice, rather than try many different ways that all take a lot of learning from my current starting point.

Anyway, based on your advice above, it seems a good starting point is to get familiar with PHP and how that would link to Airtable? Presumably I would need to install a development environment on my PC to start to play around with a file that I can then view, and try connecting to the Airtable database?

In any case, thanks again for your help so far.


#4

Here’s a jump start using PHP code that should run just fine on just about any server/setup. I’ve quickly edited one of my class methods down to this—meaning, I haven’t actually run this code but it’s essentially what I always use when I’m outside of Wordpress. Inside of Wordpress, I have a different function that uses Wordpress’ wp_remote_get, transients, and other functions specific to the framework.

The following should return you records from a view called “Open Late” for a table called “Items” that have fields called “Type” and “Price” that match the values specified. It will sort the results on two fields—first Price then Type.

Good luck!

<?php

	$AIRTABLE_API_KEY = "xxxxx";
	$AIRTABLE_API_URL = "xxxxx";
	$AIRTABLE_APP_ID = "xxxxx";
	
	$table = "Items";
	$filterByFormula = "OR( AND({Type}='Drink',{Price} <= 3), AND({Type}='Snack',{Price} <= 6))";
	$view = "Open Late";
	$sort = array(array("field" => "Price", "direction" => "desc"), array("field" => "Type", "direction" => "asc"));
	$limit = 10;
	$params = array("filterByFormula" => $filterByFormula, "view" => $view, "sort" => $sort, "limit" => $limit);
	
	$items = getAirtableRecords($table,$params);
	
	var_dump($items);

	private function airtable_get($endpoint,$params=array()){
		global $AIRTABLE_API_URL, $AIRTABLE_APP_ID, $AIRTABLE_API_KEY;
		
		$ch = curl_init();

		$http_headers = array(	'Authorization: Bearer ' . $AIRTABLE_API_KEY,
								'Content-Type: application/json'
							);
							
		curl_setopt( $ch, CURLOPT_HTTPHEADER, $http_headers );																						
		curl_setopt( $ch, CURLOPT_RETURNTRANSFER, TRUE );	
		curl_setopt( $ch, CURLOPT_TIMEOUT, 10 );
		curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, $method );
		
		// Create an array to use to for URL and HTTP Headers.
		$http_params = array();

		// Initialize the offset.
		$offset = '';
		
		$records = array();

		// Make calls to Airtable, until all of the data has been retrieved...
		while ( !is_null( $offset ) ) {
			
			// We're using an offset to get specific batches of records.
			if (!empty($offset)){
				$http_params["offset"] = $offset;	
			}
			
			if (isset($params["view"])){
				// Has a view been specified? The API will honor any filters 
				// that have been applied to the view, as well as any sort
				// order that has been applied to it.
				$http_params["view"] = $params["view"];
			}
			
			if (isset($params["limit"])){
				// By default, the APi will return 100 records per request.
				// You can specify smaller batch sizes using the "limit"
				// query parameter, as we are here.
				$http_params["limit"] = $params["limit"];
			}
			
			if (isset($params["sort"])){
				// You may specify a sort order for the request,
				// which will override any sort order that has been 
				// applied on the view.
				$http_params["sort"] = $params["sort"];
			}
			
			if (isset($params["filterByFormula"])){
				$http_params["filterByFormula"] = $params["filterByFormula"];
			}
			
			// Specify the URL to call.
			$curlopt_url =   $AIRTABLE_API_URL
				           . $AIRTABLE_APP_ID
				           . '/' . rawurlencode($endpoint)
				           . "?" . http_build_query($http_params);

			curl_setopt( $ch, CURLOPT_URL,	$curlopt_url );
					
			// Execute the request.
			$response_json = curl_exec( $ch );

			// If there was a curl error encountered while making the call...
			if ( curl_errno( $ch ) != 0 ) {

				// Show an error message with the CURL error code.
				// For a complete list of error codes, see:
				// http://curl.haxx.se/libcurl/c/libcurl-errors.html 
				echo '<h2>CURL Error</h2>';
				echo 'Code: ' . curl_errno( $ch );
				echo "\n<br>\n".$curlopt_url;
				die;

			} 

			// Decode the JSON-formatted response that was received from the Airtable API.
			$response = json_decode( $response_json, TRUE );

			// If the Airtable API returned an error...
			if ( isset ( $response["error"] ) ) {

				// Show an error message.
				echo '<h2>Airtable Error</h2>';
				echo 'Type: ' . $response["error"]["type"] . '<br />';
				echo 'Message: ' . $response["error"]["message"] . '<br />';
				die;			

			}

			// When getting a table, we'll build an array of records,
			// when getting a record, we'll just return the record.
			if (isset($response["records"])){
				// remember, this could be just one of many pages
				// so merge the results and keep fetching till we're done!
				$records = array_merge($records,$response["records"]);	
			} else {
				$records = $response;
			}

			// Adjust the offset.
			// Airtable returns NULL when the final batch of records has been returned.
			$offset = (isset($response["offset"]))? $response["offset"] : null;

		}

		curl_close( $ch );

		return $records;
		
	}
?>

#5

Thank you, I will give this a go at the weekend and let you know how I get on.


#6

HI Chester, I have a couple of questions. Sorry if they are too basic - I have spent some time looking for help but couldn’t find a clear answer.

The first is definitely a basic question. Am I correctly specifying these?

$AIRTABLE_API_KEY = "xxxxx"; // I have used the key from my account - pretty sure this is right
$AIRTABLE_API_URL = "xxxxx"; // I have used https://api.airtable.com/v0 - I have a feeling this is wrong?
$AIRTABLE_APP_ID = "xxxxx"; // I have used app0oEDZyfkTdozp3 which I got from the API - correctly, I think

The second is that when I have run this (assuming the above is done correctly), I got an error message:

‘Parse error: syntax error, unexpected ‘private’ (T_PRIVATE), expecting end of file in C:\Abyss Web Server\htdocs\Airtable_Test.php on line 18’

Airtable_Test is the name of the php file. I am using Atom as the text editor, if that helps.

I have found a few suggestions from searching online but they didn’t seem correct - suggesting missing brackets, which I can’t see.

Thanks

Simon


#7

Sorry for posting code last time without testing! There were a few minor errors—not very helpful! :blush:

I’ve tested and run this code—simply paste into a PHP file and specify your own key and app id as you previously did:

<?php

	ini_set('display_errors', 1);
	ini_set('display_startup_errors', 1);
	error_reporting(E_ALL);

	$AIRTABLE_API_KEY = "keyd2zOxxxxuzehqE";
	$AIRTABLE_API_URL = "https://api.airtable.com/v0/";
	$AIRTABLE_APP_ID = "appJSP2xxxxDrixhv";
	
	$table = "Prices";
	$filterByFormula = "{Total} > 40";
	$view = "Test";
	$sort = array(array("field" => "Description", "direction" => "desc"), array("field" => "Plan", "direction" => "asc"));
	$limit = 10;
	$params = array("filterByFormula" => $filterByFormula, "view" => $view, "sort" => $sort, "limit" => $limit);
	
	$records = airtable_get($table,$params);
	
	var_dump($records);

	function airtable_get($endpoint,$params=array()){
		global $AIRTABLE_API_URL, $AIRTABLE_APP_ID, $AIRTABLE_API_KEY;
		
		$ch = curl_init();

		$http_headers = array(	'Authorization: Bearer ' . $AIRTABLE_API_KEY,
								'Content-Type: application/json'
							);
							
		curl_setopt( $ch, CURLOPT_HTTPHEADER, $http_headers );																						
		curl_setopt( $ch, CURLOPT_RETURNTRANSFER, TRUE );	
		curl_setopt( $ch, CURLOPT_TIMEOUT, 10 );
		curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, "GET" );
		
		// Create an array to use to for URL and HTTP Headers.
		$http_params = array();

		// Initialize the offset.
		$offset = '';
		
		$records = array();

		// Make calls to Airtable, until all of the data has been retrieved...
		while ( !is_null( $offset ) ) {
			
			// We're using an offset to get specific batches of records.
			if (!empty($offset)){
				$http_params["offset"] = $offset;	
			}
			
			if (isset($params["view"])){
				// Has a view been specified? The API will honor any filters 
				// that have been applied to the view, as well as any sort
				// order that has been applied to it.
				$http_params["view"] = $params["view"];
			}
			
			if (isset($params["limit"])){
				// By default, the APi will return 100 records per request.
				// You can specify smaller batch sizes using the "limit"
				// query parameter, as we are here.
				$http_params["limit"] = $params["limit"];
			}
			
			if (isset($params["sort"])){
				// You may specify a sort order for the request,
				// which will override any sort order that has been 
				// applied on the view.
				$http_params["sort"] = $params["sort"];
			}
			
			if (isset($params["filterByFormula"])){
				$http_params["filterByFormula"] = $params["filterByFormula"];
			}
			
			// Specify the URL to call.
			$curlopt_url =   $AIRTABLE_API_URL
				           . $AIRTABLE_APP_ID
				           . '/' . rawurlencode($endpoint)
				           . "?" . http_build_query($http_params);

			curl_setopt( $ch, CURLOPT_URL,	$curlopt_url );
					
			// Execute the request.
			$response_json = curl_exec( $ch );

			// If there was a curl error encountered while making the call...
			if ( curl_errno( $ch ) != 0 ) {

				// Show an error message with the CURL error code.
				// For a complete list of error codes, see:
				// http://curl.haxx.se/libcurl/c/libcurl-errors.html 
				echo '<h2>CURL Error</h2>';
				echo 'Code: ' . curl_errno( $ch );
				echo "\n<br>\n".$curlopt_url;
				die;

			} 

			// Decode the JSON-formatted response that was received from the Airtable API.
			$response = json_decode( $response_json, TRUE );

			// If the Airtable API returned an error...
			if ( isset ( $response["error"] ) ) {

				// Show an error message.
				echo '<h2>Airtable Error</h2>';
				echo 'Type: ' . $response["error"]["type"] . '<br />';
				echo 'Message: ' . $response["error"]["message"] . '<br />';
				die;			

			}

			// When getting a table, we'll build an array of records,
			// when getting a record, we'll just return the record.
			if (isset($response["records"])){
				// remember, this could be just one of many pages
				// so merge the results and keep fetching till we're done!
				$records = array_merge($records,$response["records"]);	
			} else {
				$records = $response;
			}

			// Adjust the offset.
			// Airtable returns NULL when the final batch of records has been returned.
			$offset = (isset($response["offset"]))? $response["offset"] : null;

		}

		curl_close( $ch );

		return $records;
		
	}
?>

#8

Thanks very much, I will have another go.


#9

Hi Chester. Here a a few questions / comments which would be helpful to check that I am on the right track.

a) The first time I ran it, it gave me a curl error code 60. This suggests something about the authentication is not working, I have found after some digging. I have checked my Key and APP ID so maybe it is something about the code where it tries to authenticate. Does it work for you?
To get past this I have entered a line:
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, false);
which worked but I will try to figure it out later

b) Is your code saying 'Show all rows where the field ‘Total’ is > 40, listing the field ‘Description’ sorted in descending order and the field ‘Plan’ is sorted in ascending order, from the view ‘Test’ and the table ‘Prices’?

c) I made a couple of changes to suit my database table (using ‘Total’ < 40 and changing 'Plan to be ‘Name’ and got an output like this. Does this look right? If so my next tasks are 1) to work out my actual queries and 2) to work out how to make html display the output nicely - does that sound right?

array(5) { [0]=> array(3) { [“id”]=> string(17) “recCX7q8IHPWLYb7z” [“fields”]=> array(3) { [“Name”]=> string(7) “Biscuit” [“Total”]=> float(0.99) [“Description”]=> string(5) “Snack” } [“createdTime”]=> string(24) “2016-08-27T15:49:58.000Z” } [1]=> array(3) { [“id”]=> string(17) “recp1FdjKu2s2zRAK” [“fields”]=> array(3) { [“Name”]=> string(8) “Sandwich” [“Total”]=> float(3.58) [“Description”]=> string(5) “Snack” } [“createdTime”]=> string(24) “2016-08-27T15:49:19.000Z” } [2]=> array(3) { [“id”]=> string(17) “recB62WbIzatNWrfS” [“fields”]=> array(3) { [“Name”]=> string(6) “Coffee” [“Total”]=> float(1.28) [“Description”]=> string(5) “Drink” } [“createdTime”]=> string(24) “2016-08-27T15:43:11.000Z” } [3]=> array(3) { [“id”]=> string(17) “rec27pAQfqJjNChVt” [“fields”]=> array(3) { [“Name”]=> string(5) “Juice” [“Total”]=> float(2.65) [“Description”]=> string(5) “Drink” } [“createdTime”]=> string(24) “2016-08-27T15:49:04.000Z” } [4]=> array(3) { [“id”]=> string(17) “rec3ERC6KHLGcZphU” [“fields”]=> array(3) { [“Name”]=> string(3) “Tea” [“Total”]=> float(2.02) [“Description”]=> string(5) “Drink” } [“createdTime”]=> string(24) “2016-08-27T15:43:11.000Z” } }

Many thanks


#10

Good troubleshooting! I won’t wade too deeply into the SSL issue as it’s outside my expertise and the scope of this forum, however here’s a good place to start: https://www.saotn.org/dont-turn-off-curlopt_ssl_verifypeer-fix-php-configuration/

My “airtable_get” function allows you to provide two parameters, with only the first required: $endpoint (i.e. table) and $params.

If you pass no $params then you’ll get every record from the table you specify in $endpoint. You may optionally pass any combination of the other parameters from my example. Visit http://airtable.com/api to learn more about each parameter and what it does.

I would recommend creating a mockup/wireframe (http://moqups.com is a great tool) to figure out what your end result should be and then “reverse engineer” it to figure out what data you need and what HTML/CSS/JS you’ll need to create it. You could also look into Bootstrap (or any other front-end framework) to help with the HTML/CSS/JS styling.


#11

Thank you so much for your help Chester!

I’m now one step closer to hand crafting my own blog that will be pulling data from AirTable. As I’m new to the back end of the internet, from your last post I’ll now investigate “Bootstrap” to help make a pretty picture. I’d appreciate any other tips or pointers you have on this.

Summary

array(3) { [0]=> array(3) { [“id”]=> string(17) “recoeQYbc53XQx5IA” [“fields”]=> array(3) { [“Description”]=> string(6) “StuffA” [“Plan”]=> string(12) “Stuff A Type” [“total”]=> int(100) } [“createdTime”]=> string(24) “2017-11-05T00:06:41.178Z” } [1]=> array(3) { [“id”]=> string(17) “recjdHIFxtwC7C4Rr” [“fields”]=> array(3) { [“Description”]=> string(6) “StuffB” [“Plan”]=> string(12) “Stuff B Type” [“total”]=> int(222) } [“createdTime”]=> string(24) “2017-11-05T00:06:41.178Z” } [2]=> array(3) { [“id”]=> string(17) “recOTssv6UJmWWFPy” [“fields”]=> array(3) { [“Description”]=> string(6) “StuffC” [“Plan”]=> string(12) “Stuff C Type” [“total”]=> int(133) } [“createdTime”]=> string(24) “2017-11-05T00:06:41.178Z” } }

Thanks again!