Connect multiple tables in different databases? MySql.

Question

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.

Answer 1

I always use the full name for my tables. So if a table named tablea is in a database called dba, I would do the following query:

SELECT * FROM dba.tablea

This allows you to switch databases really easily. If you don't want to remember to do this, I also suggest defining constants for each of your tables, at which time you can put it in.

Answer 2

A good strategy for you might be to define a single MySQL user that has the same access to both databases. Something like this:

GRANT ALL ON siteinfo.* TO user@localhost IDENTIFIED BY 'password';
GRANT ALL ON sitedescriptions.* TO user@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Once you establish a connection as that user, you will have access to both databases, and can reference them easily by prefixing your table names with the database name.

SELECT * FROM siteinfo.sites;

You can even join your tables across databases in this way with no difficulty. So to answer your second question, to get all three description rows for a site, you could do this (assuming the sites have the same ids in each database, or the same name, or something unique that you can join on):

SELECT * FROM
    siteinfo.sites AS s LEFT JOIN 
    sitedescriptions.description AS d ON s.siteId=d.siteId
WHERE s.siteId=123;