Page 2 of 2
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 02, 2013 12:16 pm
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?
Re: Can you open a separate hosted DB from within another?
Posted: Fri May 03, 2013 3:46 am
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);
??
Re: Can you open a separate hosted DB from within another?
Posted: Fri May 03, 2013 9:05 am
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 ?
Re: Can you open a separate hosted DB from within another?
Posted: Wed May 08, 2013 4:21 am
by simonmlewis
Help please - so close to sorting this, but don't see how this Update script works for each separate database.
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 09, 2013 4:13 am
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.
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 09, 2013 9:40 am
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.
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 09, 2013 10:22 am
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.
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 09, 2013 10:52 am
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
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 09, 2013 11:05 am
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?
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 09, 2013 11:18 am
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.
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 09, 2013 11:23 am
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.
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 09, 2013 12:25 pm
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
Re: Can you open a separate hosted DB from within another?
Posted: Thu May 09, 2013 12:48 pm
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.