php mysql connection confusion

Question

Hi guys, had a bit of confusion going around in work earlier. Thought id run it by yous to see if anyone knows wats going on.

Were working on an internal administration system for our client, and its rite about to launch. Its comprised of TWO MySQL databases on the server [db_1 and db_2] and a PHP front end. [Both databases contain several tables].

There are maybe 90 different PHP files, some of which require a connection to our databases and ALL of these connections are made via a single PHP function which explicitly connects to the first database mentioned above [db_1] and supplies the login and password. And this works fine.

However, our second database, db_2, does not seem to require its own login and password to access its contents.
As soon as we connect to db_1, we seem to have full access to db_2, as long as we use the Full-Name for our tables [ie: db_2.usersTable] -> ("SELECT * FROM db_2.usersTable WHERE...").

And this is what was causing much confusion.

My question is this: Once you connect to a database on a server, do you have access to other databases on that server, or are we overlooking something???

Any feedback greatly appreciated lads...

Answer 1

You usually don't access a database server by a specific database, but by a user that has access to one or more databases.

for example:

mysql_connect("localhost", "user", "password") or die(mysql_error());

connects to the server and not a specific database.

Once connected to the database server you have access to all databases for which that user has permission. You just need to specify the database name in your queries if there are multiple databases that aren't the default.

mysql_select_db("myTable") or die(mysql_error());

sets myTable as the default, but you can still access other tables that the user has permission to.

Answer 2

I might be wrong, but doesn't PHP only connect and authenticate with the MySQL server with a user? And thus, based on that user's permissions, PHP can select any database that user has access to. I would check the permissions for the user you are connecting with...

Answer 3

Once you connect to a database on a server, do you have access to other databases on that server

If you have permissions to other databases, then yes. When you connect, you're connecting to the server and setting your default database to the one specified. Which is why you have to explicitly specify db_2 when you want to access it, but you don't have to specify db_1.