Querying multiple databases and table name qualification

cannondale

Guys,

I'm have a test script (below) that am using to try and understand table name qualification when accessing multiple databases.

In the [Connect section], I have two connection object vars, $conn1 (DB1) and $conn2 (DB2).
In the [query1 db1 section], the code performs fine WITHOUT qualifying the testtbl1 with the database name (db1).

However, in the [query2 db2 section], an error is produced ("No database selected") WITHOUT qualifying the testtbl2 with the database name (db2).
I am providing the connection object var $conn2 (for DB2) in the mysqli_query call, which would appear to tell the mysqli_query which DB to query.

What am I missing?


//Connect
$conn1 = mysqli_connect('localhost', 'user'], 'pw', 'db1'); 
$conn2 = mysqli_connect('localhost', 'user'], 'pw', 'db2');

//query1 db1
$query = "SELECT testcol1 FROM testtbl1";   //works fine with table name NOT fully qualified

$result = mysqli_query($conn1, $query);

echo("Number of rows " . mysqli_num_rows($result) . "<br>");


//query2 db2
//$query = "SELECT testcol1 FROM db2.testtbl2";   //works fine with table name fully qualified
$query = "SELECT testcol1 FROM testtbl2";   //fails with mysql error: "No database selected"

$result = mysqli_query($conn2, $query);

echo("Number of rows " . mysqli_num_rows($result) . "<br>");

bradgrafelman

Why are you using two separate connections?

cannondale

The tables reside in different databases.

bradgrafelman

Right, but it looks like the two databases are on the same host and can be accessed with the same credentials. Thus, there appears to be no reason to complicate things by adding a separate connection.

cannondale

Thanks for the information. I reduced the connections to one for testing purposes. However, I believe one connection would allow access to both databases ONLY if the user credentials were identical. Correct?
Unfortunately, the results were the same.

If two connections were required due to different user credentials, I don't understand why the mysqli_query call ignores the connection object var ($conn2 for DB2), which would appear to tell the mysqli_query which DB to query. Instead, it requires the table name in the second DB to be fully qualified with the DB name.

I'ts clear I'm missing a piece to the puzzle.

johanafm

cannondale;11033553 wrote:
I believe one connection would allow access to both databases ONLY if the user credentials were identical. Correct?

Not necessarily. One connection would allow access to both databases if the specified user is granted such access from the connecting address. Do note that you may very well have to distinct users, where each of the users has access to "both databases" while neither user can access any table that the other user can access. That is, user1 might access db1.table1 and db2.table1, while user2 can access db1.table2 and db2.table2. Or they may have access to the exact same tables, except that user1 has SELECT access only while user2 has UPDATE access only.

Moreover, user1 connecting from localhost may have access to db1, while the same username connecting from web-server-1 has access to db2.

cannondale;11033553 wrote:
Instead, it requires the table name in the second DB to be fully qualified with the DB name.

You may very well have a connection without having selected a database. While "database" is often used for everything, it may be clearer if you state that the connection is made to the server after which you must specify which schema to use. One server or RDBMS may contain several schemas (or databases).

However, looking at the code you presented initially, it wont' work due to errors. Start by correcting those so that you may actually run the code and inspect what happens.

Also note that you can specify schema in several different ways.
1. when creating the connection - sets default schema to be used
2. [man]mysqli::select_db[/man] - sets default schema to be used
3 The SQL statement "use", as in: use schema_name; Although considering that there is a specific API call for this, using select_db is most likely better - sets default schema to be used
4. qualifying table identifiers with schema name, as in: SELECT stuff FROM schema_name.table_name - has to be made every time a non-default schema is used

cannondale

Thanks for your detailed reply. With respect to using one DB connection or two, in this particular case, table level access security is not being used. A user either has access to the DB (and all tables in it) or they don't. In addition, all DB's are on the same localhost.

However, my original question still stands, which is:

If I am providing the connection object var $conn2 (for DB2) in the mysqli_query call, WHY is it required for me to qualify the table name with with the DB name???

The mysqli_query function REQUIRES the DB connection object in it's first parm, which SHOULD tell mysqli_query which DB to query without qualifying the table name with with the DB name for each query.

4. qualifying table identifiers with schema name, as in: SELECT stuff FROM schema_name.table_name - has to be made every time a non-default schema is used

If using only one DB connection, then it's clear that either the the DB must be selected or the table must be qualified with with the DB name before executing mysqli_query.

bradgrafelman

Only thing I can come up with is that you appeared to do zero error checking to ensure that the connection was successfully opened and that the DB was selected. For example, after verifying the connection itself was successful, one thing you could try would be to execute the query:

SELECT DATABASE();

on both connection objects and see what the result is.

Also, do you have error_reporting set to E_ALL (or better) and display_errors set to On?

cannondale

bradgrafelman,

Thanks for your suggestions. They actually help illuminate the problem.

I included error_reporting(E_ALL) at the top of the script. However, no errors were found. I then included SELECT DATABASE() after each connection.
Note that error checking is included in my test script but removed from the code in my original post for simplicity reasons.

However, the SELECT DATABASE() returned no database after the second connection, which was due to a fat finger typo in the database name (4th parm) in mysqli_connect. Interestingly, this did NOT produce a mysqli_connect error. I am wondering if this is because the mysqli_connect database parm is optional? My error checking code after mysqli_connect is:

if(($conn2) === FALSE) die("mysqli_connect error: " . mysqli_connect_error());

The good news is that once the typo in the database name (4th parm) in mysqli_connect was corrected, the following query worked perfectly
WITHOUT qualifying the table name with the DB name. As I suspected, the connection object var $conn2 (for DB2) in the mysqli_query call, DOES in fact tell the mysqli_query which DB to query.

$query = "SELECT testcol1 FROM testtbl2";
$result = mysqli_query($conn2, $query);

bradgrafelman

cannondale;11033593 wrote:
Interestingly, this did NOT produce a mysqli_connect error. I am wondering if this is because the mysqli_connect database parm is optional? My error checking code after mysqli_connect is:
if(($conn2) === FALSE) die("mysqli_connect error: " . mysqli_connect_error()); 

It's actually not so "interesting" once you think about it. MySQL doesn't care which database you want to use as you connect - that only comes into play later, after you've successfully established a connection. Likewise, your check for an error here is only half complete.

I would suspect that [man]mysqli_error/man would have been more helpful here since it would tell you about the error (which, again, occurred after the connection was established successfully) that occurred when the client tried to "USE" the incorrect database name.

cannondale;11033593 wrote:
As I suspected, the connection object var $conn2 (for DB2) in the mysqli_query call, DOES in fact tell the mysqli_query which DB to query.

Not really. At least, that's not what it directly does.

Instead, it tells the driver which connection to use when executing the query. Remember, you've got two separate connections open to the MySQL server here (scalability note: if 50 people requested this page at once, you'd have 100 open DB connections).