Page 1 of 1

Multiple Database Connections - showing ID from one.

Posted: Wed May 20, 2015 5:01 am
by simonmlewis
We have a few web sites where we connect to multiple databases as they use the same stock codes.

In this scenario, ( a slimmed down version ) we are trying to check if the same price reductions are across multiple sites.

Problem is, it's only echoing the ID from the first $db1 connection. I just cannot see why.
Have I made some stupid error here?

Code: Select all

$db1= mysql_connect("localhost","root","");
mysql_select_db('db1test', $db1);

$db2= mysql_connect("localhost","root","");
mysql_select_db('db2test', $db2);

$db3= mysql_connect("localhost","root","");
mysql_select_db('db3test', $db3);


$result = mysql_query ("SELECT * FROM products WHERE romancode = 'AG045'", $db1);
        while ($row = mysql_fetch_object($result))
        {
        $resultjag = mysql_query ("SELECT id AS jagid, datepricedrop, pricedrop, price FROM products WHERE romancode = '$row->romancode'", $db2);
        while ($rowjag = mysql_fetch_object($resultjag))
        {
        $resultjbbg = mysql_query ("SELECT id, datepricedrop, pricedrop, price FROM products WHERE romancode = '$row->romancode'", $db3);
        while ($rowjbbg = mysql_fetch_object($resultjbbg))
        {
      echo "$rowjag->jagid - $row->id";
      
      }}}

Re: Multiple Database Connections - showing ID from one.

Posted: Wed May 20, 2015 6:12 am
by requinix
Don't use multiple connections. If it's on the same server and the same user has access to all three databases then just use the one connection. Include the database name with the table.

Code: Select all

SELECT * FROM db1test.products WHERE romancode = 'AG045'
Assuming that's not your real code (because otherwise it would be very silly), use JOINs to use multiple tables in a single query.

Re: Multiple Database Connections - showing ID from one.

Posted: Wed May 20, 2015 6:48 am
by simonmlewis
Locally they are on the same machine. When it's live, they will be on separate servers and separate IPs.

Re: Multiple Database Connections - showing ID from one.

Posted: Wed May 20, 2015 7:22 am
by simonmlewis
I think what's happening is that for some reason it won't work locally, only live.
It works perfectly, live. Possibly because it's DB Conn is on a different server.
But bearing in mind they are diff DB names, surely it should work locally anyway.
It seems to pick up only the first DB conn.

Re: Multiple Database Connections - showing ID from one.

Posted: Wed May 20, 2015 7:48 am
by Celauran
You could create aliases in your hosts file if you're insistent on keeping that set up. Queries inside loops with queries inside loops... that's going to result in some pretty terrible performance. I'd strongly advise cleaning that logic up and keeping your queries to a minimum.

Re: Multiple Database Connections - showing ID from one.

Posted: Wed May 20, 2015 7:53 am
by simonmlewis
I need to query on just three table fields on one table. So instead I am running the query right at the top, once I have the row fron the "current" table in memory. Assigning those results into three individually named variables (NULL'd beforehand), and then using that instead.

So far, it seems to be working.
It doesn't explain why it wouldn't work locally, but if I can get it right LIVE, I will see if my tweaks work locally.

I Agree that loop on loop is not good.