/    Sign up×
Community /Pin to ProfileBookmark

Connect multiple tables in different databases?

Copy linkTweet thisAlerts:
Jul 26.2009

Hey, I am looking for a good way to connect to at least 2 databases in mysql using php and gathering information within each of those databases from tables.

The tables will have related information. eg. I have site names in one database(‘siteinfo’) in a table called ‘sites’. I also have the site descriptions in another database(‘sitedescriptions’) in a table called ‘descriptions’.

I can’t seem to get these two databases to talk to each other. Does anyone know how I can go about doing the above? I’m pretty new to php/mysql.

Also, in the ‘descriptions’ table there are three rows of information that are related to one site in the sites table. is it possible to get those rows to talk with only the one site that it relates to?

Thank you for any help.

to post a comment
PHP

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@MindzaiJul 26.2009 — Why are you using separate databases? Could you not combine the tables into a single db if they are related?
Copy linkTweet thisAlerts:
@spyderfusion02authorJul 26.2009 — Why are you using separate databases? Could you not combine the tables into a single db if they are related?[/QUOTE]

I would rather them be in different databases based on the current structure it's in.
Copy linkTweet thisAlerts:
@criterion9Jul 27.2009 — If they were on the same database but in separate tables you could use any number of 'JOIN's. Is there a particular reason they need to be in different databases rather than tables?
Copy linkTweet thisAlerts:
@NogDogJul 27.2009 — As long as the databases can be accessed by the same connection (i.e. they're on the same server and the same DB user has the necessary accesses to each D?, it's no big deal. Just use the database name as a prefix for table names
<i>
</i>SELECT a.col1, b.col2
FROM db_name_1.table_a AS a
INNER JOIN db_name_2.table_b as b ON a.col_x = b.col_y
Copy linkTweet thisAlerts:
@SrWebDeveloperJul 27.2009 — I would call that poor database design - the whole common sense concept of relational database setup is out the window. In layman terms, lets' say we have a database named English consisting of two tables, upper and lower - each containing the alphabet letters A-Z, and a-z respectively. Another table named Chinese contains a similar structure. If you attempted to join the "lower" of each so you had all English and Chinese lower case alphabetical letters, what would be the point since they're unique languages, you can't make real words mixing two alphabets, and anyone reading any combination of the two would only understand one, the other, or neither - and for those who know both, they still can't do anything useful like speak in Chinglish or Engese which don't exist! "Relational" is much more than name matching commonality, it's how the dataset is [U]perceived[/U] by the user to accomplish [U]sensible[/U] organization and structure of that data.


But guess what... [B]you can indeed do this[/B], anyway! heh

Here are details on how to do this in a myriad of ways, not all easy. Read carefully:

http://www.dottedidesign.com/node/14

So classify this as unusual, breaks alot of sound conventions that all database admins and developers are accustomed and fully expect. Breaking conventions because you simply can is NO justification to do so.

Be sure to add copious comments to your code generating these queries.

-jim
Copy linkTweet thisAlerts:
@NogDogJul 27.2009 — I agree that I would not do this by design, and I do not know off-hand if using separate DB's in this manner imposes any additional processing overhead in MySQL. But it [i]is[/i] possible should the need arise due to whatever circumstances that may be beyond your control.
×

Success!

Help @spyderfusion02 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.17,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...