PHP: working with multiple databases
I'm in the process of moving a few other blogs over to use WP - one of which makes calls to a seperate database unrelated to WP. This proved to cause a conflict. After many hours of trying to figure out where the specific conflict was - I narrowed it down to my mysql_select_db line. Apparently selecting your database this way - even if you close your mysql connection - and then select a different database later on - it can cause you headaches (and make you stay up later than you had intended to) LOL! =Yawn=
The remedy was specifying the databasename when referencing the table and avoiding using the mysql_select_db line altogether.
So instead of code that looked like this:
$databaseConnection = mysql_connect($databaseServer, $databseUsername, $databasePassword) or
die ('I can't connect to the database.');
mysql_select_db($databaseName,$databaseConnection);
$query = "SELECT * from tablename;";
$result = mysql_query($query);
I changed it to this and it seemed to work:
$databaseConnection = mysql_connect($databaseServer, $databseUsername, $databasePassword) or
die ('I can't connect to the database.');
$query = "SELECT * from " . $databaseName . ".tablename;";
$result = mysql_query($query,$databaseConnection);
June 16th, 2004 at 12:22 am
Wow, that is a new and interesting way of doing it. I do see how it works.
I usually just make sure I have mysql_close() after my query is done, and only open connections when they're necessary.
June 16th, 2004 at 7:41 am
Well that's the thing - in this case, for whatever reason - just doing mysql_close() wasn't enough. It still was causing some kind of conflict. - I guess the mysql_select_db() is persistent (or *can* be persistent) even AFTER mysql_close(). Personally - I think "UNSELECTING" a database should be a function in PHP - maybe they'll add it. But MAN was that driving me crazy!
June 16th, 2004 at 7:47 am
I had this problem some time ago, and eventually resolved it by merging the two databases - luckily none of my table names conflicted. Your solution is more elegant - wish I'd thought of it!
August 9th, 2004 at 7:42 pm