database interactions using OOP

Question

I'm developing wordpress plugin which store data in database. I tried to start OOP way for my plugin according to boilerplate from Best Practicies: https://github.com/DevinVinson/WordPress-Plugin-Boilerplate/tree/master/plugin-name

Thing I can't understand is how to call creating tables method only one time - on plugin installation event and make all interactions secure. Also, I want to schedule some load tasks via cron.

I figured out that there are two ways for first goal:

• Make all methods public static so db loader class become just wrapper around wpdb instance. But it's not enough secure, isn't it?

• Initialize db loader instance every time when plugin needs. (with private methods and so on)

The question is how to initialize db loader class instance correctly to create all tables only one time and avoid public static methods?

This is code from boilerplate:

    /**
    * Core plugin class
    */
    require_once plugin_dir_path(__FILE__) . 'includes/class_my_plugin.php';


    function run_my_plugin() {
        $plugin = new my_plugin();
        $plugin->run();
    }

    run_my_plugin();

It's main plugin class example:

class my_plugin {

private
    $wpdb,
    $table_prefix,
    $charset_collate;


public function __construct() {
    global $wpdb;

    $this->wpdb = &$wpdb;
    $this->table_prefix = $this->wpdb->prefix . 'test_private_';
    $charset_collate = $this->wpdb->get_charset_collate();

}

public function run() {

    $table_name = $this->table_prefix . 'desks';


    $sql = "CREATE TABLE IF NOT EXISTS $table_name ( 
    some_id int(11) NOT NULL AUTO_INCREMENT,
    some_title VARCHAR(50) NOT NULL,
    PRIMARY KEY  (desk_id)
    ) $this->charset_collate;";

    dbDelta($sql);

}

}

Answer 1

This is potentially a fairly involved question, so I may not be totally rigorous in my answer, but this should give you a good start when creating and deleting tables..

Include the following lines in your plugin _construct() function of class_my_plugin.php:

if (is_admin()){
    //manage the the database tables when registering or deactivating
    register_activation_hook($loc, array($this, 'your_plugin_installation'));
    register_deactivation_hook($loc, array($this, 'your_plugin_unset'));
    register_uninstall_hook ($loc, 'your_plugin_uninstall');
}

Include these functions in class_my_plugin.php within the class itself:

function your_plugin_installation() {
    create_data_tables();
}

function your_plugin_unset() {
    //do whatever here for users that deactivate the plugin
}

Outside the main plugin class add these functions:

function your_plugin_uninstall() {
    delete_db_tables();
    //add whatever else you need to do to clean up
}

function create_data_tables() {
    global $wpdb;
    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    $table_name = $this->table_prefix . 'desks';
    $charset_collate = $wpdb->get_charset_collate();
    if( $wpdb->get_var("SHOW TABLES LIKE '".$table_name."'") != $table_name ) {
       $sql = "CREATE TABLE IF NOT EXISTS ".$table_name;
       $sql .= " (some_id int(11) UNSIGNED NOT NULL AUTO_INCREMENT, ";
       $sql .= "some_title varchar(31) NOT NULL DEFAULT '', ";
       $sql .= "PRIMARY KEY (some_id)) ".$charset_collate;
        dbDelta( $sql );
    }
    // repeat the if {} statement for each table you want to create
}

function delete_db_tables() {
    global $wpdb;
    $table_name = $this->table_prefix . 'desks';
    $wpdb->query( "DROP TABLE IF EXISTS ".$table_name );
    //delete your tables here
}

Note that I usually place your_plugin_uninstall outside of the main class as you will get an error if it is inside the main class when a user uninstalls.

This will help you to create and delete the tables when the user installs and uninstalls. This therefore will happen only once and the functions will be ignored unless the install or uninstall hooks are fired.

The tables can be easily accessed using standard WP funtionality e.g.

function so_get_some_title($id,) {
    global $wpdb;
    $id = (int) $id;
    $query = "SELECT some_id, some_title FROM ".$table_name." WHERE some_id = ".$id;
    $recs = $wpdb->get_results($query,ARRAY_A);
    }
    return $recs;
}

I hope that I have interpreted your question correctly and that this helps in some way.