How to connect to 2 databases at the same time in PHP

Question

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

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?

If you use 2 connection handlers, you definitely want to make sure you close both connections after using them. - Arunabh Das — , Dec 29 '09 at 05:17
But PHP will disconnect from the database when the script terminates http://php.net/manual/en/function.mysql-close.php — Darryl Hein, Dec 29 '09 at 05:48

score 22 · Accepted Answer · answered Oct 24 '08 at 21:15

22

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.

answered Oct 24 '08 at 21:15

Lucas Oman

15,597
2
44
45

score 14 · Answer 2 · answered Oct 24 '08 at 21:42

14

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

answered Oct 24 '08 at 21:42

Joe Lencioni

10,231
18
55
66

I was trying very hard to figure out how an old php app was doing this. This makes sense now. – zkent May 04 '16 at 16:36

score 8 · Answer 3 · answered Oct 24 '08 at 21:32

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_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 --

score 4 · Answer 4 · answered Oct 24 '08 at 21:17

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

score 3 · Answer 5 · answered Oct 24 '08 at 22:21

3

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

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

answered Oct 24 '08 at 22:21

Gary Richardson

16,081
10
53
48

Rajpal Singh · Answer 6 · 2016-08-06T10:16:51.417

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);
      ?>

As a general guide variables in PHP should not be capitalised but `$camelCased`. Capitalised words are harder to read. — Martin, Aug 06 '16 at 10:15
@Martin - According to your choice you can use your own style. Because I'm just try to give a answer about this question with some examples . — Rajpal Singh, Aug 06 '16 at 10:20

score -1 · Answer 7 · answered Aug 06 '16 at 10:29

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);

This doesn't work. You need to pass true as the 4th parameter in mysql_connect otherwise it will just return the previous connection — Darryl Hein, Aug 07 '16 at 03:38

How to connect to 2 databases at the same time in PHP

7 Answers7

Linked