Page 1 of 2

Can you open a separate hosted DB from within another?

Posted: Wed May 01, 2013 10:44 am
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.

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

Posted: Wed May 01, 2013 2:43 pm
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

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

Posted: Wed May 01, 2013 5:22 pm
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.

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

Posted: Thu May 02, 2013 3:20 am
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.

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

Posted: Thu May 02, 2013 4:07 am
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.

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

Posted: Thu May 02, 2013 6:08 am
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)

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

Posted: Thu May 02, 2013 6:19 am
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

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

Posted: Thu May 02, 2013 6:51 am
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

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

Posted: Thu May 02, 2013 7:09 am
by simonmlewis
Thanks a lot - that did highlight the causes of the errors which I have found and fixed.
Great stuff.

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

Posted: Thu May 02, 2013 9:35 am
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

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

Posted: Thu May 02, 2013 9:49 am
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.

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

Posted: Thu May 02, 2013 10:03 am
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'>";
}
?>

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

Posted: Thu May 02, 2013 11:46 am
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

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

Posted: Thu May 02, 2013 11:58 am
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?

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

Posted: Thu May 02, 2013 12:01 pm
by mikosiko
the UPDATE with the JOIN is doing that... will UPDATE only if a matching row is found.