[RESOLVED] Open connections to two databases on separate servers

bib_ak

My first post here - hello 🙂

I work as IT technician, and sometimes me and my colleagues need to fill inn a "work log" without internet access. We currently use a really old custom build version of Lotus Notes - were we can replicate and update the database as soon as we're back online. For several reasons we now want to use MySQL for hosting our data.

I have installed Apache with php and MySQL on my local XP computer. Works very well. So I thought - what about writing the work log to the local MySQL database on my laptop, and then update our central MySQL database on our Debian server once internet connection i established again?

So, naturally, I run into some difficulties - since I'm not working much with coding:

How to work with two database connections on two different servers simultaneously? Once I use 'mysql_connect' for the second database I can not get data from the first database any more!

My idea is to check the central database for new records since my last synchronization, receive updates from my colleagues and send my own work log. The database will grow large, so I won't send/receive the full database every time, only changes.

Any ideas are greatly welcome 🙂

MarkR

You can do this extremely easily, provided you ALWAYS supply a link_id to all your mysql_ functions.

There is a design bug in the mysql_ functions - you are allowed to omit the link parameter. If it is omitted, the implementation will choose almost arbitrarily** which connection to use, which could easily be the wrong one.

The fix is to NEVER omit the link identifier.

Other, more sane APIs (mysqli, PDO, other DB libraries) insist that you tell them which connection to work with.

Mark

** I actually think it uses the most recently connected or supplied one, or something. It's stupid. Avoid it.

bib_ak

Thank you for your reply.

So, I have to call MySQL like this then:

mysql_query("SELECT field FROM db1.table", $localdatabase)

And to ask the othe server:

mysql_query("SELECT field FROM db2.table", $externaldatabase)

The 'new_link' parameter simply does not work? Or didn't I understand the usage of it?

laserlight

So, I have to call MySQL like this then:

Yes.

The 'new_link' parameter simply does not work? Or didn't I understand the usage of it?

I have never used it myself, but I think you misunderstand it. It has to do with whether a new link is opened even though mysql_connect() is called with the same arguments as the previous call.

bib_ak

Alright, thanks - I understand it fully now 🙂