Can you open a separate hosted DB from within another?

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

Moderator: General Moderators

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

Can you open a separate hosted DB from within another?

Post by simonmlewis »

We have web sites that run in multiple countries.
They have their own databases.
We need to check via the UK site, if a product is held on each of the other sites.
There is a primary code used, (tho it varies with two characters at the start).

I need to open the UK conn, query for each code, and test that code against each other site. If found, update the UK site.

It would have been something like this:

Code: Select all

include "dbconn.php";
$result = mysql_query ("SELECT romancode FROM products");
while ($row = mysql_fetch_object($result))
  {
  $rcid = $row->rcode;
`include "second_dbconn.php";
$result2 = mysql_query ("SELECT romancode FROM products WHERE romancode = '$rcid'");
$num_result = mysql_num_rows($result2);
if ($num_result != 0)
{
mysql_query(UPDATE products (from the dbconn.php connection) SET site2 = "in" WHERE romancode = "$rcid");
}
mysql_close($sqlconn);


`include "third_dbconn.php";
$result2 = mysql_query ("SELECT romancode FROM products WHERE romancode = '$rcid'");
$num_result = mysql_num_rows($result2);
if ($num_result != 0)
{
mysql_query(UPDATE products (from the dbconn.php connection) SET site2 = "in" WHERE romancode = "$rcid");
}
mysql_close($sqlconn);

  }
It looks a mess, but I hope you can see what I am driving for, and maybe there is a simpler method?

Basically - just need to test each 'code' against multiple sites. If the code is in, then update the local site.
I suppose another way, might be to store variables in sessions, and on-the-fly add more sessions with a count ($session1, 2, 3, 4) and then queries those against the databases.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Can you open a separate hosted DB from within another?

Post by mikosiko »

opening connections and queries in loops ? .... no way jose!!! highly inefficient ....

- you can open several connections at the same time outside of the loop.
- you can just make the UPDATE query using JOIN... no loops.... you can reference the remote table as host.db.tablename
- you can use the FEDERATE ENGINE instead of make so many connections http://dev.mysql.com/doc/refman/5.0/en/ ... ngine.html (pay attention to the limitations)

my preference... FEDERATE STORAGE ENGINE
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Can you open a separate hosted DB from within another?

Post by pickle »

When you run a query, you can specify which database connection to use:

Code: Select all

$db1 = mysql_connect('db1');
$db2 = mysql_connect('db2');

mysql_query('UPDATE....',$db2);

The mysql_* functions are fantastically old. You should upgrade to the mysqli_* functions which in most cases can be drop-in replacements for the mysql_* functions. You should probably use the OOP version as well, and abstract your database interactivity into an abstract class.... but that's beyond the scope of your question.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

Dumb question, but how do I do

Code: Select all

$db1 = mysql_connect('db1');
With

Code: Select all

include "dbconn.php";
?

I several of these conn files, so want to still with that routine, and see if it works using your method of running a query, with the $db at the end.
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 open a separate hosted DB from within another?

Post by simonmlewis »

Code: Select all

<?php
$cookietype = $_COOKIE['type'];
if ($cookietype == "admin") 
{
echo "PLEASE WAIT A MOMENT<br/><br/>";

$dbuk = mysql_connect("localhost", "root",""); 
$dbes = mysql_connect("localhost", "root",""); 

mysql_select_db('site', $dbuk);
mysql_select_db('site_es', $dbes);

$result = mysql_query ("SELECT title, romancode FROM products GROUP BY romancode", $dbuk);
while ($row = mysql_fetch_object($result))
  {
  $rces = "es"."$row->romancode";
  echo "$row->romancode, ";
  
  $resultes = mysql_query ("SELECT uk_title, romancode FROM products WHERE romancode = '$rces' GROUP BY romancode", $dbes);
  echo "$rces<br/>";
  $num_rows = mysql_num_rows($resultes);
  if ($num_rows != 0) 
    { 
    echo "$num_rows";
    mysql_query ("UPDATE products SET region_es = 'yes'", $dbuk);
    }
    $num_rows = 0;
  
  
  }
}
else 
{
	echo "<meta http-equiv='Refresh' content='0 ;URL=index.php?page=a_login&menu=a_admin&head=please login'>";
}
?>
This seems to be only using the second of the two connections. The "echo $row->romancode" is only rendering the code from the ES database.
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 open a separate hosted DB from within another?

Post by twinedev »

First off, are the databases on two separate database servers, or both on the same machine?

From your title to the thread, I was at first thinking you meant a separately hosted (ie different) machine.
If this is the case, doing both mysql_connect()'s to "localhost' will not work, as they will both only access the database on the same server the script it running. If they are two different machines, you need to set one of them to be the name (or IP) of the remote database server, and on that server, make sure that whatever user you are using, you give remote access to that user (lock it down either by hostname or IP, do not just fully open it up to wildcard)
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

Apologies, after I sent that, it looked obvious. But it's not.

Code: Select all

<?php
$cookietype = $_COOKIE['type'];
if ($cookietype == "admin") 
{
echo "PLEASE WAIT A MOMENT<br/><br/>";

$dbuk = mysql_connect("localhost","USERHERE","PASSHERE"); 
$dbes = mysql_connect("www.sitea.es","USERHERE","PASSHERE"); 

mysql_select_db('sitea', $dbuk);
mysql_select_db('siteb_es', $dbes);

$result = mysql_query ("SELECT title, romancode FROM products GROUP BY romancode", $dbuk);
while ($row = mysql_fetch_object($result))
  {
  $rces = "es"."$row->romancode";
  echo "$row->romancode, ";
  
  $resultes = mysql_query ("SELECT uk_title, romancode FROM products WHERE romancode = '$rces' GROUP BY romancode", $dbes);
  echo "$rces<br/>";
  $num_rows = mysql_num_rows($resultes);
  if ($num_rows != 0) 
    { 
    echo "$num_rows";
    }
    $num_rows = 0;
  
  
  }
}
else 
{
	echo "<meta http-equiv='Refresh' content='0 ;URL=index.php?page=a_login&menu=a_admin&head=please login'>";
}
?>
This produces the following error on this line:

Code: Select all

while ($row = mysql_fetch_object($result))
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/site/public_html/update.php on line 14
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 open a separate hosted DB from within another?

Post by twinedev »

place checks on the connections. This is the "ugly" way of doing it:

Code: Select all

$dbuk = mysql_connect("localhost", "root","")
  or die ('Could not connect to UK database server');
$dbes = mysql_connect("localhost", "root","")
  or die ('Could not connect to ES database server');

mysql_select_db('site', $dbuk)
  or die ('Could not select specific database SITE on UK server');
mysql_select_db('site_es', $dbes)
  or die ('Could not select specific database SITE_ES on ES server');
I'm betting that you will probably find that it is a permission error which was cascading down to cause other things to fail
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

Thanks a lot - that did highlight the causes of the errors which I have found and fixed.
Great stuff.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Can you open a separate hosted DB from within another?

Post by mikosiko »

good... you just applied the point 1 of my first post... still running queries in a loop when is no need JOIN's will do the job (point 2 in first post)... and also using a GROUP BY when is not neccesary
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

How would I do this in a JOIN - frankly I'm rubbish at Joins!! Group by I have removed now. didn't need it. lol.
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 open a separate hosted DB from within another?

Post by simonmlewis »

This is it at the moment:
So it checks for each product in the UK DB, then checks the romancode (modified) against two other external databases.
I don't see any way of doing that, in a JOIN.

Code: Select all

<?php
$cookietype = $_COOKIE['type'];
if ($cookietype == "admin") 
{
echo "PLEASE WAIT A MOMENT<br/><br/>";

$dbuk = mysql_connect("localhost","user","pass")or die ('Could not connect to UK database server');
$dbes = mysql_connect("site2","user","pass")or die ('Could not connect to ES database server');
$dbie = mysql_connect("site3","user","pass")or die ('Could not connect to ES database server');

mysql_select_db('dbname', $dbuk)
  or die ('Could not select specific database SITE on UK server');
mysql_select_db('dbname', $dbes)or die ('Could not select specific database SITE_ES on ES server');
mysql_select_db('dbname', $dbes)or die ('Could not select specific database SITE_IE on IE server');

$result = mysql_query ("SELECT title, romancode FROM products WHERE pause = 'off'", $dbuk);
while ($row = mysql_fetch_object($result))
  {
  $rces = "es"."$row->romancode";
  $rcie = "ie"."$row->romancode";    
  
  $resultes = mysql_query ("SELECT romancode FROM products WHERE romancode = '$rces'", $dbes);
  $num_es = mysql_num_rows($resultes);
  if ($num_es != 0)
    {
    $countes = $countes + 1;
    echo "$row->romancode (UK): Found $rces (SPAIN) - Updated! $countes (Spain)<br/>";
    mysql_query("UPDATE products SET region_es = 'yes' WHERE romancode = '$row->romancode'", $dbuk);
    }
  
  $resultie = mysql_query ("SELECT romancode FROM products WHERE romancode = '$rcie'", $dbie);
  $num_ie = mysql_num_rows($resultie);
  if ($num_ie != 0)
    {
    $countie = $countie + 1;
    echo "Found $rcie (IRELAND) for $row->romancode (UK) - Updated! $countIE (Ireland)<br/>";
    mysql_query("UPDATE products SET region_ie = 'yes' WHERE romancode = '$row->romancode'", $dbuk);
    }
  
  }
  
  echo "$countes updates for Spain<br/>
  $countie updates for Ireland<br/>";
  
mysql_close($dbuk);
mysql_close($dbes);
mysql_close($dbie);
}
else 
{
	echo "<meta http-equiv='Refresh' content='0 ;URL=index.php?page=a_login&menu=a_admin&head=please login'>";
}
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Can you open a separate hosted DB from within another?

Post by mikosiko »

the general guidance/example directly from the manual:
"You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.9.2, “JOIN Syntax”. Here is an example:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
The preceding example shows an inner join that uses the comma operator, but multiple-table UPDATE statements can use any type of join permitted in SELECT statements, such as LEFT JOIN.

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly"


a probably example for your case (obviously non tested on my side... you have to play with it):

Code: Select all

 UPDATE site.products AS sp
   JOIN site_es.products AS sep ON CONCAT('es',sp.romancode) = sep.romancode
 SET region_es = 'yes'
no SELECT... no LOOP... apply the same for the site_ie
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

But it has to check the roman code is in the ES and IE DBS first. Or is that happening in your example?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Can you open a separate hosted DB from within another?

Post by mikosiko »

the UPDATE with the JOIN is doing that... will UPDATE only if a matching row is found.
Post Reply