Can you open a separate hosted DB from within another?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

Ok yes I see it now. How would I then do the same for IE? Or do I just do the same script, but for IE instead?
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: Can you open a separate hosted DB from within another?

Post by simonmlewis »

I've just looked at this JOIN script again - and I can't see in there, where it knows to connect to the UK db and where to the ES db?
Like the previous submission: .....", $dbuk);
??
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: Can you open a separate hosted DB from within another?

Post by simonmlewis »

Do you mean it might be:

Code: Select all

UPDATE localhost.products AS sp
   JOIN www.fred.es.products AS sep ON CONCAT('es',sp.romancode) = sep.romancode
 SET region_es = 'yes'
???

So www.fred.es.products AS sep ?
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: Can you open a separate hosted DB from within another?

Post by simonmlewis »

Help please - so close to sorting this, but don't see how this Update script works for each separate database.
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: Can you open a separate hosted DB from within another?

Post by simonmlewis »

mikosiko wrote:

Code: Select all

 UPDATE site.products AS sp
   JOIN site_es.products AS sep ON CONCAT('es',sp.romancode) = sep.romancode
 SET region_es = 'yes'
no SELECT... no LOOP... apply the same for the site_ie
I still don't see in here, where you have told it which two database to apply these two: how it knows "site_es.products" connects via the ES dbconn, and site.products is for UK.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Can you open a separate hosted DB from within another?

Post by mikosiko »

hmmm... I think that by mistake I mislead you, basically because in my example I did use syntax from other RDBMS that allows what I did mention in my point # 2 of my first answer to you
"....you can reference the remote table as host.db.tablename"
.. AFAIK that is not true in MYSQL.. apologize for that.

However, in my example I was thinking also in what I did mention in my point #3 ... Use of FEDERATED ENGINE should simplify your process and allow you to use an UPDATE as the one I shown.

please read the link the I did provide originally to understand how FEDERATE ENGINE works... basically in your local machine you create a new table (empty) with FEDERATED ENGINE as the storage engine, that serve as a pointer to your remote server table (site_es per example), after successfully create this new table just run a SELECT to validate that you can access the remote data without problems... if everything works then you can use that new table in your UPDATE more or less in the format that I did provide before... the new table act like as it were in your local server

here is another link that have an example http://stackoverflow.com/questions/8103 ... lect-query.

2 conditions for this to work:
a) The FEDERATED ENGINE MUST be active in your localserver... if is not it can be enabled starting Mysql with the --federated switch (google how to do this).
b) The user used to access the remote server MUST have the proper privileges in that server (access/select and so on).

hope this help you to solve your issue.... don't panic with the FEDERATED ENGINE... it sound difficult but is not.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

I'ved tried to read it and take it all in, but I am lost.

I simply want to check if it's on one server/db. If it is, update the existing DB that we use daily.
And do this for 2 or more web sites.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Can you open a separate hosted DB from within another?

Post by mikosiko »

unfortunately If you cannot follow the examples for yourself is hard to help you... let's try this SIMPLE ONE
http://winashwin.wordpress.com/2012/08/ ... ted-table/

there is the simplest example of how to implement it... just follow exactly what is there and you SHOULD be able to make it works.... and before you dig into it check if the FEDERATED engine is available running in whatever mysql client that you use (phpmyadmin or other) this command
SHOW ENGINES

if FEDERATED is not available and running in the list you must activate it. (--federated switch... google how to)

if you can't follow this you have a bigger problem to start with .... good luck
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

Ok, so the way it reads to me, is that on the local site (from where I am drawing in the information to query), I have to have duplicated tables, of what's remote? And then query that.

Am I in the right ball park here?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Can you open a separate hosted DB from within another?

Post by mikosiko »

simonmlewis wrote:..... I have to have duplicated tables, of what's remote? And then query that.

Am I in the right ball park here?
no duplicated tables... just the table definition... no the data... and yes query that.... the easy way to test... just create the tables in the example and play with it... you will figure it out.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

What concerns me here, is having to create SIX lots of extra database tables so I can run this query. Six, because we need to run checks on our site external web sites.

When the Loop, although time consuming on the server, would only be run about once a month, maybe twice a month.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Can you open a separate hosted DB from within another?

Post by mikosiko »

then you have your concerns in the wrong place my friend....

create 6 table definitions (no data) take probably 5 minutes and you will do it just 1 time, and after that run 1 UPDATE sentence per each external site.... with the option to run queries in a loop that you have... let's see... let's say that you have locally 100 "romancodes" and after filtering with the first query you get 20 records... then you do queries in a loop.. i.e 20 queries per each external site... total 120 queries... against only 6... see the pattern?... which option do you think should be more efficient?

any way... do whatever suit you best
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you open a separate hosted DB from within another?

Post by simonmlewis »

The issue is, as you can tell, it didn't make sense to me, as I read it as another table kinda between the others.
Sounds like there is NO extra table.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply