Hey, I'm searching for a great way to connect with a minimum of 2 databases in mysql using php and gathering information within all of individuals databases from tables.
The tables may have related information. eg. I've site names in a single database('siteinfo') inside a table known as 'sites'. I additionally possess the site explanations in another database('sitedescriptions') inside a table known as 'descriptions'.
I can not appear to obtain both of these databases to speak to one another. Does anybody understand how I'm able to start doing the above mentioned? I am pretty a new comer to php/mysql.
Also, within the 'descriptions' table you will find three rows of knowledge that are based on one site within the sites table. can you really get individuals rows to talk to just the one site that it requires?
Appreciate any help.
A great technique for you may be to define just one MySQL user which has exactly the same use of both databases. Something similar to this:
GRANT ALL ON siteinfo.* TO user@localhost IDENTIFIED BY 'password'; GRANT ALL ON sitedescriptions.* TO user@localhost IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
When you begin a connection as that user, you'll have use of both databases, and may reference them easily by prefixing your table names using the database title.
SELECT * FROM siteinfo.sites;
You may also join your tables across databases in by doing this without any difficulty. To answer your next question, to obtain the 3 description rows for any site, you could do this this (presuming the websites have a similar ids in every database, or even the same title, or something like that unique that you could join on):
SELECT * FROM siteinfo.sites AS s LEFT JOIN sitedescriptions.description AS d ON s.siteId=d.siteId WHERE s.siteId=123;
I usually make use of the full reputation for my tables. Therefore if a table named tablea is within a database known as dba, I'd perform the following query:
Choose * FROM dba.tablea
This enables you to definitely switch databases really easily. If you won't want to remember to get this done, I additionally suggest determining constants for every of the tables, after which place the it in.