473,241 Members | 1,627 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

Getting database name for a given query


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
Jul 17 '05 #1
4 5130
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.
Jul 17 '05 #2
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.

Jul 17 '05 #3

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

Jul 17 '05 #4
"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
Jul 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Oleg Shnayderman | last post by:
Hi All, I am very new to C# as well as to SQL. I need to write an application which is connecting to SQL DB and getting all the user's tables and after create a dataset for all the tables in the...
4
by: Sean Shanny | last post by:
To all, Running into an out of memory error on our data warehouse server. This occurs only with our data from the 'September' section of a large fact table. The exact same query running over...
2
by: Anantha | last post by:
Dear All, One day our Windows 2000 Server OS crashed, so our NT admin has re-installed the OS on C: drive. Fortunately we kept our database file and installation in F: drive. When we...
3
by: itfetish | last post by:
I've been programming a bit in PHP and then recently learnt classic ASP for projects at work, now I'm working on another project that they want done in .net. I've got my head around web parts, that...
47
by: Jo | last post by:
Hi there, I'm Jo and it's the first time I've posted here. I'm in process of creating a database at work and have come a little unstuck.....I'm a bit of a novice and wondered if anyone could...
22
by: Sri | last post by:
All Recenetly our shop migrated to DB2 V8 from V7. We are in IBM System Level: z/OS 1.6.1 @ RSU 0702. Processor : IBM 2064-1C7 (z/900) # 1B89 Mode: 64-bit One of my application is facing...
4
by: jehugaleahsa | last post by:
Hello: Say I were to query the schema tables from a database management system. Say I got a DataType field that held the string 'VARCHAR2'. Now, I know that VARCHAR2 associates to System.String....
9
by: jerry101 | last post by:
I am currently using a version of phpmyadmin via heartinternet. I can connect to my database fine, but I cannot seem to input any database into the table I have created. the database is called...
1
by: raghuvendra | last post by:
Hi I have a jsp page with 4 columns: namely Category name , Category order, Input field and a submit button. All these are aligned in a row. And Each Category Name has its corresponding Category...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.