Multiple Database Connections - showing ID from one.

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

Multiple Database Connections - showing ID from one.

Post 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";
      
      }}}
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: Multiple Database Connections - showing ID from one.

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Multiple Database Connections - showing ID from one.

Post by simonmlewis »

Locally they are on the same machine. When it's live, they will be on separate servers and separate IPs.
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: Multiple Database Connections - showing ID from one.

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Multiple Database Connections - showing ID from one.

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Multiple Database Connections - showing ID from one.

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply