Jump to content

Concat Table Name to Column Name


bsmither

Recommended Posts

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.