Getting database name for a given query

[ChronoFish]

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
is
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?

Thanks!
CF

[Pjotr Wedersteers]

ChronoFish wrote:

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;
}

use mysql_db_name ($dbConnection)
HTH
Pjotr

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

[ChronoFish]

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
follows:

SELECT database1.some_table.column1,
database1.some_table.column2, database2.some_table.column1
FROM database1.some_table, database2.some_table
WHERE database1.some_table.id = database2.some_table.id
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.

Thanks!
CF

"Pjotr Wedersteers" <pj***@wedersteers.com> wrote in message
news:41***********************@news.xs4all.nl...

ChronoFish wrote:

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;
}

use mysql_db_name ($dbConnection)
HTH
Pjotr

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

[Steve]

If there are column name clashes the simplest way is to alias the
column names to identify which database/table they were selected from,
thus:
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,
database2.some_table
WHERE database1.some_table.id = database2.some_table.id
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";
}
// ...
---
Steve

[ChronoFish]

"Steve" <go********@nastysoft.com> wrote in message news:<10**********************@z14g2000cwz.googleg roups.com>...

If there are column name clashes the simplest way is to alias the
column names to identify which database/table they were selected from,
thus:
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,
database2.some_table
WHERE database1.some_table.id = database2.some_table.id
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";
}
// ...
---
Steve

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
requirements.

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....

Thanks!
Christopher