Lately I've been rediscovering the joy of PHP. Also been helping an MVP get off the ground which uses a lot of nocode/lowcode bits and pieces and miraculously puts them together.
Anyway, I had to write to an Airtable table with PHP and some quick googling didn't find an example to copy-paste so I'm writing this one for posterity.
So I have a table called people which has Name and Email fields. I want to write to it programmatically. Airtable has this pretty cool documentation which uses existing data from your real tables as examples. Search "airtable api" and you'll find your way.
There you'll see things like this:
Here people is the table name and blarghblahbla is the the AirTable app ID (obfuscated). This is generated for you so no need to worry about it, just copy. The question is to turn it into PHP code.
Oh, you'll also need your Airtable key which you get from your account:
Armed with these prerequisites, here's the PHP code that uses cURL to write to the table:
// get this from your account
$airtable_key = 'keyeKanyeOladiOblada';
// URL generated by the docs
$url = 'https://api.airtable.com/v0/blarghblahbla/people';
$headers = [
'Content-Type: application/json',
'Authorization: Bearer ' . $airtable_key,
];
// this is the JSON-encoded record to write to the table
$post = '{"fields": {"Name": "Stoyan", "Email": "ssttoo@ymail.com"}}';
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_POST,1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $post);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
$result = curl_exec($ch);
curl_close($ch);
$json = json_decode($result, true);
Et voilà!
Every website we build has its own unique challenges. But the tools we use can help us tackle them head on.
WordPress, and its never-ending ecosystem of plugins have helped web designers tame even the most difficult client requests. Pretty much everything we need is just click-and-go. And if it doesn’t exist, we can build it ourselves.
But for each of these solutions we implement, there is a hidden consequence. One that only rears its head when we dig a little deeper for user-specific data. Only then do we realize the tangled mess that sits beneath that pretty outer shell.
Unlimited Downloads: 1200+ WordPress Themes
WordPress Themes, WooCommerce Themes, Plugins, Web Templates, Design Assets, and much more!
A Simple Start
In a default WordPress installation, user data (at least, the kind you might want to export) is actually pretty neat and tidy. Data is stored in the wp_usermeta database table. Inside, you’ll find the basics like the user’s name, along with their role/capabilities and their account preferences.
Combine this with what is in the wp_users table (username, email address, password), and you can grab a lot of useful information for every user on your website. Plus, you can easily import a CSV list of new users if need be.
Of course, most websites don’t stop at a default configuration. On the contrary, we often add any number of plugins so that users can do more with our site.
We want them to do things like have custom profile information, keep track of orders and belong to specific groups. Plus, features like forums, support portals and learning management systems are also widely used these days.
And that’s just scratching the surface. There’s a whole lot more that can be added to a typical WordPress site. That’s a good thing, until you have to try and wrangle the data.
Data, Data, Everywhere
The issue at hand is not so much the fault of WordPress itself. It’s that where a particular piece of data is stored is, in many cases, left up to plugin developers. That can result in the user data you want to gather being stored all over the place. It’s the nature of the beast.
Let’s use a recent website I worked on as an example. It uses a membership plugin, which allows people to join the client’s organization.
Collected Data
When they sign up, we ask them for more than just the standard WordPress user metadata. New members are asked for information such as:
- Mailing address;
- Phone number;
- Their preference for how they receive newsletters (email or postal mail);
Generated Data
Beyond the information we ask users to supply, there is also a plethora of data generated by the membership plugin itself, including:
- Membership status (active or inactive);
- Membership level;
- Membership expiration date;
There’s nothing over-the-top about the configuration. It’s probably not much different than the tens of thousands of other sites running the same membership suite.
While the site itself isn’t too complicated, that doesn’t mean its user data is easy to find. Let’s look at how a seemingly-simple task can become a time-consuming challenge.
The Challenge
The client had a very basic need. They wanted an export of all active members who prefer a hard copy of the organization’s newsletter sent to them. Based on what we have, this should take just a few minutes to cobble together. I was way off.
This was much more difficult than I envisioned. The data we needed was right there in the database. But trying to piece it together proved to be a near-monumental task for someone who isn’t a database-querying wizard.
However, that’s why we have plugins, right? And there are a ton of different options – both free and premium. But, no matter what I tried, I couldn’t seem to get exactly what the export required. Here’s why:
- The custom data we ask members to provide us is easy enough to get. It’s in the
wp_usermeta table, which user export plugins are generally able to find. So, generating a list of users who wanted that hard copy of the newsletter was fairly straightforward.
- The other member-related data, however, is stored in another table that is exclusive to the membership plugin. Even a rather robust commercial plugin I used couldn’t help me here.
The result was that I could find out who asked for the postal edition of the newsletter, but I couldn’t tell if their membership was active – not very helpful.
Sure, that info was stored in an adjacent table in the same database – but it may as well have been on Jupiter for my purposes. It felt like searching the house for your keys, only to find that your neighbor is holding them for ransom.
Eventually, I did find an export plugin – one that included an add-on for the membership plugin – that was able to help me put together the data I needed. If that hadn’t existed, I’d still be stuck with only a half-solution.
Can the Experience Be Improved?
This all left me to wonder how these situations can be improved or avoided in the first place. It’s a tough call.
First, I admit that these types of challenges aren’t my strong point. Someone with plenty of expertise in PHP and MySQL could probably come up with a custom solution. Me? I’m left to experiment with plugins and groan when they don’t work as I’d hoped.
But one question worth asking is, should such expertise be required to export a complete set of user data? It seems like there should be a more user-friendly way to make this work.
The fact that WordPress allows plugins to create their own database tables is both understandable and even beneficial. It ensures that we can install and uninstall plugins without fear of breaking something.
Yet, while this all works seamlessly to the naked eye, it’s anything but to those of us trying to access the underlying data.
Perhaps there needs to be an API that allows us to grab everything related to a specific user, regardless of where it is stored in the database. But I’ll leave that discussion to those who know the pros and cons of such a feature.
Until then, I’ll just keep on piecing together things as clients need them – hoping for a much cleaner process in the future.
The post Why Wrangling User Data in WordPress Can Be a Nightmare appeared first on Speckyboy Design Magazine.
