So I had a problem.  We have a WordPress site that has a few tables on it that have drifted out of date.  Now all the data we need to keep them updated is in a database and if this were a CFML site well then it’d be the end of the post.  But alas it isn’t, so the issue is what is the best way to make this automated and keep the site up to date.  I had liked the Tabulator JavaScript library (http://tabulator.info/) and used it in the past with static data.  I looked in the documentation and knew it could take a JSON feed.  As we all know ColdFusion can spit out JSON with a simple use of serializeJSON function so seemed like a natural fit.  However, poking around I didn’t see any documentation or guides on anyone doing this so here goes one.  Hopefully it’s helpful to someone, but more likely people may read and say “well yeah, of course”.   That’s ok too.

WordPress

So my old use of Tabulator was in pre-Gutenberg WordPress.  I have heard that Gutenberg will now let you put JavaScript into a page but because of our users we have stayed in the classic interface.  So this part may not be necessary if you can do it in the newer version.  For ours, I installed the plugin “Scripts n Styles” (https://wordpress.org/plugins/scripts-n-styles/) so that i could put in the custom CSS and JavaScript needed.

In the main editor, go to the page you want to add the table to and switch to the “Text” tab and put in a div that will hold the Tabulator table where you need it to appear.

<div id="testTable"></div>

The “Scripts n Styles” plugin will add a section at the bottom of the edit page table.  Go to the second text field which will place code in the body section of the page.

Here is my code for there and you can tweak all the settings to your heart’s content in the first part..

$("#testTable").tabulator({
fitColumns:true,
columns:[
{title:"Col1", field:"col1_fieldname", headerFilter:true},
{title:"Col2", field:"col2_fieldname", headerFilter:true},
{title:"Col3", field:"col3_fieldname", headerFilter:true}
]
});

$("#testTable").tabulator("setData", "https://testdomain.thathastheJSON.com");

Here’s a caveat to remember. Click the “Update Scripts” button in the “Scripts n Styles” section after every change.  The first time it may not matter but going forward it does seem to make a difference to make sure the changes in JavaScript are saved into the page.

Now because I plan on using Tabulator throughout the site I put the Tabulator JavaScript library and CSS links into the template itself.  Sure it means it will load on pages its not needed but figured it’s a trade off I was willing to make.  I did not want to have to hit every page should i need to update those includes.

ColdFusion

On this end I am using FW/1 but I’ll break it down to more simply show the basics as a single CFM page.  The blog didn’t like my cfscript opening and closing tabs but they should be wrapped around your code

     qryTestList = queryExecute( "Select 
                Select
                    col1_fieldname,
                    col2_fieldname,
                    col3_fieldname
                From 
                    test_table",
        {  }, 
        { datasource = "testDSN" } );

    writeOutput("#serializeJSON( qryTestList, 'struct'  )#");

Now here’s another thing to point out that i ran into. Since i was in FW/1 I originally used this in my controller

variables.fw.renderData( 'json', testList );

The renderData function uses serializeJSON but not in the way that tabulator needed.  I looked through the examples on the Tabulator site without seeing the format specifically defined anywhere.  So I looked at the source JSON for one of the examples: http://tabulator.info/exampledata/ajax to figure it out. That’s the format needed and you’ll notice is not the one natively returned by serializeJSON in CF2016 at least that i’m using. The answer is that second parameter in serializeJSON: ‘struct’.  Add that and the data conforms to what Tabulator needs.

A couple of last “gotchas”

If you are hosting your WordPress site and your ColdFusion API is on another domain you will likely run into a nice new error involving Cross-origin resource sharing (CORS).  The table will throw an error but in the console you’ll see the problem.  There’s lots of info out there about CORS if you want to look further but the guts of it is it blocks JavaScript and CSS code from making requests against a different domain.  So how do you get around that.  The answer is you need to add a new HTTP Header named “Access-Control-Allow-Origin” on your CF server’s domain.  Setting it to “*” will be all you need to let WordPress hit your API and get a good result.  Now I’m not a fan of “*” because that means anyone can now use the resource.  I am looking further into how to lock that down tighter but that is starting to get outside of CF specific content.  So I’ll just leave it as * will get you going but you may want to look further.  In my code I also have a cleared IP list of who can call the API but I’d like to lock it down further.

The second of the couple gotchas is more simple.  Just make sure if your site is SSL then you are also hitting an SSL secured link for your ColdFusion API.  WordPress, and all SSL really, don’t like to be mixed and matched.

That’s it.  Hopefully it’s helpful and if i broke anything by trying to simplify my code it is still close enough to get anyone interested on the right path.