Cant create table on plugin activation

Question

I am trying to create table on plugin activation, I tried making the same function inside the class and calling it from the constructor, and I tried calling it outside the class and finally i tried to make another php file for calling it using the register_activation_hook, but nothing worked

plugin-code:

<?php
/**
*@package pageauthors
**/

/** 
* Plugin Name: Page Authors
* Plugin URI: https://www.localhost.com/plugin
* Description: Allows you to add multiple authors to your wordpress post.
* Version: 1.0.0 
* Author: Raphael Eid.
* Author URI: https://www.localhost.com/
* License: GPL v2 or Later 
* Text Domain: PageAuthors
*/

$db = mysqli_connect('localhost','x','x','x');
//We can use require_once('../../../wp-config.php');
//mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

defined('ABSPATH') or die('Error my friend');

class pageauthors{

    function register(){
        //Call the Scripts
        add_action('admin_enqueue_scripts', array($this, 'enqueue'));

        //Create in the admin function
        add_action('admin_menu', array($this, 'add_admin_pages'));
    }   
    
    public function add_admin_pages(){
        //add_menu_page( string $page_title, string $menu_title, string $capability, string $menu_slug, callable $function = '', string $icon_url = '', int $position = null )
        add_menu_page('Add Author', 'Add Author', 'manage_options', 'raphaelprefix_plugin', array($this, 'admin_index'), 'dashicons-insert', 110);
    }

    public function admin_index(){
        define( 'MY_PLUGIN_PATH', plugin_dir_path( __FILE__ ) );
        include( MY_PLUGIN_PATH . 'templates/index.php');
        // include( MY_PLUGIN_PATH . 'index.php');
        include( MY_PLUGIN_PATH . 'templates/showAuthors.php');
        // require_once plugin_dir_path(__FILE__) . 'templates/index.php';
    }    

    function deactivate(){
        global $wpdb;
        $table_name = $wpdb->prefix . 'postsauthors';
        $sql = "DROP TABLE IF EXISTS $table_name";
        $wpdb->query($sql);
        delete_option("my_plugin_db_version");
    }

    function enqueue(){
        //Here we can enqueue and we should create the assets (css and js)
        wp_enqueue_style('mypluginstyle', plugins_url('/assets/mystyle.css', __FILE__) );
        wp_enqueue_script('mypluginstyle', plugins_url('/assets/mystyle.js', __FILE__) );
    }

}

if(class_exists('pageauthors')){
    $raphplugin = new pageauthors();
    $raphplugin->register();
    // $raphplugin->activate();
    $raphplugin->deactivate();

}

function installer(){
    include('installer.php');
}

//Activate - Register Activation Hook(__FILE__, array($instance, function))
register_activation_hook(__FILE__, 'installer');

//Deactivate - Register Deactivation Hook(__FILE__, array($instance, function))
register_deactivation_hook(__FILE__, array($raphplugin, 'deactivate') );

?>

installer.php

<?php
$db = mysqli_connect('localhost','x','x','x');
    global $wpdb;
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

    if (count($wpdb->get_var('SHOW TABLES LIKE "wp_postsauthors"')) == 0){

        $sql = "CREATE TABLE `wp_postsauthors` 
        ( `ID` INT(200) NOT NULL AUTO_INCREMENT , `post_ID` INT(200) NOT NULL , 
        `author_name` VARCHAR(200) NOT NULL , `title` VARCHAR(200) NOT NULL , 
        `description` VARCHAR(200) NOT NULL , PRIMARY KEY (`ID`)) ENGINE = InnoDB;";

dbDelta($sql);

}

Nothing is working even tho I am typing everything correctly

Isolate your problems first. Ignoring table creation completely, does you `register_activation_hook` even fire? It doesn't matter what it does, that's what you first need to debug. Throw a giant error message in there instead of doing anything complex. Looking at your code `register_activation_hook(__FILE__, 'installer');` is invalid. The callback must a PHP callable. If you have a function named `installer`, that code would work, but I'm not seeing that. That name is also pretty generic and should be bound to a namespace, too. — Chris Haas, Apr 06 '21 at 14:06
@ChrisHaas yes i have the ```installer``` function, up in the plugin code, you can see the function that contains ```include(installer.php)``` — Raphael Eid, Apr 06 '21 at 14:07
I apologize, I see that now. Don't include anything, instead throw a giant error message in there. Then try activating your plugin and see if you see an error message in WordPress. — Chris Haas, Apr 06 '21 at 14:10
@ChrisHaas i echoed a test message in the function and everything worked well, now i think that the problem is in my code, but mine is from stackoverflow tho and worked on it — Raphael Eid, Apr 06 '21 at 14:12
I think the problem might be in your `if` statement. You are getting a variable using `get_var` which will return a scalar, and then your are passing this to the `count()` function. Personally, I'd just recommend using [`CREATE TABLE IF NOT EXISTS`](https://stackoverflow.com/q/1650946/231316) in activation, but you can change the `if` logic, too. Instead of `if`, assign the value of `$wpdb->get_var` to a variable and inspect it before and after the table exists. Then you can add that logic back to the `if`. Also, don't mix `wpdb` and `mysqli_connect`, it is asking for trouble. — Chris Haas, Apr 06 '21 at 14:19
@ChrisHaas I commented the ```if count``` and in the ```sql``` query I used ```CREATE TABLE IF NOT EXISTS `wp_postsauthors``` but still nothing worked — Raphael Eid, Apr 06 '21 at 14:21
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/230807/discussion-between-raphael-eid-and-chris-haas). — Raphael Eid, Apr 06 '21 at 14:23

score 2 · Accepted Answer · answered Apr 06 '21 at 14:39

I'm going to simplify things as much as possible. Activation hooks are always frustrating to debug because of their async-like behavior.

First, you actually don't need to if check on whether the table exists, that's what dbDelta does for you already. You can actually change your schema that's defined in $sql later and dbDelta will attempt to figure that out and handle things automatically for you, too. In the real world, however, I have found that semi-complex table logic doesn't always make it through to an ALTER TABLE. Instead, most people keep track of their schema version and run table upgrades on plugin update. But that's for later.

Second, never assume the WordPress table prefix, that is asking for trouble some day. Instead, always use $wpdb->prefix.

The code below is the smallest version of a plugin that does what you are looking for. Instead of a function in a file, I'm just using a local anonymous function. The bulk of the code is the same as yours, just with some formatting that my IDE does for me automatically. This code is a full plugin which I'd recommend testing first. Once you've confirmed it is working, you should be able to pull the activation logic out. It is not wrong to use another file, I just try to remove as much distraction when I debug things.

<?php

/**
 * Plugin Name: Page Authors
 */

register_activation_hook(
    __FILE__,
    static function () {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // Never assume the prefix
        $table_name = $wpdb->prefix . 'postsauthors';

        $sql = "CREATE TABLE `${table_name}` 
                (
                    `ID` INT(200) NOT NULL AUTO_INCREMENT ,
                    `post_ID` INT(200) NOT NULL ,
                    `author_name` VARCHAR(200) NOT NULL ,
                    `title` VARCHAR(200) NOT NULL ,
                    `description` VARCHAR(200) NOT NULL ,
                    PRIMARY KEY (`ID`)) ENGINE = InnoDB;";

        dbDelta($sql);
    }
);

I think the problem is from my localhost, phpmyadmin, this should work and my code above should also work, I tried manually to create a table and then after minutes it got deleted. I think the problem is on my machine — Raphael Eid, Apr 06 '21 at 14:47

Cant create table on plugin activation

1 Answers1