Handling multiple databases in WordPress

January 22, 2012,  

There are situations comes when you want to handle multiple databases but in wordpress environment. The wordpress database configuration is configured in wp-config.php file, which holds defined variables like DB_NAME, DB_USER, DB_PASSWORD, DB_HOST. Where we have to define wordpress database name for DB_NAME variable.

Connect and fetch data from another database

To get the data from another database will need the some basic configuration.

$db_name = 'another_database';
$db_user = 'root';
$db_pass = '';
$db_host = 'localhost';

If you want to keep your included files generic to all the environments ie. Local, QA, Production you will have to extend the connection by adding IF-ELSE conditions in another-db.php file:

# DB CONNECT
$db_name = 'local_db_name';
$db_user = 'local_db_user';
$db_pass = 'local_db_pass';
$db_host = 'localhost';

$host = $_SERVER['HTTP_HOST'];
if($host == 'qa.yoursite.com'){
  $db_name = 'qa_db_name';
  $db_user = 'qa_db_user';
  $db_pass = 'qa_db_pass';
}elseif($host == 'www.yoursite.com'){
  $db_name = 'prod_db_name';
  $db_user = 'prod_db_user';
  $db_pass = 'prod_db_pass';
}

$mysql_con = @mysql_connect($db_host, $db_user, $db_pass);
if (!$mysql_con){
  die('Could not connect');
}
$mysql_db = mysql_select_db($db_name, $mysql_con);
if (!$mysql_db){
  die('Could not connect to database');
}

This makes your MySQL database connection configuration easy and one time.

Problem

The headache comes after this another database connection! Once the data got fetched from another database the WordPress then excludes its all queries and does not show the post content! Even if you call PHP’s mysql_close(), include_once() or require_once() functions, it does not benefited to get wordpress data.

The jQuery’s load function also does not solve this problem

<script type="text/javascript">
  $(function(){
    $('div#another_db_data').load('another-db-content.php');
  });
</script>

Solution: Reconnect WP DB!

To reset a wordpress database connection we have to re-connect to wordpress DB. Create ‘reconnect-wp-db.php’:

$mysql_con = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if (!$mysql_con){
  die('Could not connect');
}
$mysql_db = mysql_select_db(DB_NAME, $mysql_con);
if (!$mysql_db){
  die('Could not connect to database');
}

Once you do these stuff your both the databases will work just fine!

Following is the code will help you out:

<div id="another_db_data">
  <?php
    require_once('another-db.php'); # connect to another database
    include_once ('another-db-content.php'); # fetch data from another database
    mysql_close(); # close another database connection
  ?>
</div>

<div id="wordpress_db_data">
  <?php
     include_once ('reconnect-wp-db.php'); # re-connect to wordpress database
     # echo your wordpress content here
  ?>
</div>

Leave a Reply