Page 1 of 1
Connecting to 2 database's
Posted: Fri Dec 30, 2005 10:21 am
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

)
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.
Posted: Fri Dec 30, 2005 10:25 am
by Jeroen Oosterlaar
Look closer

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.
Posted: Fri Dec 30, 2005 10:31 am
by someberry
Hehe, that will teach me to read the description and parameters a bit more closely, thanks

Posted: Fri Dec 30, 2005 10:58 am
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.
Posted: Fri Dec 30, 2005 11:39 am
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
Posted: Fri Dec 30, 2005 12:26 pm
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());
}
}
Posted: Fri Dec 30, 2005 12:41 pm
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]]]]] )
Posted: Fri Dec 30, 2005 1:31 pm
by someberry
Thanks, the bool new_link worked like a treat, thanks.
