Updating an existing table programmatically

Hi,

thanks for your post, and sorry for the trouble.

For this, I suggest looking into using a plugin filter hook in some custom PHP code. For example, you could use the filter hook tablepress_table_render_data, see https://github.com/TobiasBg/TablePress/blob/6383ba8d874e256b7f39f73719de9b57f8409ae3/classes/class-render.php#L258, to get the table data before it’s printed to the page. There, you could replace it with the data from your API calls.

Regards,
Tobias

I’ve just come to the forum to ask a very similar question. I did this on the front-end, using an ajax call to get the data and then populating a specific column. This works to populate the table cells, but sorting on that column is broken. Is there something you can do with javascript to refresh existing data from the table for sorting?

Hi,

you would need to call the DataTables JS library again, see the JS that TablePress adds to the footer of the page.

Regards,
Tobias

To do this, I made a wordpress plugin with a daily schedule and an admin page with button to update.

Mine goes to a remote database and queries it, but you can easily adapt it for other sources.

Note: My admin page is a subpage of one of my other plugins admin pages.
Also might have some issue with the visibility metadata I’m not updating it yet.

<?php

/*
Plugin Name: Update Datatables
Plugin URI:
Description: Uses Remote Service Intergration to get a remote database handle.
Runs hard-coded querys against remote DB updating hard-coded TablePress table ids.
Version: 1.0
Author: Liam Mitchell
Author URI: https://github.com/LiamKarlMitchell
License: MIT
*/

// On plugin activation schedule our daily datatable update.
register_activation_hook( __FILE__, 'vt_create_daily_datatable_schedule' );
function vt_create_daily_datatable_schedule(){
  // Use wp_next_scheduled to check if the event is already scheduled
  $timestamp = wp_next_scheduled( 'vt_create_daily_datatable' );

  // If $timestamp == false schedule daily datatables since it hasn't been done previously
  if( $timestamp == false ){
    //Schedule the event for right now, then to repeat daily using the hook 'vt_create_daily_datatable'
    wp_schedule_event( time(), 'daily', 'vt_update_daily_datatables_hook' );
  }
}

register_deactivation_hook( __FILE__, 'vt_remove_daily_datatable_schedule' );
function vt_remove_daily_datatable_schedule(){
    wp_clear_scheduled_hook( 'vt_update_daily_datatables_hook' );
}

add_action('admin_menu', 'vt_register_submenu_page');

function vt_register_submenu_page() {
    add_submenu_page('remote-server-integration/remote-server-integration-admin-page.php', 'Datatables', 'Datatables', 'manage_options', __FILE__.'/datatables', 'vt_datatables_admin_page');
}

function vt_datatables_admin_page() {
    // TODO: Add nonce to form?
    // https://codex.www.ads-software.com/WordPress_Nonces
    ?>
    <p>Data tables are hard-coded into the update-my-datatables.php.<br>Queries are run against the remote database for a service daily and the appropriate data tables have their data updated.</p>
    <form action="<?php echo admin_url('admin-post.php'); ?>" method="post">
        <input type="hidden" name="action" value="vt_update_daily_datatables">
        <input type="submit" value="Update datatables.">
    </form>
    <?php
}

add_action( 'admin_post_vt_update_daily_datatables', 'vt_update_daily_datatables_from_admin' );

function vt_load_table( $table_id ) {
    if ( ! TablePress::$model_table->table_exists( $table_id ) ) {
        return false;
    }
    // Load the table.
    $table = TablePress::$model_table->load( $table_id, true, true );
    // Skip tables that could not be loaded or that are corrupted.
    if ( is_wp_error( $table ) || ( isset( $table['is_corrupted'] ) && $table['is_corrupted'] ) ) {
        return false;
    }
    return $table;
}

function vt_save_table( $table ) {
    $result = TablePress::$model_table->save($table);
    if (is_wp_error($result)) {
        // TODO: Log error?
    }
}

function vt_update_daily_datatables_from_admin() {
    define( 'WP_DEBUG' , true);
    define( 'WP_DEBUG_DISPLAY' , true);
    define('WP_DISABLE_FATAL_ERROR_HANDLER',true);
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);

    // TODO: NONCE? https://premium.wpmudev.org/blog/handling-form-submissions/
    vt_update_daily_datatables();

    die('done');
    wp_safe_redirect(
        esc_url_raw(
            add_query_arg( 'tablesupdated', 'success', admin_url('admin.php?page=update-my-datatables%2Fupdate-my-datatables.php%2Fdatatables') )
        )
    );
    exit();
}

add_action('vt_update_daily_datatables_hook', 'vt_update_daily_datatables');
function vt_update_daily_datatables(){
    vt_table_update();
}

function vt_table_update() {
    global $rsi;
    // TODO: If user is admin on admin page show log to page?
    $table = vt_load_table(1);
    if ($table === false) {
        // Error loading table.
        return;
    }
    // Get connection to Database.
    $db = $rsi->getDBForService('another_remote_mysql_database');
    if ($db === false) {
        // Error connecting to DB?
        return;
    }
    // var_dump($db->get_results("SELECT columns from table etc.", ARRAY_N);

    // If an array was returned and no errors occurred, return the result set
    if (is_array($rows) && empty($db->last_error)) {
        $rowcount = count($rows);
        // Put rank number on manually as row_number method does not work in our version of MariaDB.
        for ($i=0; $i < $rowcount; $i++) {
            $rows[$i][0] = strval($i + 1);
        }

        // Make a new array containing the table headers and the rows.
        $table['data'] = array_merge([$table['data'][0]], $rows);
        // Save table.
        vt_save_table($table);
    }
//    else {
//        // echo $db->last_error;
//    }
}

• This reply was modified 4 years, 4 months ago by liammitchell.
• This reply was modified 4 years, 4 months ago by liammitchell.
• This reply was modified 4 years, 4 months ago by liammitchell.

Thanks liammitchell

Hi,

nice idea, thanks for sharing this!

As for updating the wp_postmeta, also see the prepare() method, maybe.

Regards,
Tobias

Sorry, it ran fine from admin but not via the cron schedule.

@tobiasbg , I’ll certainly check the prepare method thank you ?? I guess I just have to build the array of 1’s for each row I want to show (all in this case). At the beginning of this I was not aware that we could hide rows but ran into it as you can tell from my other post, which now I can see is because I didn’t update the visibility array when updating automatically! Doh.

Maybe something like this would work in my case to show all rows and columns. Untested…
I assume that at least the header row was set up via the tablepress admin and I retain it each import.

$table['visibility'] = array( 'rows' => array_fill(0, count($table['data']), 1), 'columns' => array_fill(0, count($table['data'][0]), 1) );

TablePress::$model_table->table_exists was silently failing (no errors in debug.log or error log so weird)

I found in another extension the Table Auto Import that we can load specific classes/models, this makes sense to me now as the TablePress likely does not run functions/load extra things when running on cron as it does not run in cron normally without other extensions and would not want to create unnecessary complexity for a WP site should it not be needed to load.

https://tablepress.org/extensions/table-auto-import/

I had to get the table model like this.
$model_table = TablePress::load_model( 'table' );

Redacted code sample:

<?php

/*
Plugin Name: VT Datatables
Plugin URI:
Description: Uses Remote Service Intergration to get a remote database handle.
Runs hard-coded querys against remote DB updating hard-coded TablePress table ids.
Version: 1.0
Author: Liam Mitchell
Author URI: https://github.com/LiamKarlMitchell
License: MIT
*/

// On plugin activation schedule our daily datatable update.
register_activation_hook( __FILE__, 'vt_create_daily_datatable_schedule' );
function vt_create_daily_datatable_schedule(){
  // Use wp_next_scheduled to check if the event is already scheduled
  $timestamp = wp_next_scheduled( 'vt_create_daily_datatable' );

  // If $timestamp == false schedule daily datatables since it hasn't been done previously
  if( $timestamp == false ){
    //Schedule the event for right now, then to repeat daily using the hook 'vt_create_daily_datatable'
    wp_schedule_event( time(), 'daily', 'vt_update_daily_datatables_hook' );
  }
}

register_deactivation_hook( __FILE__, 'vt_remove_daily_datatable_schedule' );
function vt_remove_daily_datatable_schedule(){
    wp_clear_scheduled_hook( 'vt_update_daily_datatables_hook' );
}

add_action('admin_menu', 'vt_register_submenu_page');

function vt_register_submenu_page() {
    add_submenu_page('remote-server-integration/remote-server-integration-admin-page.php', 'Datatables', 'Datatables', 'manage_options', __FILE__.'/datatables', 'vt_datatables_admin_page');
}

function vt_datatables_admin_page() {
    // TODO: Add nonce to form?
    // https://codex.www.ads-software.com/WordPress_Nonces
    ?>
    <p>Data tables are hard-coded into the vt.php.<br>Queries are run against the remote database for a service daily and the appropriate data tables have their data updated.</p>
    <form action="<?php echo admin_url('admin-post.php'); ?>" method="post">
        <input type="hidden" name="action" value="vt_update_daily_datatables">
        <input type="submit" value="Update datatables.">
    </form>
    <?php
}

add_action( 'admin_post_vt_update_daily_datatables', 'vt_update_daily_datatables_from_admin' );

function vt_load_table( $table_id ) {
    vt_log(__FUNCTION__." load table running $table_id");
    vt_log(__FUNCTION__." load table check if exists $table_id");
    try {
        $model_table = TablePress::load_model( 'table' );
        if (!$model_table->table_exists($table_id)) {
            vt_log(__FUNCTION__ . " table does not exist.");
            return false;
        }
    } catch (Exception $e) {
        vt_log(__FUNCTION__." exception ".$e->getMessage());
        return false;
    }
    // Load the table.
    vt_log(__FUNCTION__." load table $table_id");
    $table = $model_table->load( $table_id, true, true );
    // Skip tables that could not be loaded or that are corrupted.
    if ( is_wp_error( $table ) || ( isset( $table['is_corrupted'] ) && $table['is_corrupted'] ) ) {
        vt_log(__FUNCTION__." table could not load.");

        vt_log(__FUNCTION__." load table wp_error ".$table->get_error_message());
        return false;
    }
    vt_log(__FUNCTION__." table loaded.");
    return $table;
}

function vt_save_table( $table ) {
    vt_log(__FUNCTION__." saving table.");
    $model_table = TablePress::load_model( 'table' );
    $result = $model_table->save($table);
    if (is_wp_error($result)) {
        // TODO: Log error?
        vt_log(__FUNCTION__." error saving table.");
        vt_log(__FUNCTION__." save table wp_error ".$result->get_error_message());
    }
}

function vt_update_daily_datatables_from_admin() {
    define( 'WP_DEBUG' , true);
    define( 'WP_DEBUG_DISPLAY' , true);
    define('WP_DISABLE_FATAL_ERROR_HANDLER',true);
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);

    // TODO: NONCE? https://premium.wpmudev.org/blog/handling-form-submissions/
    vt_update_daily_datatables();

    die('done');
    wp_safe_redirect(
        esc_url_raw(
            add_query_arg( 'tablesupdated', 'success', admin_url('admin.php?page=vt%2Fvt.php%2Fdatatables') )
        )
    );
    exit();
}

// Note: This is just a log function made to get some output out of the extension whilst I was debugging the problem.
function vt_log($message) {
    file_put_contents(WP_CONTENT_DIR . '/vt-debug.txt', current_time('Y-m-d h:m:s').' '.$message."\n", FILE_APPEND);
}

add_action('vt_update_daily_datatables_hook', 'vt_update_daily_datatables');
function vt_update_daily_datatables(){

//    /**
//     * This will log all errors notices and warnings to a file called debug.log in
//     * wp-content (if Apache does not have write permission, you may need to create
//     * the file first and set the appropriate permissions (i.e. use 666) )
//     */
//    define('WP_DEBUG', true);
//    define('WP_DEBUG_LOG', true);
//    define('WP_DEBUG_DISPLAY', true);
//    @ini_set('display_errors',0);
// Note: THIS DID NOT WORK!

    //wp_mail( 'PutYourEmailHereForDebugPurposes', 'Testing Cron', 'Cron is running' );
    vt_log(__FUNCTION__." running.");
    try {
        // Level Ranking
        vt_level_table_update();
    } catch (Exception $e) {
        vt_log($e->getMessage());
    }
    //wp_mail( 'PutYourEmailHereForDebugPurposes', 'Testing Cron', 'Cron finished' );
}

function vt_level_table_update() {
    vt_log(__FUNCTION__." running.");
    global $rsi;
    // TODO: If user is admin on admin page show log to page?
    vt_log(__FUNCTION__." loading table.");
    $table = vt_load_table(1);
    if ($table === false) {
        // Error loading table.
        vt_log(__FUNCTION__." error loading table.");
        return;
    }
    vt_log(__FUNCTION__." connecting to db.");
    // Get connection to Database.
    $db = $rsi->getDBForService('vsky2');
    if ($db === false) {
        // Error connecting to DB?
        vt_log(__FUNCTION__." error connecting to remote db.");
        return;
    }
    // var_dump($db->get_results("SELECT VERSION();"));
    // Note: ROW_NUMBER() OVER does not work in this version of Maria DB we need 10.2 or higher we are on 10.1.38-MariaDB
    // https://stackoverflow.com/questions/57765698/problem-using-row-number-function-in-mariadb
    $rows = $db->get_results("SELECT A B C FROM SomeTable", ARRAY_N);

    // If an array was returned and no errors occurred, return the result set
    if (is_array($rows) && empty($db->last_error)) {
        $rowcount = count($rows);
        // Put rank number on manually as row_number method does not work in our version of MariaDB.
        for ($i=0; $i < $rowcount; $i++) {
            $rows[$i][0] = strval($i + 1);
        }

        // Make a new array containing the table headers and the rows.
        $table['data'] = array_merge([$table['data'][0]], $rows);
        vt_log(__FUNCTION__." saving table.");
        // Save table.
        vt_save_table($table);
    } else {
        vt_log(__FUNCTION__." DB Error: ".$db->last_error);
    }
}

Example screenshot:
[img]https://i.imgur.com/jwrYHUj.png[/img]

Thanks again ??

• This reply was modified 4 years, 4 months ago by liammitchell.

Hi,

yes, when using Cron, you’ll need to load a few things by hand. Good to hear that you found all this ??

Best wishes,
Tobias