Can you open a separate hosted DB from within another?
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?
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.
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?
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);
??
Like the previous submission: .....", $dbuk);
??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
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?
Do you mean it might be:
???
So www.fred.es.products AS sep ?
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.
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?
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.
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?
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.mikosiko wrote:no SELECT... no LOOP... apply the same for the site_ieCode: Select all
UPDATE site.products AS sp JOIN site_es.products AS sep ON CONCAT('es',sp.romancode) = sep.romancode SET region_es = 'yes'
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can you open a separate hosted DB from within another?
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
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.
.. AFAIK that is not true in MYSQL.. apologize for that."....you can reference the remote table as host.db.tablename"
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?
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.
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.
All the best from the United Kingdom.
Re: Can you open a separate hosted DB from within another?
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
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?
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?
Am I in the right ball park here?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can you open a separate hosted DB from within another?
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 wrote:..... I have to have duplicated tables, of what's remote? And then query that.
Am I in the right ball park here?
-
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?
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.
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.
All the best from the United Kingdom.
Re: Can you open a separate hosted DB from within another?
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
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?
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.
Sounds like there is NO extra table.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.