I’m a database guy. That’s one reason why I like working with WordPress, because everything is stored in a database. Every page, every post, all content on the site is in a database. If you can store it, you can retrieve it.
Let’s say you have a large set of data that you want to display in a list by using a table. Tables have rows and columns to organize your data and make your collection easy to read.
Standard HTML Table
| Name | Awesome | Website | Date Created | Photo |
|---|---|---|---|---|
| AAA Towing Record | YES | https://towingredondo.com/ | 2017-07-25 | |
| Adding From Web Form | YES | http://testsite.dev/add-new-records/ | 2017-07-23 |  |
| Beach | YES | 2017-07-17 |  |
|
| Beach Photo | YES | https://www.google.com/ | 2017-07-23 |  |
| Eighth Record | YES | https://rumspeed.com/ | 2017-07-17 | |
| Eleventh Record | no | 2017-07-23 | ||
| Fifth Record | YES | https://rumspeed.com/ | 2017-07-16 |  |
| First Record | YES | https://rumspeed.com/ | 2017-07-15 |  |
| Fourth Record | no | https://rumspeed.com/ | 2017-07-15 |
Depending on how many records you have, putting it in an HTML table might be the best way to display it. If you have around 25 records, you may want to code that by hand since it will be relatively easy to maintain. However, let’s say you have around 500 records. Maintaining that within the WordPress interface can be a little tedious; there is a lot of searching and clicking to find, edit and save your data. This is where spreadsheets come in handy.
Spreadsheets are designed to maintain datasets like this. The problem is, how do we connect our website to a spreadsheet? That’s where Airtable comes in. Airtable can be a great solution for a large collection of data. It’s a database disguised as a spreadsheet. If you love spreadsheets and databases, you’ll love Airtable as much as I do.
With Airtable, you create a “base” and start entering content into a table as if you’re editing a spreadsheet. You can even start by importing data from a CSV file. The beautiful thing about this is that every “base” has an API. With this API you can communicate directly to your fancy database from other applications. In other words, Airtable is a method of moving a large data set into a collection and using the API to extract the data and display it in an HTML table on a page in a WordPress website.
But HTML tables get a bad rap. They are “old school” and not all that flexible. Introducing DataTables. DataTables is a JavaScript library for formatting and displaying HTML tables. Combining Airtable with DataTables solves the problem of storing data and displaying it in a modern, intuitive way. It makes your table look great while providing an intuitive interface that improves usability.
It has a lot of nice features, like instant column sorting, built in search and pagination for large datasets. It works great, it’s fast and really slick. However, it still comes down to how to manage the content in that table. If you’ve got a lot of records to display, you still will have to format it with HTML in order for DataTables to do the heavy lifting. When you have to edit content, you have to work around the formatting and find what you need changed. Five hundred records is a lot of HTML tags and formatting, and if you miss one closing tag you can blow the whole table.
For example, I created a table and added rows to the it, but I had to write some code to access the API to pull out the data. For the display, I looped through the recordset returned by the API call and wrapped my HTML tags around it until the table was complete. Now that I had my table of data, I needed to finish off the display. DataTables requires two files where all the magic happens and a bit if JavaScript to apply it to the HTML table that I built. With a few lines of code, my standard HTML table was transformed into a beautiful, highly functional table.
| Name | Awesome | Website | Date Created | Photo |
|---|---|---|---|---|
| First Record | YES | https://rumspeed.com/ | 2017-07-15 | |
| Second Record | YES | https://www.google.com/ | 2017-07-15 | |
| Third Record | YES | https://rumspeed.com/ | 2017-07-15 | |
| Fourth Record | no | https://rumspeed.com/ | 2017-07-15 | |
| Fifth Record | YES | https://rumspeed.com/ | 2017-07-16 | |
| Sixth entry | no | 2017-07-16 | ||
| Seventh Record | no | https://rumspeed.com/ | 2017-07-16 | |
| Eighth Record | YES | https://rumspeed.com/ | 2017-07-17 | |
| Beach | YES | 2017-07-17 | ||
| Tenth Record | no | 2017-07-23 | ||
| Eleventh Record | no | 2017-07-23 | ||
| Adding From Web Form | YES | http://testsite.dev/add-new-records/ | 2017-07-23 | |
| Beach Photo | YES | https://www.google.com/ | 2017-07-23 | |
| Redondo Beach | YES | https://en.m.wikipedia.org/wiki/ | 2017-07-23 | |
| Testing a new entry from the form | YES | 2017-07-25 | ||
| AAA Towing Record | YES | https://towingredondo.com/ | 2017-07-25 | |
| Test This | YES | google.com | 2017-10-01 |
Now I have a way to easily store the data, and with the API connection, it’s automatically pulled into the webpage. All the heavy lifting is done, and I can use Airtable in a browser or on a phone to maintain the dataset. Another nice feature of Airtable is the ability to use a form and embed that on your webpage to collect data. Each time a form is submitted, a new row is added to the table. Then, instantly, that data is available in the table on your web page. Brilliant!
There’s a lot more that you can do with data relationships, groups, filtering, sorting and conditional logic that I may get into in future posts, but the idea for this one has been achieved: Store data in a database where it can be easily maintained and display the records in a nice modern table that looks great on the web and on a phone.
If you store it, you can retrieve it.
Very cool. Using two of my favorite technologies: Airtable and DataTables. Throw in some D3js and we’re in nirvanaland.
Glad you like it. I had fun putting it together. There are so many possibilities to implement the two together. D3js looks amazing and incredibly creative.