••• The database code in WordPress is not prepared for plugins that require a separate connection to a second database. For plugin developers this is a very source of headaches. And in fact it is a general problem for any PHP coded site that needs to access data from more than one database to render a single page.

I’m actually one of those developers. My WordPress plugin MintPopularPostsWP needs access to the Mint database. Mints database tables can safely reside within the WordPress database. Nevertheless, there are good reasons to keep them in a separate MySQL database. Opening a connection to this separate database would usually close the WordPress database connection. As a result all subsequent WordPress database methods would fail.

In order not to interfere with the WordPress database code plugin developers have to take some precautionary steps. Since it took a decent amount of trial, error and testing for me to find the proper solution I want to share the solution I finally came up with.

For most MySQL query functions there is an optional last parameter link_identifier. A link_identifier represents a particular database connection. Whenever it is added to the MySQL function call the desired database connection is used instead of the connection to the database currently selected by mysql_select.

In order to make this work you also have to add the optional fourth parameter true to your call to mysql_connect. This paramater tells MySQL to open a new database connection using the credentials provided instead of replacing the current connection with the new one. mysql_connect returns NULL in case of any error or the desired link_identifier for the new database connection. It should be stored in a variable. For WordPress developers this variable should carry a different name than $mysql or $wbdb since those variables seem to be used globally by WordPress.

Your code may look like this:

$my_mysql = @mysql_connect(‘MY_HOST’, ‘MY_USER’, ‘MY_PASSWORD’, true);
        
if ( !$my_mysql )
{
   echo mysql_error();
   // and bail out…
}

if ( !@mysql_select_db(‘MY_DATABSE’, $my_mysql) )
{
   echo 'Could not select database!';
   // and bail out…
}

Afterwards you would have to add the link_identifier ($my_mysql as in the sample above) as the very last parameter to all MySQL query functions.

$result = mysql_query( ‘SELECT foo FROM bar’, $my_mysql );

It is always a good idea to explicitly clean up – close the databse connection – when you don’t need it anymore:

@mysql_close($my_mysql);

This way the WordPress database connection and the WordPress code using this connection won’t be affected at all. — Nice and case closed.

Tags: Wordpress • Ausser der Reihe

3 Kommentare »