Working with multiple databases in php/mysql

Question

I am working on a project needing me to work with multiple database connections. From what I have read, I should be able to switch between connections in the query itself, something like:

mysql_query("SELECT * FROM user_types", $db_core)or die(mysql_error());

But I receive the error:

Table 'db_company.user_types' doesn't exist

So I can see it is looking at the incorrect db, it is grabbing the last mysql_select_db

I wouldn't want to have to re-select the database everytime but if that is the better way to go I can.

I have the databases selected like so:

<?
$currentpage = $_SERVER["REQUEST_URI"];
//Core DB
$db_core_host = "localhost";
$db_core_username = "root";
$db_core_password = "";
$db_core_name = "db_main";
//
$db_core = mysql_connect($db_core_host,$db_core_username,$db_core_password);
mysql_select_db($db_core_name, $db_core)or die(mysql_error());
//Company DB
$db_company_host = $company['db_server'];
$db_company_username = $company['db_username'];
$db_company_password = $company['db_password'];
$db_company_name = $company['db_name'];
//
$db_company = mysql_connect($db_company_host,$db_company_username,$db_company_password);
mysql_select_db($db_company_name, $db_company)or die(mysql_error());
?>

Not sure if it helps at all but when I echo either of the database connections I get Resource id #5

Answer 1

Use the db.table syntax in the query:

mysql_query("SELECT * FROM databas_ename.table_name", $db_core) or die(mysql_error());

Answer 2

The code you have in your question should work, except when both databases are on the same server. Take a look at the $new_link parameter of mysql_connect (see docs here): if you call it twice with the same server/user/pass, the connection will be re-used - which makes you end up with the mysql_select_db call on one connection influence the other one.

So if you have two different servers, or set $new_link to true, your code should work.