Query with 2 databases/accounts

[yoghurt1977]

Hi,
I want to execute a query with mysql, getting data from two databases. These databases are stored in two different accounts, with different passwords.

Is it possible to execute a select statement with a join?

Thanks,
Christian

[jatar_k]

Welcome to WebmasterWorld yoghurt1977,

I don't believe you can join across db's. Your best bet is probably to grab you result set from one db and read it into an array. Then do the same from your second db and collate them with php.

[yoghurt1977]

Yes, I tried to do it this way. But it would not work if you have a ORDER BY in your selection on one table. The two arrays you want to combine would not have the same order!

Another option is to read the records from one table and then select the corresponding record from the other table record by record. But this is extremely slow.

Another idea?

[jatar_k]

You could get the array sorted properly using array_multisort [ca.php.net] or some of the other array sorting functions.

It depends on what you are doing with the data, how many records you have etc.

Why do you need to use 2 seperate db's?

[lorax]

I've been wondering about this very thing for quite some time. It might be possible to identify the database just like we do tables in a JOIN:

database.tablename.fieldname

but I haven't tried it. The issue - I think - will be in the use of mysql_select_db()

[yoghurt1977]

I think that would not work. You can select two databases with SELECT database.table.field, but not with different passwords.

Our Provider supports only 1 database per account. We can create unlimited accounts, but with different passwords.

I will try sorting the arrays. Thanks for your idea.

[coopster]

Sounds like the best bet so far. But why not create the table you need in the database that is lacking it? I'm guessing that's what jatar_k was leading to.

For the record, you actually can join tables from different databases. I've done it from both the command line as well as through a PHP script. Not that you would normally want to, but you can. However,

1. the databases must be on the same server
   
2. the user must have the correct priveleges on both databases (tables, columns, etc).

lorax is on the right track. The reason the databases need to be on the same server is because of the link identifier [webmasterworld.com].

[jatar_k]

it's just so darn messy. ;)

The 2 options that look the best are

1. grab data from remote server
- create table
- insert data from remote server
- query to your hearts content

You could even have an import scipt that works on cron to do this at set intervals which would make things nice and simple.

2. select from remote db into array
- select from local db and read into same array
- sort your array using php functions

The cron would probably be the best and then just do local joins etc.

[coopster]

Option 1 -- exactly. If the requirement is there to have the table in order to perform joins and execute the necessary queries, then create and populate the table in the database for the account which requires it. You can have only one database per account, yoghurt1977, but, hopefully, multiple tables in that database.