Can you connect to two separate Hosted databases?
Moderator: General Moderators
-
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?
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 =..... ??
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.
All the best from the United Kingdom.
Re: Can you connect to two separate Hosted databases?
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.
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?
No - with totally separated hosting companies.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can you connect to two separate Hosted databases?
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?
"DBMS" ???
I may have to just do multiple open conns, and close conns. There isn't like to be more than 10 or 15.
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.
All the best from the United Kingdom.
Re: Can you connect to two separate Hosted databases?
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.
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
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";
}
?>-Greg
Last edited by twinedev on Fri Mar 16, 2012 9:57 am, edited 1 time in total.
Re: Can you connect to two separate Hosted databases?
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?
MySQL.
Somehow - this works. It gets the data from the external site, and the PRICE from the internal database - no errors.
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: £$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.
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?
It works locally, but not live.
I'm lost!!!
And no error messages at all, even tho the error script is 'on'.
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.
All the best from the United Kingdom.
Re: Can you connect to two separate Hosted databases?
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
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?
Since found out the hosts of external site do not allow external connections.
Big prob!
Big prob!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can you connect to two separate Hosted databases?
What about a RESTful solution?
Re: Can you connect to two separate Hosted databases?
A little more advanced, but you could look at putting a tunneling script on the remote host.
-Greg
-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?
Huh?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.