Connecting to two DBs on two separate hosts - how?

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: 4434
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Connecting to two DBs on two separate hosts - how?

Post by simonmlewis »

I need to run a query every few hours that checks from our USA server, that the UK database has certain credentials in the "rcstock" field.

So I need to open two connections: 1, to local USA, and 2, to UK on a different host server.

USA is allowed in, so that should be fine. But I am getting a while screen. No errors.
There is nothing in the error log for it.

Am I doing it right??

Code: Select all

<?php
$dbuk = mysql_connect('host','username','pass')or die ('Could not connect to website.co.uk');
$dbus = mysql_connect('localhost','username','pass')or die ('Could not connect to website.com');
mysql_select_db('databasename', $dbuk) or die ('Could not connect to website.co.uk Database');
mysql_select_db('databasename', $dbus)or die ('Could not connect to website.com Database');


        $result = mysql_query ("SELECT id, romancode, title FROM products WHERE pause <> 'on' AND romancart = 'uk'", $dbus);
        while ($row = mysql_fetch_object($result))
          {
          $romancode = substr($row->romancode, 2); // "removes us from romancode for uk stock check"
          echo "$row->title - $row->romancode ";
          
          $resultuk = mysql_query ("SELECT rcstock FROM products WHERE romancode = '$romancode'", $dbuk);
          while ($rowuk = mysql_fetch_object($resultuk))
          {
            if ($rowuk->rcstock == "available")
            {
            mysql_query ("UPDATE products SET rcstockuk = 'available' WHERE id = '$row->id'", $dbus);
            echo " available<br/>";
            }
            else if($rowuk->rcstock == "out of stock")
            {
            mysql_query ("UPDATE products SET rcstockuk = 'out of stock' WHERE id = '$row->id'", $dbus);
            echo " out of stock<br/>";
            }
            else if($rowuk->rcstock == "in stock")
            { 
            mysql_query ("UPDATE products SET rcstockuk = 'in stock' WHERE id = '$row->id'", $dbus);
            echo " in stock<br/>";
            }
          }mysql_free_result($resultuk);
          } mysql_free_result($result);
?>
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: Connecting to two DBs on two separate hosts - how?

Post by requinix »

Is the connection timing out? The code looks fine so...
User avatar
Christopher
Site Administrator
Posts: 13595
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Connecting to two DBs on two separate hosts - how?

Post by Christopher »

Does the UK server allow remote connections? Does it require SSL? Do you have the right server name, port, username and password?

PS - do not use the mysql_* library -- it is no longer maintained. Upgrade to mysqli or PDO.
(#10850)
User avatar
Celauran
Moderator
Posts: 6425
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Connecting to two DBs on two separate hosts - how?

Post by Celauran »

Christopher wrote:PS - do not use the mysql_* library -- it is no longer maintained. Upgrade to mysqli or PDO.
In fact, it was removed altogether in PHP 7. The migration from 5 to 7 is happening a lot more quickly than did 4 to 5 and mysql_ functions will leave sites depending on them completely broken. I've been banging on about this for years and now it's happening.
Post Reply