Query multiple databases

[Sillysoft]

Hello,
I am using Mysql 4.0 and was wondering is there a way to query 2 different databases in one query? Both databases are on the same machine. Right now the only way I find I can do this is query the first db, then go through a while statement and then query the next db on each pass to get the required info.

But I thought I read somewhere that was bad. So was wondering if there is a better way. I searched around and saw some posted messages that this cannot be done, but I wanted to come here first and check.

Thanks
Silly

[redemption]

I'm pretty sure you can, if they are in the same database server.

Just make sure to qualify them in the db_name.table_name.field_name syntax.

SELECT db1.some_table.fieldy, db2.another_table.fieldx
FROM db1.some_table, db2.another_table

[Egghead]

But I thought I read somewhere that was bad.

Putting queries into loops will put a performance hit on your pages but I think that is the only reason it is bad. Remember: It may work fine on your local system in testing but on some very busy host's servers the extra performance costs may be far more noticeable.

[Son Nguyen]

Doing the loop is significantly slower than mySQL join mechanism. Avoid that if you could.

[sporkit]

Putting queries into loops will put a performance hit on your pages but I think that is the only reason it is bad. Remember: It may work fine on your local system in testing but on some very busy host's servers the extra performance costs may be far more noticeable.

Awwwww crap! Nobody told me your not supposed to use more than one database! Since i host my own webserver i created a seperate database for my sites content management system, forum, and picture gallery!

The problem punched me in the face when I wanted to include some code from my CMS in my photo gallery. Who thought it would be so hard to get info two databases like that?!

Heh i guess my next step is to try and transfer all this into one database now.

Doh!