Connecting to 2 database's

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Connecting to 2 database's

Post by someberry »

Hi,

I was wondering what would be the best way to connect to two database's in the same script. I want to go for the OO approach for many reasons, which I wont go into here (since I have seen the effects it can cause :roll:)

I had a quick browse of the PHP manual for seeing if you could add something to the mysql_query function to determine which database you wanted to get info from, but I couldn't find anything.

Anyone know if this is possible, and if so, how I would go about it?

Thanks,
someberry.
Jeroen Oosterlaar
Forum Commoner
Posts: 37
Joined: Sun Nov 06, 2005 4:12 pm

Post by Jeroen Oosterlaar »

Look closer :wink: at

http://nl2.php.net/manual/en/function.mysql-query.php
resource mysql_query ( string query [, resource link_identifier] )

mysql_query() sends a query (to the currently active database on the server that's associated with the specified link_identifier).

query

A SQL query

The query string should not end with a semicolon.
link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level warning is generated.
The link identifier parameter can be used to specify an open database connection.
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post by someberry »

Hehe, that will teach me to read the description and parameters a bit more closely, thanks :)
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post by someberry »

Hmm, I think I must be doing something wrong, it only ever seems to diplay the last database the script was connected to :(

Code: Select all

// A mySQL query
echo '<p><b>Database 1</b><br />';
$rs = mysql_query("SELECT `id` FROM `test`", $connection->connection) or die(mysql_error());

while($row = mysql_fetch_assoc($rs)){ echo 'ID: ' . $row['id'] . '<br />'; }

// Another mySQL query
// Create another connection
$newConnection = new Connect();
$newConnection -> setDatabase('test');

echo '<p><b>Database 2</b><br />';
$rs = mysql_query("SELECT `id` FROM `test`", $newConnection->connection) or die(mysql_error());

while($row = mysql_fetch_assoc($rs)){ echo 'ID: ' . $row['id'] . '<br />'; }

// Another mySQL query
// Back to the old database
echo '<p><b>Database 1... again</b><br />';
$rs = mysql_query("SELECT `id` FROM `test`", $connection->connection) or die(mysql_error());

while($row = mysql_fetch_assoc($rs)){ echo 'ID: ' . $row['id'] . '<br />'; }
This produces:

Code: Select all

Database 1
ID: 1
ID: 2

Database 2
ID: 1
ID: 2
ID: 3
ID: 4

Database 1... again
ID: 1
ID: 2
ID: 3
ID: 4
-> connection is the variable that contains the connection info.

I am used to JAVA, so I assume that PHP OO works in the same way, in which each new instance contains a fresh set of variables for the instance to work with?

Thanks.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

let's see how you created the connection, without seeing into your class I can't tell much, but that looks like it should be working unless you are not storing the link identifier within your class correctly
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post by someberry »

Code: Select all

class Connect {
	var $connection;
	var $result;
	var $database;

	function setDatabase($database) {
		$this -> database = $database;
		$this -> makeConnection();
	}
  
	function makeConnection() {
		$this -> connection = mysql_connect		('localhost', 'username', 'password') or die(mysql_error());
		$this -> result 	= mysql_select_db	($this->database, $this ->connection) or die(mysql_error());
	}
}
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

ok, try setting the "new link" flag on mysql_connect

resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]] )
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post by someberry »

Thanks, the bool new_link worked like a treat, thanks. :)
Post Reply