Page 1 of 1
Can you connect to two separate Hosted databases?
Posted: Wed Mar 14, 2012 11:58 am
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 =..... ??
Re: Can you connect to two separate Hosted databases?
Posted: Wed Mar 14, 2012 4:51 pm
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.
Re: Can you connect to two separate Hosted databases?
Posted: Thu Mar 15, 2012 4:24 am
by simonmlewis
No - with totally separated hosting companies.
Re: Can you connect to two separate Hosted databases?
Posted: Thu Mar 15, 2012 4:48 am
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.
Re: Can you connect to two separate Hosted databases?
Posted: Thu Mar 15, 2012 4:50 am
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.
Re: Can you connect to two separate Hosted databases?
Posted: Thu Mar 15, 2012 5:22 am
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
Re: Can you connect to two separate Hosted databases?
Posted: Thu Mar 15, 2012 5:58 am
by requinix
DBMS. Are you using MySQL or SQL Server or Oracle or what?
Re: Can you connect to two separate Hosted databases?
Posted: Thu Mar 15, 2012 6:13 am
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: £$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);
Re: Can you connect to two separate Hosted databases?
Posted: Fri Mar 16, 2012 8:09 am
by simonmlewis
It works locally, but not live.
I'm lost!!!
And no error messages at all, even tho the error script is 'on'.
Re: Can you connect to two separate Hosted databases?
Posted: Fri Mar 16, 2012 10:00 am
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
Re: Can you connect to two separate Hosted databases?
Posted: Fri Mar 16, 2012 10:09 am
by simonmlewis
Since found out the hosts of external site do not allow external connections.
Big prob!
Re: Can you connect to two separate Hosted databases?
Posted: Fri Mar 16, 2012 10:12 am
by Celauran
What about a RESTful solution?
Re: Can you connect to two separate Hosted databases?
Posted: Fri Mar 16, 2012 10:29 am
by twinedev
A little more advanced, but you could look at putting a tunneling script on the remote host.
-Greg
Re: Can you connect to two separate Hosted databases?
Posted: Fri Mar 16, 2012 10:34 am
by simonmlewis
Huh?