php mysql problem

dual connection..... is it possible?

[omoutop]

Hi all and thanks in advance for any insight.

my problem is this..
i must transfer selected records/data between two databases (but on the same server). How can i have two simultaneous connections open?

general guilines are:
- connect, select db // first connection
- connect, select db // second connection
- execute query and select data/rows from 1 table (first db)
- while looping through selected data
{
insert/update table // second db
}.

Would mysql_pconnect offer any help? (i am not sure on max number of persistend connections that are allowed).

[elgumbo]

Hi

I connect to 2 db without any problems. I don't transfer between the two but I can't see why it should be a problem.

eg:

//db1 connect
$dbh=mysql_connect ("localhost", "user", "password", true) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db1", $dbh);

//db2 connect
$dbhx=mysql_connect ("localhost", "user", "password", true) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db2", $dbhx);

i then just need to specify which connection to use when I am performing my query.

eg:
$sql= @mysql_query( "SELECT * from TABLE_ON_DB2 ",$dbhx);

Notice the connection string at the end of the query?

[omoutop]

$dbh=mysql_connect ("localhost", "user", "password", true)

All the magic is in the "TRUE" statement (force new connection).
My php version dont allow this....

But thanks anyway.

Any other suggestion?

======================
Anyway, i figure it ... thanks elgumbo you provide me the idea to test and it worked....

thanks again

[eelixduppy]

omoutop, I suggest you update your PHP ;)

Instead of connecting to both, dbs at once, you can query the one and retrieve a result variable for the info, and then query the second db with that info using the same db connection. You are just going to have to switch dbs in there somewhere:

$link = mysql_connect("localhost","user","password");
mysql_select_db("db1");
$query = "select * from table";
$result1 = mysql_query($query) or die(mysql_error());

$query = "insert into table2 (col1,col2) values ";
while($row = mysql_fetch_array($result1)){
$query .= "('".$row['col1']."','".$row['col2'."'),";
}
$query = rtrim($query,',');
mysql_select_db("db2");
$result2 = mysql_query($query) or die(mysql_error());
if($result2) {
echo "DB insert Was Successful";
}
else {
echo "DB insert NOT successful";
}
mysql_close($link);

I haven't tested this, or even reread it for that matter ;) Good luck!

[omoutop]

Let's see....

the php i cannot update myslef (server issues), so i am doomed to 4.1 :)

As for the rest.... i thank you eelixduppy for a quicker way... i indeed open two connections (well to be exact, i open 8 connections :) and retrieved data from 7 different databases and store some of them to the 8th).

[jatar_k]

read this carefully
[php.net...]

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters.

so you only need the fourth param if you are trying to force another connection to the same server, if that is the case then you don't need more than one connection. You can use the same connection and switch db's using mysql_select_db.

If you are not "calling with the same arguments" then it will open another connnection with no problems.

I have done both of these methods and the one eelix demonstrated on versions much older than 4.1.

[omoutop]

---

System: The following message was spliced on to this thread from: http://www.webmasterworld.com/php/3017143.htm [webmasterworld.com] by dreamcatcher - 1:00 pm on July 21, 2006 (utc 0)

---

I am in need of setting two connection on two servers.

I use:
// for server from which the script runs
$link1 = mysql_connect("localhost", "user1", "pass1") or die(mysql_error());

//the server to connect to
$link2 = mysql_connect("#*$!.#*$!.#*$!.#*$!:port", "user2", "pass2") or die(mysql_error());

also i tried
$link2 = mysql_connect("mysite.com:port", "user2", "pass2") or die(mysql_error());

In all cases i got a "Warning: mysql_connect(): Lost connection to MySQL server during query in path/to/page line x" error for the second connection.

Any ideas on what to do?

[jatar_k]

have you tried connecting to only the remote server? Maybe it isn't letting you connect