Connect to multiple dbs, on multiple servers through one php file?

how?

[ajs83]

How does one accomplish this?

[Philosopher]

That would depend on how the various db's are set up.

Do they allow remote connections or are they locked down to local connections?

I've run into that issue in the past where some of the db servers allowed remote connections and some didn't. I ended up creating a script that would run on the local server and another master script that would run on the primary server. The master script would run the local scripts which would access the db's. The local scripts would then output the results which would be read by the master script.

[freeflight2]

Easy...:

$db = @mysql_connect (host , user , passwd);
$db2 = @mysql_connect (host2 , user , passwd);
$db3 = @mysql_connect (host3 , user , passwd);

etc...

then you can make your queries like this:
$result = db_query($sql, $db1)
or
$result = db_query($sql, $db2)
...

go to php.net and check out all available [my]Sql functions php has to offer.

[ajs83]

Would it be just like this

$db_host = "site";
$db_username = "username";
$db_password = "pass";
$db_name = "database_name";

$conn = mysql_connect($db_host,$db_username,$db_password);
$db = mysql_select_db($db_name);
============================
$db1_host = "site";
$db1_username = "username";
$db1_password = "pass";
$db1_name = "database_name";

$conn1 = mysql_connect($db1_host,$db1_username,$db1_password);
$db1 = mysql_select_db1($db1_name);

and so on?

[ajs83]

?

[jatar_k]

sure, should work

[coopster]

Yep, should. Sometimes you just have to take that leap of faith and test it, ajs83 ;)

[StupidScript]

Combining what freeflight2 noted with your connection code as an illustration:

## SET UP CONNECTION VARIABLES

$db_host = "site0"; 

$db_username = "username0"; 

$db_password = "pass0"; 

$db_name = "database_name0";

$db1_host = "site1"; 

$db1_username = "username1"; 

$db1_password = "pass1"; 

$db1_name = "database_name1"; 

### MAKE CONNECTIONS

$conn = mysql_connect($db_host,$db_username,$db_password); 

$conn1 = mysql_connect($db1_host,$db1_username,$db1_password); 

### RUN QUERIES

$q = mysql_db_query($db_name,$q_string,$conn);

$q1 = mysql_db_query($db_name1,$q_string1,$conn1);

### USE QUERY OUTPUT

while($row=mysql_fetch_array($q)) {

[do something with data]

}

["regular" non-db stuff, if you like]

while($row=mysql_fetch_array($q1)) {

[do something with data1]

}

etc.

IMHO, I just like to keep everything together like that. The code you posted is pretty close*, too ... it's a personal preference, mostly.

*There's no

mysql_select_db1()

function, for example. You need to use either

mysql_select_db($db,$resource_link_identifier)

or

mysql_db_query($db,$q,$resource_link_identifier)

to hook up with a specific connection among many. I use

mysql_db_query()

in these situations because what the heck? Why not do the query if you're selecting the db?

<edit>Interestingly, nearly the same code (didn't see it, I swear!) can be found in the PHP manual for

mysql_select_db()

section [php.net]. Scroll down to the comments section.</edit>

[ajs83]

Yep, should. Sometimes you just have to take that leap of faith and test it, ajs83 ;)

I usually do so I don't post too many "How do I..." questions but being new to php I like to make sure that Im heading in the right direction before wasting too much time.

[ajs83]

I'm having issues adding it to this query, I've tried numerous various but get all types of errors. What is the correct way to tell it to use the connection with the "$conn" tag?

$query = "SELECT id, email FROM users WHERE email='$u'";
$result = @mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
if ($row) {
$id = $row[0];
$email = $row[1];

-------------------------------------------------------
// Make the query.
$query = "UPDATE users SET password='".md5($p)."' WHERE id='$id'";

[coopster]

resource mysql_query [php.net] ( string query [, resource link_identifier] )

Example:

$conn = @mysql_connect($server, $user, $password); 
$db = @mysql_select_db($database, $conn); 
$query = "SELECT id, email FROM users WHERE email='$u'"; 
$result = @mysql_query ($query, $conn); 
if ($row = mysql_fetch_array ($result, MYSQL_NUM)) { 
  $id = $row[0]; 
  $email = $row[1]; 
  //  
  // more code here 
  // 
}

[JamShady]

Just in case you're connecting to two different databases on the same server, make sure you set the new link variable in mysql_connect to true. Otherwise PHP will just use the old connection, but change the database it's using over, so in effect both connections will be using the same database, as opposed to two different databases on the same server.