Getting database name for a given query

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

In PHP you can retrieve the table.column name of a MySQL
query doing
something like this:

$result = mysql_query($query, $dbConnection);
$resultArray = mysql_fetch_row($result);
$i = 0;
foreach ($resultArray as $data)
  $tableName = mysql_field_table($result, $i);
  $fieldName = mysql_field_name($result, $i);
  $returnVal["$tableName.$fieldName"] = $data;
  $i += 1;

The problem is that I am selecting across databases and it
conceivable that two tables will have the same name.  How do
I get the
Database Name for a given field?

I would want something like this:

foreach ($resultArray as $data)
  $dbName = mysql_field_db($result, $i);
  $tableName = mysql_field_table($result, $i);
  $fieldName = mysql_field_name($result, $i);
  $returnVal["$dbName.$tableName.$fieldName"] = $data;
  $i += 1;

Any suggestions?


Re: Getting database name for a given query

ChronoFish wrote:
Quoted text here. Click to load it
use mysql_db_name ($dbConnection)

PS mysql_dbname () can be used still, but is deprecated, so make sure you
use both underscores.

Re: Getting database name for a given query

Thanks Pjotr for the quick response.  Unfortunately
mysql_db_name() will only show me the Database that I am
connected to.  It will not display the database that a
resultant column is coming from.

Some of my queries span databases.  A made up example

SELECT database1.some_table.column1,
database1.some_table.column2, database2.some_table.column1
FROM database1.some_table, database2.some_table

I would like to put the results in an associative array -
but just using table name and column name is not enough for
the key - as there could be tables from two databases with
the same name.


Quoted text here. Click to load it

Re: Getting database name for a given query

If there are column name clashes the simplest way is to alias the
column names to identify which database/table they were selected from,

SELECT database1.some_table.column1 AS db1_column1,
database1.some_table.column2 AS db1_column2,
database2.some_table.column1 AS db2_column1
FROM database1.some_table,

The columns can be accessed using these aliases, thus:

// ...
$Result = mysql_query( $Query, $Connection );
while( $Row = mysql_fetch_array( $Result, MYSQL_ASSOC ) )
print $Row[ 'db1_column1' ] . "\n";
print $Row[ 'db1_column2' ] . "\n";
print $Row[ 'db2_column1' ] . "\n";
   // ...


Re: Getting database name for a given query

Quoted text here. Click to load it

Hi Steve,

Thank you for quick response.

You are 100% correct that the simplist way to avoid the probelm is to
use an alias.  However that assumes that I am the person generating
the queries which I'm not.  I suppose you could claim that this
becomes a "training" issue but this really re-defines the

I'm surprised that PHP does not give a way to get the database name
from a column result given that it can get the column name and table
name, and that PHP (at least the developers) are aware of the ability
to query across databases....

I would like to avoid editing the queries prior to using them as this
becomes a much more complex solution - and in my opinion not a very
good one - however it appears that I may have to force the queries
into an alias format....


Site Timeline