如何在PHP中同时连接两个数据库

I am trying to connect to 2 databases on the same instance of MySQL from 1 PHP script.

我正在尝试从一个PHP脚本连接到同一个MySQL实例上的两个数据库。

At the moment the only way I've figured out is to connect to both databases with a different user for each.

目前，我找到的唯一方法是为每个数据库连接不同的用户。

I am using this in a migration script where I am grabbing data from the original database and inserting it into the new one, so I am looping through large lists of results.

我在一个迁移脚本中使用它，我从原始数据库中获取数据并将其插入到新的数据库中，因此我正在对大量结果列表进行循环。

Connecting to 1 database and then trying to initiate a second connection with the same user just changes the current database to the new one.

连接到一个数据库，然后尝试与同一用户启动第二个连接，只需要将当前数据库更改为新的数据库。

Any other ideas?

任何其他想法?

7 个解决方案

#1

You'll need to pass a boolean true as the optional fourth argument to mysql_connect(). See PHP's mysql_connect() documentation for more info.

您需要将布尔值true作为可选的第四个参数传递给mysql_connect()。更多信息请参见PHP的mysql_connect()文档。

#2

If your database user has access to both databases and they are on the same server, you can use one connection and just specify the database you want to work with before the table name. Example:

如果您的数据库用户可以访问两个数据库，并且它们位于同一个服务器上，那么您可以使用一个连接，并在表名之前指定要处理的数据库。例子:

SELECT column
FROM database.table

Depending on what you need to do, you might be able to do an INSERT INTO and save a bunch of processing time.

根据需要做什么，您可能能够插入并节省大量处理时间。

INSERT INTO database1.table (column)
SELECT database2.table.column
FROM database2.table

#3

Lucas is correct. I assume that both the databases are hosted on the same host.

卢卡斯是正确的。我假设这两个数据库都驻留在同一个主机上。

Alternatively, you can create only 1 db connection and keep swapping the databases as required. Here is pseudo code.

或者，您可以只创建一个db连接，并根据需要继续交换数据库。这是伪代码。

$db_conn = connect_db(host, user, pwd);
mysql_select_db('existing_db', $db_conn);
 -- do selects and scrub data --
mysql_select_db('new_db', $db_conn);
-- insert the required data --

#4

I would suggest using two connection handlers

我建议使用两个连接处理程序

   $old = mysql_connect('old.database.com', 'user', 'pass);
   mysql_select_db('old_db', $old);


   $new = mysql_connect('new.database.com','user','pass);
   mysql_select_db('new_db', $new)

   // run select query on $old
   // run matching insert query on $new

#5

If it's an option, use PDO: you can have as many database connections open as you like.

如果是一个选项，可以使用PDO:可以打开任意多的数据库连接。

Plus, assuming your executing the same queries over and over, you can use prepared statements.

另外，假设您反复执行相同的查询，您可以使用准备语句。

#6

You can easily use 2 databases in same time with following Codes:

您可以轻松地同时使用两个数据库，代码如下:

<?php
  define('HOST', "YOURHOSTNAME");
  define('USER', "YOURHOSTNAME");
  define('PASS', "YOURHOSTNAME");
  define('DATABASE1', "NAMEOFDATABASE1");
  define('DATABASE2', "NAMEOFDATABASE2");

  $DATABASE1  = mysqli_connect(HOST, USER, PASS, DATABASE1);
  $DATABASE2  = mysqli_connect(HOST, USER, PASS, DATABASE2);
  if(!$DATABASE1){
      die("DATABASE1 CONNECTION ERROR: ".mysqli_connect_error());
   }
  if(!$DATABASE2){
      die("DATABASE2 CONNECTION ERROR: ".mysqli_connect_error());
   }


   $sql = "SELECT * FROM TABLE";   /* You can use your own query */

   $DATABASE1_QUERY = mysqli_query($DATABASE1, $sql);
   $DATABASE2_QUERY = mysqli_query($DATABASE2, $sql);

   $DATABASE1_RESULT = mysqli_fetch_assoc($DATABASE1_QUERY);
   $DATABASE2_RESULT = mysqli_fetch_assoc($DATABASE2_QUERY);

   /* SHOW YOUR RESULT HERE WHICH DATABASE YOU WANT FROM */
   echo  $DATABASE1_RESULT['id'];
   echo  $DATABASE2_RESULT['id'];


  /*After complete your all work don't forgot about close database connections*/
  mysqli_close($DATABASE1);
  mysqli_close($DATABASE2);
      ?>

#7

-1

First Connect Two Database

首先连接两个数据库

$database1 = mysql_connect("localhost","root","password");
$database2 = mysql_connect("localhost","root","password");

Now Select The Database

现在选择数据库

$database1_select = mysql_select_db("db_name_1") or die("Can't Connect To Database",$database1);
$database_select = mysql_select_db("db_name_2") or die("Can't Connect To Database",$database2);

Now if we want to run query then specify database Name at the end like,

现在如果我们想要运行查询那么在末尾指定数据库名，

$select = mysql_query("SELECT * FROM table_name",$database1);

#1