Caching data and auto-update cache in WordPress tables

Caching dataSetup caching data in WordPress tables

We are proud to introduce a new feature that will dramatically increase the loading performance of your tables created from existing data sources like CSV, Excel, XML, JSON, PHP array, Google sheet, and the new type – Nested JSON as well. As you know, in this case, you will upload the file and initialize the table, whereupon it will be read every time the page loads. Before wpDataTables v5.0, if the source file was larger than 3.000 – 5.000 rows, the page load and generation time was extremely slow, and in some cases, the pages with large linked tables would break completely. With the new caching option, the load time is significantly lowered, but we still recommend keeping the data in these files down to around 5.000 rows.

In this tutorial, we will show you how to create a cache of data from tables created from the existing data sources. Let’s watch a live demo of the cached table first, and then go through the steps needed to create it.

Cache optionHow to set cache data in table

create-table-linked-to-a-source

Go to wpDataTables -> Create a Table and choose  Create a table linked to an existing data source option.

all_source_option

Depending on what you choose in the Input data source type option (Excel, CSV, XML, Google spreadsheet, JSON, Nested JSON, or PHP array) you can follow the detailed instructions on how to create those tables on these links:

  1. Create a table from an Excel file
  2. Create a table from a CSV file
  3. Create a table from XML
  4. Create a table from a Google spreadsheet
  5. Create a table from JSON
  6. Create a table from Nested JSON
  7. Create a table from the PHP array (if you’re getting dynamic data based on user or any other condition, then we advise you not to use the cache option)
cache_data_option

When you create the table you wish to create, you will see a new option “Cache data” in the Display tab above the table. It is turned off by default.

If you turn it on and Save settings, data from your source file will be saved in the database, and on every page load, the data in the table will be populated from the cache, not from the source file. This is very useful if you have static data in some of the source files that do not need to be updated.

Auto update cacheInstructions how to set auto update option

On the other hand, if you are updating your source file frequently, and want to keep the data in the table updated after a custom interval, the new feature Auto update cache from the source file will be perfect for you. It will update table cached data based on the source file following the time interval that you will set in the cron job that we will explain later.

By default, it is not visible when you turn on the Cache data option.

Let’s dive into how you can set it up.

cache_settings

First, you will need to access the Main settings of the plugin.

Go to wpDataTables -> Settings, and switch on the new tab Cache settings. You will see a new option Auto update cache for tables, which is turned off by default.

cron_instructions

When you turn it on, detailed instructions will be shown below on how you can set it to work properly. Later below, we will explain those instructions in detail in the section Setup cron job. Click Save settings and return to your created table.

auto_update_table_option

When you visit your table, created from the beginning of this tutorial, and refresh the page, you will see a new option Auto update cache on the Display tab in table settings beside the Cache data option. It is turned off by default.

When you turn it on and click Save Changes, your table will be updated from your source file based on your cron setup, which you will see below.

Auto update cache can be very useful for tables created from some API-s that have limited access by user, time, or other conditions.

One of them is the Google sheet API which has free usage with limited access and with this feature you are able to create tables with Google Sheet API where you will turn on Cache data and Auto update cache options and set (for example) a 15-minute interval with a cron job that will update cache data in the table. Like this, calls to Google sheet API are reduced to only 4 for one hour and you are able to use it for free, without the need to buy additional quotes or to get tables without data because the limit is exceeded. Tables will be loaded very fast from the cache for all users.

Setup Cron jobInstructions how to set properly cron job on your server

First on the “Data source” tab in table settings, the new options “Cache Data” and “Auto update cache” need to be turned on.

Auto update cache for tables requires a bit of additional configuration with your server cron scheduler. If you want to run a cron (do auto update cache) every 15 minutes, copy the command below and paste it into the cron file.

*/15 * * * * your.website.url/wp-admin/admin-ajax.php?action=wdtable_update_cache&wdtable_cache_verify=your.cache.verify.string

If that doesn’t work, please try these as well:
this

*/15 * * * * wget -q -O – "your.website.url/wp-admin/admin-ajax.php?action=wdtable_update_cache&wdtable_cache_verify=your.cache.verify.string"

or this

*/15 * * * * /usr/local/bin/php ~/public_html/wp-cron.php –action='wdtable_update_cache' –wdtable_cache_verify='your.cache.verify.string'

or this

GET 'your.website.url/wp-admin/admin-ajax.php?action=wdtable_update_cache&wdtable_cache_verify=your.cache.verify.string' > /dev/null

If you need a different schedule time, you can see the more detailed tutorial on how to set up cron jobs on this link.

If you are not familiar with this, you can use the plugin WP Crontrol for creating a cronjob.

First, copy this: your.website.url/wp-admin/admin-ajax.php?action=wdtable_update_cache&wdtable_cache_verify=your.cache.verify.string

Then, go to Tools -> Cron Events admin panel, and click on the “Cron Schedules” tab. In it, add a 15-minute interval.

Now go back to the “Cron Events” tab, and click on “Add New”.

Below it, select the “PHP Cron event” radio button, and add the URL you copied:

file_get_contents("your.website.url/wp-admin/admin-ajax.php?action=wdtable_update_cache&wdtable_cache_verify=your.cache.verify.string");

You don’t need to provide the PHP opening tag (“<?php”).

Set the Event name, and set the next run to be “At”, and a few minutes in the future. Select the recurrence (the event you created as the first step, in WP Crontrol – the “every_15_minutes” interval) and that’s it.

IMPORTANT: If you copy those URLs, you will need to replace “your.website.url” and “your.cache.verify.sting” with your website URL and with your cache verify string that you can find on the “Cache settings” tab in the Main settings of the plugin.

Please note that the table cache will not be updated properly if:

  1. The source file doesn’t have the same number of columns
  2. The source file doesn’t have the same names of columns
  3. The source file is empty
  4. The source file doesn’t exist anymore
  5. Any errors are triggered due to reading the file or getting data from the URL

If Auto update is not working properly, please check the steps for troubleshooting on the Cache settings tab in the Main settings of the plugin at the bottom of the page.