PHP Tip for when connecting to Multiple MySQL Databases in a Project Programming 25 APR 2013

php elephant iconIt is not very common but sometimes you’ll find yourself working on a project that features multiple databases holding all the information we need.

Now of course we know that when making an initial database connection we get a identifier on a successful connect, which we can then pass along to a mysql_query call as a parameter in order to force that query to happen on that particular database connection:

$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db('mydatabase', $conn);
mysql_query($mysqlquery, $conn);

And this will work 99% of the time. However, there is one instance when it won’t work, and is guaranteed to trip you up if you’ve never encountered this before. If you are using the same hostname, username and password across your databases, in other words the arguments passed to your mysql_connect call remains the same, mysql_connect will in fact not open a new connection on your secondary connect to another database, and instead just pass you the identifier it came up with on your first connect. Obviously this will break your code because the database you’re interested in using doesn’t exist on this first connection!

Luckily to get around this limitation is pretty simple. The mysql_connect function has an often neglected boolean parameter known as $new_link which if set to true, will force a new connection to be created regardless of whether or not the parameters for this connect request matches those of your previous connect request

//Doesn't work - $conn2 is never actually made, meaning your SQL statement will fail.
$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db('mydatabase', $conn);
$conn2 = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db('mydatabase2', $conn2);
mysql_query($mysqlquery, $conn2);

//Does work - $conn2 is made, meaning your SQL statement will succeed.
$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db('mydatabase', $conn);
$conn2 = mysql_connect('localhost', 'mysql_user', 'mysql_password',true);
mysql_select_db('mydatabase2', $conn2);
mysql_query($mysqlquery, $conn2);

And now you know. Not that this scenario should happen very often mind you…

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.