How to connect or switch WordPress to a third-party database

This question can be found all around the web.

The problem position is that you need to retrieve data from a third-party Database or a third-party WordPress System.

Most people use in this case a new instance of the wpdb class. This gives you the possibility to run general queries and all possibilities of the wpdb class.

/**
 * Switch to third-party database by using the WordPress wpdb class
 */
$my_wpdb = new wpdb('username', 'password', 'database', 'host');

In most scenarios this way is sufficient, even if we have an other instance in our runtime.
But if you are connected to a third-party WordPress System, wouldn’t it be nice to use also the whole functionality auf the WordPress core system?

This can be made easy by using just a simple function, that forces your whole WordPress System to run with the third-party Database. Then you are still be able to use all WP functions like get_post, etc. to retrieve data from the third-party system.

First you should define the third-party database connection data as Constants in your wp-config file.  You can also just replace the constants with the values directly in the function.

/**  The name of the third-party database */
define('THIRD_PARTY_DB_NAME', '');

/** MySQL third-party database username */
define('THIRD_PARTY_DB_USER', '');

/** MySQL third-party database password */
define('THIRD_PARTY_DB_PASSWORD', '');

/** MySQL third-party hostname */
define('THIRD_PARTY_DB_HOST', '');

/** Set WP-DB Prefix of the third-party WordPress System */
define('THIRD_PARTY_DB_PREFIX', '');

Now we build up the function to switch to the third-party database and back (use your favorite prefix):

/**
 * prefix_switch_wpdb
 *
 * Switch to third-party Database
 * Detect active Database Connection if not given
 * Override global wpdb to work with all WordPress Functions on third-party databas
 * Flush WordPress Cache to ReInit Database Connection
 *
 * @param string $direction *optional vars 'wp' => home system, 'third_party' => third-party system
 * @return void
 */
function prefix_switch_wpdb($direction = '') {

	global $wpdb, $table_prefix;

	if(empty($direction))
		$direction = $table_prefix == $wpdb->prefix && DB_NAME == $wpdb->dbname ? 'third_party' : 'wp';

	switch($direction) {
		case 'wp':
			$wpdb = new \wpdb( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );
			$wpdb->set_prefix( $table_prefix );
			break;
		case 'third_party':
			$wpdb = new \wpdb( THIRD_PARTY_DB_USER, THIRD_PARTY_DB_PASSWORD, THIRD_PARTY_DB_NAME, THIRD_PARTY_DB_HOST );
			$wpdb->set_prefix( THIRD_PARTY_DB_PREFIX );
			break;
	}

	wp_cache_flush();

}

With calling the function prefix_switch_wpdb() you are connected to another database and use the global $wpdb object to make general queries.
To return to the Host WordPress System just call prefix_switch_wpdb() again.

If the optional $direction value is not set, the function detects wether you are connected to a third-party system or not. If connected to another database, you return automaticly to your home system by calling the function.

Two easy examples how to use:

/**
 * Switch to third-party database and just use $wpdb object
 */	

// First Query
$siteurl = $wpdb->get_row("SELECT option_value FROM " . $wpdb->prefix . "options WHERE option_name = 'siteurl'");
echo siteurl->option_value;
// will deliver your current siteurl.

// switch database and run the query again
prefix_switch_wpdb();
$siteurl = $wpdb->get_row("SELECT option_value FROM " . $wpdb->prefix . "options WHERE option_name = 'siteurl'");
echo siteurl->option_value;
// the result is the siteurl from the third-party database

// switch database and run the query again
prefix_switch_wpdb();
$siteurl = $wpdb->get_row("SELECT option_value FROM " . $wpdb->prefix . "options WHERE option_name = 'siteurl'");
echo siteurl->option_value;
// the result delivers as used your current siteurl.

/**
 * Switch to third-party database and just Use WordPress functionality
 * If you are connected to another WordPress system, you can youse all WordPress functions
 */	

// First function call
echo get_option('siteurl');
// will deliver your current siteurl.

// switch database and call get_option('siteurl') again
prefix_switch_wpdb();
echo get_option('siteurl');
// the result is the siteurl from the third-party WordPress System

// switch database ahain and call get_option('siteurl')
prefix_switch_wpdb();
echo get_option('siteurl');
// the result delivers as used your current siteurl.