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($qu ery, $dbConnection);
$resultArray = mysql_fetch_row ($result);
$i = 0;
foreach ($resultArray as $data)
{
$tableName = mysql_field_tab le($result, $i);
$fieldName = mysql_field_nam e($result, $i);
$returnVal["$tableName.$fi eldName"] = $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_tab le($result, $i);
$fieldName = mysql_field_nam e($result, $i);
$returnVal["$dbName.$table Name.$fieldName "] = $data;
$i += 1;
}


Any suggestions?

Thanks!
CF

[Pjotr Wedersteers]

Re: Getting database name for a given query

ChronoFish wrote:[color=blue]
> In PHP you can retrieve the table.column name of a MySQL
> query doing
> something like this:
>
> $result = mysql_query($qu ery, $dbConnection);
> $resultArray = mysql_fetch_row ($result);
> $i = 0;
> foreach ($resultArray as $data)
> {
> $tableName = mysql_field_tab le($result, $i);
> $fieldName = mysql_field_nam e($result, $i);
> $returnVal["$tableName.$fi eldName"] = $data;
> $i += 1;
> }
>
>
>[/color]
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]

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
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" <pjotr@wederste ers.com> wrote in message
news:418e5974$0 $48933$e4fe514c @news.xs4all.nl ...[color=blue]
> ChronoFish wrote:[color=green]
>> In PHP you can retrieve the table.column name of a MySQL
>> query doing
>> something like this:
>>
>> $result = mysql_query($qu ery, $dbConnection);
>> $resultArray = mysql_fetch_row ($result);
>> $i = 0;
>> foreach ($resultArray as $data)
>> {
>> $tableName = mysql_field_tab le($result, $i);
>> $fieldName = mysql_field_nam e($result, $i);
>> $returnVal["$tableName.$fi eldName"] = $data;
>> $i += 1;
>> }
>>
>>
>>[/color]
> use mysql_db_name ($dbConnection)
> HTH
> Pjotr
>
> PS mysql_dbname () can be used still, but is deprecated,
> so make sure you use both underscores.
>
>[/color]

[Steve]

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,
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_arr ay( $Result, MYSQL_ASSOC ) )
{
print $Row[ 'db1_column1' ] . "\n";
print $Row[ 'db1_column2' ] . "\n";
print $Row[ 'db2_column1' ] . "\n";
}
// ...


---
Steve

[ChronoFish]

Re: Getting database name for a given query

"Steve" <googlespam@nas tysoft.com> wrote in message news:<109991016 5.775993.158000 @z14g2000cwz.go oglegroups.com> ...[color=blue]
> 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_arr ay( $Result, MYSQL_ASSOC ) )
> {
> print $Row[ 'db1_column1' ] . "\n";
> print $Row[ 'db1_column2' ] . "\n";
> print $Row[ 'db2_column1' ] . "\n";
> }
> // ...
>
>
> ---
> Steve[/color]


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