Query Two Databases

[Herbster]

When I created database #1, DA wanted a username and password. Fine. When I created database #2, same trip, but DA would not accept the same username and password. Said user already existed (or something like that.) Seems to me a single user could have access to more than one database...

Anyway, I now have a need to query two databases, but they are not both accessible under a single login.

Anyone know how to do this?

tia

[dbmasters]

First question, why two databases? Why not just put all those tables under one database? It would make everything a whole lot simpler.

[Herbster]

First answer, it would make everything a whole lot messier and more confusing. I'd like to keep the message board and mods in a separate database. As last resort, I could combine them, but I'd rather not. Multiple dbs appeared in phpMyAdmin uner one of the Ensim configuations. Seems it should be possible with DA.

My first question, what good reason is there for not allowing a single user access to multiple dbs?

[eugene]

Herbster-
Your main account, i.e. your DA username/password, has access to ALL of your databases.

I believe the reason you can't add a new user that has access to multiple db's is that the DA routines try to do an INSERT INTO request to the master mysql database instead of a REPLACE INTO, this means there will be an error if the username already exists.

-Eugene

[eugene]

Herbster-
If you absolutely want to have a user access multiple databases, I can help you set it up. It is possible, and I have done it. PM me.

[dbmasters]

It would be MORE confusing to have everything in one database? Whoa, don't hear that every day ;) Seriously tho, I guess everyone has their own DBA techniques, I just can't see the benefit, however, like Eugene said, the primary account login should work across all databases...

Every users database(s) all reside side by side on the same servie, the reason you can't create the same user across multiple db's is the same reason you can't create two databases on the same server. If I already named a database "mydb" and you came along on my same server and wanted one named "mydb" it wouldn't let you because, while we are different users, we still share the MySQL database server. Same applies to user accounts.

[eugene]

Originally posted by dbmasters@Feb 7 2004, 04:31 PM
Every users database(s) all reside side by side on the same servie, the reason you can't create the same user across multiple db's is the same reason you can't create two databases on the same server. If I already named a database "mydb" and you came along on my same server and wanted one named "mydb" it wouldn't let you because, while we are different users, we still share the MySQL database server. Same applies to user accounts.
Dan is right about the default setup and the rationale for the default setup. However, it is possible for one to modify the access to your own databases, allowing for one user to access multiple databases as I mentioned in my previous post. As this method is somewhat special and maybe even dangerous to those not so familiar with MySQL and DirectAdmin, I'd rather not post the directions and thus give Nick and Joe headaches as a result of users wiping out their entire sites--or something else ugly.
-Eugene

[Herbster]

Originally posted by eugene@Feb 7 2004, 01:29 PM
Herbster-
Your main account, i.e. your DA username/password, has access to ALL of your databases.

-Eugene
Big "Doh!" to me for this one. I did read the docs. I did read the docs. I just didn't understand them. I ASSumed that because I had to create a user, I would have to be the user to access the db.

This is perfect: phpMyAdmin gives me access to all dbs on one screen. Queries should work fine.

As that solves my immediate problem, I'll pass on the "dangerous" technique, but thanks. ;)

dbmasters - In this instance, if I want to change message board software, having all the associated tables in a separate db makes it easier to swap in a different mb w/o picking through the tables wondering what belongs to what or prepending strings to each table. I may be wrong, but it soothes this anxious mind.

Thanks for the help guys! :D