In our last episode (How I constructed a cross database query in MySQL) I learned how to construct a cross database query in MySQL. This worked great but when our hero tried to use this newfound knowledge in PHP he found his best friend FAIL waiting for him.
I took a look at
mysql_select_db for PHP. This seems to imply that if I want to use MySQL with PHP, I have a couple of choices:
mysql_select_dbbut be stuck with only using one db at a time. This is our current setup and putting a database as a namespace identifier doesn't seem to work (it works fine in the MySQL shell so I know it's not a problem with our MySQL server setup).
mysql_select_db. From some of the examples I've seen, this seems to mean that I have to specify the db for every query that I make. This makes sense since I haven't used
mysql_select_dbto tell PHP what db I want to access. This also makes sad since I don't want to go through all my code and prepend a db name to every query.
Is there something better than this? Is there a way for me to do a cross db MySQL query in PHP without having to something crazy like (2)?
CLARIFICATION: None of the proposed answers actually let me do a cross db query. Instead, they allow me to access two different DBs separately. I want a solution that allows me to do something like
SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where ... NOT just make different queries to different DBs. For what it's worth, (2) doesn't work for me.
You will need your databases to run on the same host.
If so, you should be able to use mysql_select_db on your favourite/default db and manually specify a foreign database.
If your databases run on a different host, you won't be able to join directly. But you can then make 2 queries.