Column Aliases

[Ayana]

Is there any way to automatically create prefixed aliases for columns?

Example: (much simplified)

Table: Articles
Fields:
ArticleID
AuthorID
PublisherID
ArticleTitle
ArticleText
DatePublished

Table: Users
Fields:
UserID
Username
FirstName
LastName

Both AuthorID and PublisherID represent rows in the Users table.

I am trying to display full information about the article, its author and its publisher on a page. I am wondering if there's any automatic way to alias all columns joined from Users without explicitly writing each one of them in the query (as I have a lot more columns than in the example above and this is quite unwieldy). Something like:

SELECT A.*, U1.* AS 'U1_*', U2.* AS 'U2_*' FROM Articles AS A LEFT JOIN Users AS U1 ON A.AuthorID = U1.UserID LEFT JOIN Users AS U2 ON A.PublisherID = U2.User ID WHERE Articles.DatePublished = '2006-02-02'

[coopster]

Welcome to WebmasterWorld, Ayana.

To the best of my knowledge there is no ANSI standard for aliasing columns the way you are requesting. The columns would all be returned still, of course. For example,

CREATE TABLE table1 ( 
 tid INTEGER, 
 price DECIMAL(4,2) 
); 
CREATE TABLE table2 ( 
 tid INTEGER 
); 
INSERT INTO table1 (tid, price) VALUES(1,11.11), (2,22.22); 
INSERT INTO table2 (tid) VALUES(1), (2); 
SELECT T1.*, T2.* FROM table1 AS T1, table2 AS T2; 
-- 
-- Returns: 
-- 
+------+-------+------+ 
¦ tid  ¦ price ¦ tid  ¦ 
+------+-------+------+ 
¦    1 ¦ 11.11 ¦    1 ¦ 
¦    2 ¦ 22.22 ¦    1 ¦ 
¦    1 ¦ 11.11 ¦    2 ¦ 
¦    2 ¦ 22.22 ¦    2 ¦ 
+------+-------+------+

[Ayana]

Thanks for the welcome :)

Yes, that works as described. However PHP allows me to grab columns out of rows returned either by their order (1,2,3,etc) or their name ('tid', 'price'). By name the second tid column overwrites the values of the first (and thus I get a table with just 2 columns). By numeric order would work, however I am very wary of using that because any column addition / deletion would upset it very very easily.

[coopster]

I figured that was where you might have been leading. PHP often allows you to fetch rows from a result set with one of three options. You mentioned two, a numerical index or an associative index, but you can also return both the numerical and the associative. Using the third option you could write some kind of numeric-to-associative cross reference and extract values while looping through the columns but I think you are better off just writing your aliases out in your sql statement. It will be much easier to read, understand and modify for future reference. Yes, it may be a bit more work listing all those columns out in your SELECT statement but I think it is going to be worth it. I do, however, kind of like the idea of a prefix ... interesting idea.

[Ayana]

My main concern was affecting performance by having huge column listings being sent with each query. Other than that I can generate the column aliases through code by having the object look at its own members (each table has a matching class and each class has meta information about each of its members such as if it has to be quoted to be written to DB, what column it corresponds to or if it shouldn't be saved in the DB at all). So it looks like that's the way I will be going.

[coopster]

Sounds like you must be hitting the database once for each table even before you run your query statement then, no? Open source class? Or one you created yourself?

[Ayana]

They are classes I created myself. They are based on a simple skeleton of meta information members (prefixed with ___ for easy identification). All actual members are defined at runtime when the object is constructed through based on the meta info provided. I don't hit the DB to get the columns each table has, I simply hand-write the declaration of the member to match the column it corresponds to then many generic operations (load from result row, generate INSERT/UPDATE value pairs list, etc) can be done without me needing to add code for each member in five different places.

Of course, this all gets more complicated when dealing with joined tables which is where I am at now in the process of writing this stuff.

Let me know if you'd like to see the code I have so far.