Concat Table Name to Column Name

[bsmither]

I have an existing two table structure where sub-data is related to main data in a many-to-one relationship.

 

There are several identically named columns in both tables.

 

(The PHP database class will return an associative array that did not deal with identical keys in the row.)

 

I'm LEFT JOINing the sub-data to the main-data tables.

 

When using a stand-alone query browser (to experiment), the display shows all columns, even those column names that appear more than once in the row.

 

Is there a general approach to making keys (column names) unique strictly using a MySQL SELECT statement?

 

Perhaps using table aliases, how can I concat the alias to the columns that come from each table?

 

What I am trying to avoid is giving an alias to each column in a list of column names, such as:

SELECT

C.name AS C_name

C.numb AS C_numb

D.name AS D_name

D.numb AS D_numb

etc.

 

I am wanting more like the result one could guess would be from:

C.* AS C_*

D.* AS D_*

 

Or better:

* AS CONCAT(__TABLE__, '_', *)

if __TABLE was a magic constant (like PHP's __LINE__, __FILE__, etc).

 

[Barand]

The way to do it is to give the duplicate column names a column alias.

 

You should NOT be using "SELECT * " anyway, but specifying the columns that you want (except in rare situations where you really do want every column, or maybe when testing).

[mac_gyver]

The PHP database class will return an associative array that did not deal with identical keys in the row.

 

 

all the columns you have selected are actually in the result set sent to php. this feature is the result of using an associative fetch function where a later column with the same name as an earlier column overwrites the previous column's data when the associative array is built.

 

When using a stand-alone query browser (to experiment), the display shows all columns, even those column names that appear more than once in the row.

 

 

that's because query browsers fetch the data as a numerical array and use the column metadata from the result set to get the corresponding table/column display names.

 

 

Is there a general approach to making keys (column names) unique strictly using a MySQL SELECT statement?

 

 

afaik - there's no built in query syntax that does this. you would either need to manually write out the query with alias names or dynamically produce the query statement using php, defining lists of what you want to select from each table and let your php code produce the select term with appropriate table_column alias names.

 

you could also take the query browser approach and do this using php code after the fact, to produce your own table_column array indexes from the field metadata. in looking at what the mysqli->fetch_fields() method returns, for each field you get the [name] selected column name/alias, [orgname] original/actual column name, [table] table name/alias, [orgtable] original/actual table name.

[bsmither]

"You should be specifying the columns that you want."

 

With a general solution, the desired approach would accommodate most situations. I do want all fields. And I may not know what they are in every situation.

 

"All the columns you have selected are actually in the result set sent to php."

 

I concur. That's why I am blaming the database class (which I am not going to change).

 

"You would need to dynamically produce the query statement using php, defining lists of what you want."

 

Fortunately, the database class has a showColumns($table) method. It's a step that, while not onerous, I did not want to complicate the general algorithm. But then, to make it general, perhaps it is necessary. I will try make the construction of a dynamic column list in PHP.

[Barand]

..... And I may not know what they are in every situation.

 

You cannot be serious!