[SOLVED] mysql_connect multiple db connections clarification please

[ifubad]

Using a single script

 

Connecting to two dbases on different servers, must the first connection be closed first?

 

Connecting to two dbases on the same server, must the first connection be closed first?

[Psycho]

No to both questions. But, if you are needing to maintain multiple connections, then it is imperative that you create a link identifier when making the connections so you can use that identifier when making a query.

 

$link1 = mysql_connect('server1', 'mysql_user', 'mysql_password');

$link2 = mysql_connect('server2, 'mysql_user', 'mysql_password');

$result1 = mysql_query("SELECT * FROM tableInDBOne", $link1);

$result2 = mysql_query("SELECT * FROM tableInDBTwo", $link2);

[beyzad]

So where is your DB selection?

 

This is right:

 

$dbh1 = mysql_connect('host1','user1','pass1'); // 1st DB
mysql_select_db('db1',$dbh1);

$dbh2 = mysql_connect('host2','user2','pass2'); // 2nd DB
mysql_select_db('db2',$dbh2);

[ifubad]

Thank u for the explanation, it cleared things up a most of the way.

 

Another Question:

The example from the book is using a connection object, which uses a static variable to programmatically force it to only have one connection at a time. Is it that the main reason is to conserve resources, even though as stated by the above reply, where multiple connections is possible? (these books, sometimes when one keeps reading it over and over, it gets even more confusing)

 

See book excerpt below.

 

Book Excerpt:

Creating a database connection is an expensive operation so restricting creation of connections conserves resources.

 

By restricting the connection class to a single instance, we are mimicking the built-in mysql_connect function. Its default behavior is to reuse a connection resource rather than create a new one.

 

However, there are some circumstances where a new connection is a

necessity.

 

Two different connection objects are required if a single script needs to connect to two different servers. The close method makes it possible to connect to a different server.

 

Two instances of the MySQLConnect class can exist, but not simultaneously. If you want to create a connection to another server, you must first close the existing connection. The close method closes the current connection and resets the static variable $instances to 0. Manipulating the $instances variable in this way allows you to create a new connection, but only after the current one is closed.

[PFMaBiSmAd]

Sounds like an incorrectly written class. One of the points of using a class is because every instance of that class is separate from every other instance of that class.

 

You should be able to create two instances of a db class, each with different database connection information. If you use objects and methods of the first instance, it would use the connection that has been created in that instance. If you use objects and methods of the second instance, it would use the connection that has been created in that instance.

 

This assumes that the class was written correctly to store the link resource returned by the connection and to use that link resource in each database function call that is link specific.

[ifubad]

Sounds like an incorrectly written class.

 

Thanks for explaining it in a very clear way, it was easily understood.

 

From what I gathered of what the author of the book is trying to teach, is that the reason he is suggesting to force one open connection at a time only, is mainly to conserve resources. TYPICALLY, does this resource concern justify the author's method, compared to just connecting to multiple dbases simultaneously?

[PFMaBiSmAd]

If your code must connect to two or more different database servers to accomplish its' task, opening a connection to each server only once and keeping it open as long as you need it will take less overall resources than repeatedly opening/closing the connections so that only one connection is open at a time. If you have gone through the expense of opening a connection, don't throw it a way, open a different one, throw that a way, then open the first one again.

 

The only time you should close any database connection is when you are completely done with it, not because you need to open another connection at the same time.

 

The class/book you have found is imposing an artificial restriction that is not present in the mysql_connection function they are claiming to mimic. The class does not support simultaneous connections to multiple database servers. The mysql_connect function does support simultaneous connections to multiple database servers.

[ifubad]

The class/book you have found is imposing an artificial restriction that is not present in the mysql_connection function they are claiming to mimic.

 

I read that section of the book soooo many times and it just kept confusing me. A BIG thank u for the clear explanation, now I can finally move on to the next section , hopefully there will not be any more confusing chapters later.

 

Thnx a whole bunch, it was driving me nuts.