kevmwg
Last Updated: February 25, 2016
·
545
· merianos

WP Custom Plugin/Theme with auto database tables update

Currently I am building a personal project for a business directory based on WordPress, and it is required to create my own table for some sections of my site.

Thankfully, WordPress comes with an amazing function called dbDelta. This function it is great, but it is hangry for server resources.

One way to use it, is to run this function on page loat, but this is not a good practice. To overcome this issue the developer should use Database Versioning with some constant variable.

The other way, is my way :)

function manipulate_database_tables()
{
    global $wpdb;

    if(!function_exists('dbDelta'))
    {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }

    $def_queries = get_option('my_app_default_queries', array());

    $sql_queries = array();

    $my_table_query = 'create_table_sql_statement';
    $sql_queries[md5($my_table_query)] = $my_table_query;

    $my_another_table_query = 'create_table_sql_statement_for_another_table';
    $sql_queries[md5($my_another_table_query)] = $my_another_table_query;

    foreach($sql_queries as $md5 => $query)
    {
        if(isset($def_queries[$md5]))
        {
            continue;
        }

        dbDelta($query);
    }

    update_option('my_app_default_queries', $sql_queries);
}

manipulate_database_tables();

By using my way, you can insert this function in your functions.php of your theme or in your database related file of your plugin, and run it on every page load.

In this case, the dbDelta will run only for the modified SQL Statements,