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.

Christian Studer · Accepted Answer · 2010-04-23 05:29:04Z

6

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)

answered Apr 23, 2010 at 5:29

Christian Studer

25.6k6 gold badges51 silver badges71 bronze badges

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

Comments

too much php · Accepted Answer · 2010-04-23 05:32:26Z

5

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.

answered Apr 23, 2010 at 5:32

too much php

91.5k37 gold badges133 silver badges140 bronze badges

Comments

Your Common Sense · Accepted Answer · 2010-04-23 05:32:04Z

1

INSERT INTO db2.table SELECT * FROM db1.table

answered Apr 23, 2010 at 5:32

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Comments

ovgu12 · Accepted Answer · 2010-04-23 07:23:09Z

1

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

answered Apr 23, 2010 at 7:23

ovgu12

1302 silver badges8 bronze badges

Comments

acerb · Accepted Answer · 2010-04-23 13:05:26Z

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^

nik · Accepted Answer · 2010-04-23 06:50:46Z

0

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

answered Apr 23, 2010 at 6:50

nik

3,6783 gold badges24 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to use multiple database in a PHP web application?

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related