How to use multiple database in a PHP web application?

Question

I am making a PHP web Application in which i am using MySQL as database server, i want to make backup of some tables from one database to another database(with that tables in it). i have created two different connection, but the table is not updated.

$dbcon1 = mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD) or die(mysql_error());
$dbase1 = mysql_select_db(TEMP_DB_NAME,$dbcon)or die(mysql_error());

$query1=mysql_query("SELECT * FROM emp");

while($row = mysql_fetch_array($query1, MYSQL_NUM))
{
    $dbcon2 = mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD) or die(mysql_error());
    $dbase2 = mysql_select_db(TEMP_DB_NAME2,$dbcon)or die(mysql_error());

    mysql_query("INSERT INTO backup_emp VALUES(null,'$row[1]',$row[2])");
    mysql_close($dbcon2);
}

the code above is taking the data of emp from first database, and updataing it into another backup_emp table of another database. the code is not working properly, is there any other way of doing this...please help.

Answer 1

First of all: You're potentially opening hundreds of connections, running a query and closing them, just to re-open them again.

Take the second connect statement out of the loop.

Secondly you have to tell PHP, which command is intended for which connection, for example:

$query1 = mysql_query("SELECT * FROM emp", $dbcon1);

while($row = mysql_fetch_array($query1, MYSQL_NUM))
{
    mysql_query("INSERT INTO backup_emp VALUES(null,'$row[1]',$row[2])", $dbcon2);
}

There's a last little thing: If both connections go to the same server with the same username and the same password, the two mysql_connect statements will wind up with the same connection id.

That's why you'll need to set the fourth statement (new link) in your second connect statement to true:

$dbcon2 = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD, true)

Answer 2

INSERT INTO db2.table SELECT * FROM db1.table

Answer 3

If both databases they are on the same server, and the tables have the same schema, then you should use this query instead:

mysql_query("INSERT INTO database2.backup_emp SELECT * FROM database1.emp");

This way you don't need to worry about multiple database connections.

Answer 4

All u need is to change a line in ur code:

$dbcon2 = mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD, true) or die(mysql_error());

$dbcon1 = mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD) or die(mysql_error());
$dbase1 = mysql_select_db(TEMP_DB_NAME,$dbcon)or die(mysql_error());

$query1=mysql_query("SELECT * FROM emp");

while($row = mysql_fetch_array($query1, MYSQL_NUM))
{
    $dbcon2 = mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD,true) or die(mysql_error());
    $dbase2 = mysql_select_db(TEMP_DB_NAME2,$dbcon)or die(mysql_error());

    mysql_query("INSERT INTO backup_emp VALUES(null,'$row[1]',$row[2])");
    mysql_close($dbcon2);
}

Answer 5

why do you need such many connections . That makes things more complicated

Answer 6

You should:

• put the connection lines for the second DB outside of the loop as suggested
• you have to specify dbcon1 or 2 in the select db statement
• add true as last parameter to the 2nd connection request
• test your values before inserting them (don't forget row index begins at zero in PHP, and not 1).

Probably working like this :

$dbcon1 = mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD) or die(mysql_error());
$dbase1 = mysql_select_db(TEMP_DB_NAME,$dbcon1) or die(mysql_error());
$dbcon2 = mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD,true) or die(mysql_error());
$dbase2 = mysql_select_db(TEMP_DB_NAME2,$dbcon2) or die(mysql_error());
$errors= array();

$query1= mysql_query("SELECT * FROM emp");

while($row = mysql_fetch_array($query1, MYSQL_NUM))
{
   if (!isset($row[1])) 
      $row[1]= 'null'; //or generates an error
   else
      $row[1]= "'".$row[1]."'";
   if (!isset($row[2])) $row[2]= 'null'; //or generates an error
   if (mysql_query("INSERT INTO backup_emp VALUES(null,".$row[1].",".$row[2].")") !== true)
      $errors[]= "Problem inserting values (".$row[1].",".$row[2].").");
}
mysql_free_result($query1);
mysql_close($dbcon1);
mysql_close($dbcon2);

But if you don't need to do extra computations, checks or anything else on the data, the following suggestion was good as well :

INSERT INTO db2.table SELECT * FROM db1.table

Hope it helps

C^