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

Byron Whitlock · Accepted Answer · 2009-11-17 16:55:08Z

4

Use the db.table syntax in the query:

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

answered Nov 17, 2009 at 16:55

Byron Whitlock

54.2k29 gold badges128 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kilrizzy Over a year ago

This works, would I run into an issue if the databases are on different servers and share the same name?

Byron Whitlock Over a year ago

No they would need to be on the same server. If the db is on another server you need to create another connection using mysql_connect.

Wim · Accepted Answer · 2009-11-17 17:04:52Z

1

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.

answered Nov 17, 2009 at 17:04

Wim

11.3k44 silver badges59 bronze badges

Collectives™ on Stack Overflow

Working with multiple databases in php/mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related