database interactions using OOP

Question

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.

Related Posts: