Page 1 of 1

Connecting to two DBs on two separate hosts - how?

Posted: Mon Apr 04, 2016 7:23 am
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);
?>

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

Posted: Mon Apr 04, 2016 7:56 am
by requinix
Is the connection timing out? The code looks fine so...

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

Posted: Mon Apr 04, 2016 10:05 pm
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.

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

Posted: Tue Apr 05, 2016 6:24 am
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.