I’m working on a WordPress multisite plugin that uses a global table to store logs. When a user performs a certain action on my site, I’m executing a function similar to this one:

function add_log($source, $user_id, $action, $status) {
  global $wpdb, $blog_id;
  $table_name = $wpdb->prefix . LOG_TABLE;
  $IP = $_SERVER['REMOTE_ADDR'];
  $result = $wpdb->insert($table_name, 
    array(
      'source' => $source,
      'site_id' => $blog_id,
      'user_id' => $user_id,
      'action' => $action,
      'status' => $status,
      'IP' => $IP
    ), 
    array(
      '%s',
      '%d', 
      '%d',
      '%s',
      '%s',
      '%s'
    ) 
  );
}

This works well on the base site (where blog_id = 1) of the WordPress multisite install, but for some reason it wasn’t capturing any logs on the other sites.

After some troubleshooting, which I did by modifying the function temporarily to this:

function add_log($source, $user_id, $action, $status) {
  global $wpdb, $blog_id;
  define('DIEONDBERROR', true);
  $wpdb->show_errors();
  $table_name = $wpdb->prefix . LOG_TABLE;
  $IP = $_SERVER['REMOTE_ADDR'];
  $result = $wpdb->insert($table_name, 
    array(
      'source' => $source,
      'site_id' => $blog_id,
      'user_id'	=> $user_id,
      'action' => $action,
      'status' => $status,
      'IP' => $IP
    ), 
    array(
      '%s',
      '%d', 
      '%d',
      '%s',
      '%s',
      '%s'
    ) 
  );
  $wpdb->print_error();
}

I found out that $wpdb->prefix was not always wp_ as I expected, but it was wp_2_ or wp_3_, depending on the site the log was generated on. That actually makes a whole lot of sense.

The proper way to fix this is to indicate that you want the base prefix, not the individual site’s prefix.

The solution is to change $wpdb->prefix to $wpdb->base_prefix.

PS: If you are creating a plugin that will work on both single and multisite WordPress installs, it’s helpful to know that $wpdb->base_prefix is set on both of those versions, meaning you can always use it.

Featured image by Hal Gatewood.

Comments (1)

Previously posted in WordPress and transferred to Ghost.

Zaheer Abbas
November 30, 2020 at 9:13 am

Great. I was working on an insert query for 3 hours it was very simple but was not working. So the issue was using $wpdb->prefix instead of $wpdb->base_prefix.

Thanks for your help.