Working with Multiple Databases under MySQL, PHP and phpMyAdmin

I couldn't find clear and concise guidance, so here is my offering (after much miserable experimentation):

Establish a Connection, eg

$glob_dbConnection = mysql_connect($db_host, $db_user , $db_pass) or die ('D5518 Error connecting to mysql');


Unless you are doing anything fancy, you don't actually need to refer to $glob_dbConnection again, as this becomes the default.

This connection then allows access to as many databases as $db_user has privileges for, so you can specify queries like this:

$sql="SELECT databaseCommon.products.*, databaseCustomer.purchases.* from databaseCommon.products INNER JOIN databaseCustomer.purchases ON ...

But setting the Privileges is a bit of a pain, and (to me) far from obvious.

In phpMyAmin, click on the link to "Server: localhost" to get at all the top-level settings.

Clicking on the Privileges link will take you to the User Overview. This allows you to Add an extra user, and lists existing users, and tells what Global Privileges each has, but it says nothing about which databases the user can access.  To fix that, click on Edit Privileges, and find the second section, which is marked "Database-specific privileges". There is a select box marked "Add privileges on the following database" - with a default of "Use text field".
Using the default, you can type in the database name, but it is safer to open the Select Box and choose the database name from there.

Having done this, press Go. The page reloads and you can now specify which actions are allowed for this user for this database (eg Select+Insert+Update but not Delete, perhaps). Tick the boxes and press Go. You may well get an error message, eg
#1141 - There is no such grant defined for user 'billinguser' on host 'localhost'
which appears to be spurious. Click on the link (eg 'billinguser'@'localhost') and it will now offer you the chance to edit Global privileges at the top of the page; further down, it lists the Database-specific privileges and you will see the specific permissions that you have added.



Good Luck