how to connect to two database

hi,

i have script connect to two database and take data from first to input it into second database after making some changes

this script using mysql_connect two times with two socket
$DB1
$DB2

when i want to execute query in first i using
mysql_query($query, $DB1);

and second
mysql_query($query, $DB2);


it's working fine in my server and many others server
but with some people they told me its not works with them
when i tested it in there servers i noticed that when i open connection with the second database i lost connection with the first

i think there are settings i should make it in my.conf

any help ?

 

change the value of "mysql.max_links" in your php.ini

 

did u test it ??? it is work ? i think, just make 2 connections to 2 databases.

 

Yes, I've tested it.

Another way to do it if your databases aren't too big is:
1) Open connection to db1
2) Copy data to a PHP array
3) Close connection 1
4) Connect to db2
5) Insert the array into the db
6) Close connection 2

 

What your friend aren't doing is referencing which connection to use in each query.

Example: You have $DB1 and $DB2

$query = "SELECT * from sometable";
$result = mysql_query($query, $DB1);
while($row = mysql_fetch_object($result)) {
  $query2 = "INSERT INTO anothertable ...";
  $result2 = mysql_query($query2, $DB2);
} 

PHP:

See the 2nd parameter for the 2 mysql_query commands - you have to reference which DB connection to run the specific query on, otherwise PHP get's confused and will crash or blow a hissy fit.

Some web hosts configure PHP to use the most logical db connection (usually the first connection that was created) if you leave out the DB reference, but by default PHP doesn't know what to do if you have multiple db connections open.

 

i think mysql_pconnect is better for bigger websites, also and it doesn't close ... btw u can do 2 accounts for the 2 links to $DB1 & $DB2 if you are still unable to use DB1 after connecting DB2 ( i never tested 2 db's.. ... )

using mysql_pconnect (persistent connection ) will not close the connectiot , and won't create any NEW connection each time you call it with the same parameters, mysql_connect is not for heavy websites...

http://php.net/function.mysql-pconnect

this is actually needed only when there are too many users, but it won't be needed if he uses

mysql_pconnect();

PHP:

 

if you use 2 databases from the same server, then don't change the mysql_connect(), instead, remove the function mysql_select_db(); and instead, query like:

"SELECT * FROM [i]database-name[/i][b].[/b][i]table-name[/i]......................

Code (markup):

 

won't work if the databases are in different SQL accounts...