Can you connect to two separate Hosted databases?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Can you connect to two separate Hosted databases?

Post by simonmlewis »

I have a web site that uses it's own database on the page, but also then queries at separately hosted database (on another web site we run).

I can query the first database, then close that and store variables, then use those to query the other database.

But is there a way to run a query where you create the connection, then create a second connection, and somehow even those BOTH TABLES ARE NAMED THE SAME, query them both?

And if so, how?

ie. SELECT * FROM fred.products, where helen.products =..... ??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Can you connect to two separate Hosted databases?

Post by requinix »

If they're hosted on the same database server and you have one set of credentials that can access both databases, yes.
Fully-qualify the names like database.table.field and you can do whatever you want.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you connect to two separate Hosted databases?

Post by simonmlewis »

No - with totally separated hosting companies.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Can you connect to two separate Hosted databases?

Post by requinix »

What DBMS? There may be a way to set up a remote connection - offhand I know SQL Server can do it, but I don't know about MySQL.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you connect to two separate Hosted databases?

Post by simonmlewis »

"DBMS" ???
I may have to just do multiple open conns, and close conns. There isn't like to be more than 10 or 15.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Can you connect to two separate Hosted databases?

Post by twinedev »

You should be able to do the following (not tested, and I never had to actually use it) the key is to include the variable that holds the database connection resource that you are wanting to act on. Normally most people do not give it, for which PHP will default to the most recent one opened.

Code: Select all

<?php 

$dbServ1 = mysql_connect('SERVER_NAME_ONE','USER_NAME','PASSWORD')
	or die ('[ERR:'.__LINE__.'] Could not connect to the database server');

$dbServ2 = mysql_connect('SERVER_NAME_TWO','USER_NAME','PASSWORD')
	or die ('[ERR:'.__LINE__.'] Could not connect to the database server');

mysql_select_db('DATABASE_NAME_ON_SERVER_ONE',$dbServ1) 
	or die ('[ERR:'.__LINE__.'] Failed to select specified database');

mysql_select_db('DATABASE_NAME_ON_SERVER_TWO',$dbServ2) 
	or die ('[ERR:'.__LINE__.'] Failed to select specified database');

// Get data on Server 1:
$rsData = mysql_query('SELECT * FROM `tblName` WHERE `Condition`="True Value" ',$dbServ1)
	or die ('[ERR:'.__LINE__.'] Select Query Failed: '.mysql_error());

if (mysql_num_rows($rsData)>0) {
	while ($aryTemp = mysql_fetch_assoc($rsData)) {

		// Note, assuming other things are done as well, if you were just
		// exporting, you would put this as part of the condition in original SQL
		if ($aryTemp['Exported']=='No') {

			// Save a copy out to Server 2, assuming same table structure...
			$SQL1 = 'INSERT INTO `tblBackup` (';
			$SQL2 = ') VALUES (';
			foreach($aryTemp as $key=>$val) {
				$SQL1 .= '`'.$key.'`,';
				$SQL2 .= '"'.mysql_real_escape_string($val).'",';
			}
			// Piece togeter, substr()'s are to get rid of trailing commas
			$SQL = substr($SQL1,0,-1).substr($SQL2,0,-1).')';
			mysql_query($SQL,$dbServ2);

			// Update Server 1, assume there is a PK of "ID" that is an integer
			mysql_query('UPDATE `tblName` SET `Exported`="Yes" WHERE `ID`='.$aryTemp['ID'],$dbServ1)
				or die ('[ERR:'.__LINE__.'] Insert Query Failed: '.mysql_error());
		}

		// Do other things with this current row...
		
	}
	mysql_free_result($rsData);
}
else {
	echo "No rows returned";
}

?>
Note, you cannot use both databases in the same query. (if they were both on same database server, and the user you supplied had permissions for both, then you could).

-Greg
Last edited by twinedev on Fri Mar 16, 2012 9:57 am, edited 1 time in total.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Can you connect to two separate Hosted databases?

Post by requinix »

DBMS. Are you using MySQL or SQL Server or Oracle or what?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you connect to two separate Hosted databases?

Post by simonmlewis »

MySQL.

Somehow - this works. It gets the data from the external site, and the PRICE from the internal database - no errors.

Code: Select all

include "dbconn.php";
$result = mysql_query ("SELECT * FROM products WHERE pause = 'off'");
while ($row = mysql_fetch_object($result))
      {
        include "dbconnExtra.php";
        $resultj = mysql_query ("SELECT title, id, description, photoprimary FROM products WHERE id = '$row->prodid'");
        while ($rowj = mysql_fetch_object($resultj))
        {
        echo "<tr><td width='150px'><img src='http://www.domain.co.uk/images/productphotos/small/$rowj->photoprimary'></td>
        <td><div class='sectionhead' style='margin: 0px'>$rowj->title</div>
        <b>Shop Price: &pound;$row->price</b><br/><br/>";
        
        $position=200;
        $postcontent = substr($rowj->description,0,$position);

        echo "$postcontent</td></tr><tr><td colspan='2'><hr noshade size='1' color='#cccccc' /></td></tr>";
        }	mysql_free_result($resultj);
        mysql_close($extraconn);
      }	mysql_free_result($result);
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you connect to two separate Hosted databases?

Post by simonmlewis »

It works locally, but not live.
I'm lost!!!

And no error messages at all, even tho the error script is 'on'.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Can you connect to two separate Hosted databases?

Post by twinedev »

The code you gave has no error checking in it for things that PHP will not report errors for, primarily that your query failed (ex, due to user permissions).

So if the first query fails, you won't know, and then the while loop will never execute, so nothing in that loop would "work".

See the example I gave earlier for how to add in checking for errors and displaying them.

-Greg
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you connect to two separate Hosted databases?

Post by simonmlewis »

Since found out the hosts of external site do not allow external connections.
Big prob!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Can you connect to two separate Hosted databases?

Post by Celauran »

What about a RESTful solution?
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Can you connect to two separate Hosted databases?

Post by twinedev »

A little more advanced, but you could look at putting a tunneling script on the remote host.

-Greg
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you connect to two separate Hosted databases?

Post by simonmlewis »

Huh?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply