CODEWITHSUNDEEP
mysql
jay
jay

Reputation: 10325

MySql SELECT AS - Append all field names

I'd like to do something like this:

SELECT table.id AS prefix_id, table.name AS prefix_name

... and have the prefix added dynamically to all field names rather than selecting them all manually (ie SELECT table.* AS prefix_* or something)

Hopefully I've described that accurately. Any thoughts?

Edit

To be clear, the reason I'm asking is to make sure that my query result contains every column from each table I call even if there are duplicate field names. For example, I might have a table with lots of fields which means I don't want to alias all the fields manually. Further if 3 tables have a field called name my result won't contain three name results; it will have one. I want to avoid ambiguity with my column names.

Upvotes: 17

Views: 19492

Answers (3)

Chaendler
Chaendler

Reputation: 41

In mysqli you can use the php function

mysqli_fetch_fields()

for fieldinformations - so you get the tablename of each collumn

Upvotes: -1

jay
jay

Reputation: 10325

To avoid ambiguity in columns with multiple tables it appears you must manually specify each column name that exists with the same name in more than one table.

http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html

Upvotes: 4

Milhous
Milhous

Reputation: 14643

When you reference the results, you can reference each column by the tablename.column . Therefore you have a default prefix that is appended to each column name. 

SELECT table.id,table.name,other.name FROM table,other Where other.name = table.name;

Upvotes: -1

Related Questions