Tag Archives: wpdb

$wpdb queries not working properly on child sites in WordPress multisite install

1 Reply

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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'
        ) 
    );
}
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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();
}
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.